Percentage Formulas and adding to SIGHTS

Amy Burns

Hi, I am trying to use a formula based off of Counts or Sum to get a percentage of work done.  Then I need to add that percentage into my SIGHTS Dashboard.  I am having trouble finding an operator or formula to help me do this.  Does anyone know how to best solve this issue?

Thanks!

Richard Rymill SBP

Hi Amy

If you open a smartsheet template for project management then those calculations are already in there, so you can use one of those or learn from how it works and modify your own sheet.

Good luck.

Richard

 

Amy Burns

Thnaks - yes I have those for some of the calculations.  This is not really a project plan that I am working off of, it is more of a running list of items.  For example there are 20 companies listed in a column A and 10 of those companies have been moved column B  to a new server.  So trying to use a formula using totals/counts from 2 colums or cells and get percentage.  Hope that makes sense.

Kennedy Stomps

Hi Amy--

 

Do the items exist in two columns at the same time? E.g., are the companies listed in one column and then the moving details listed in another column? If so, you could use a COUNTIF formula to determine what number of companies listed in Column A have a "moved" status in Column B, then divide this value by the total number of companies in Column A to find a "% moved" value:

 

=COUNTIF([Column B]:[Column B], "Moved") / COUNT([Column A]:[Column A])

 

This will count the number of times a value in Column B matches your moved criteria, then divide this by the total number of values in Column A (which I'm assuming is all values?) If this doesn't match your workflow, feel free to post a screen capture of your sheet here and we can help troubleshoot further. If you can't share the sheet publicly, feel free to share it with me privately at Kennedy.Stomps@Smartsheet.com and I'll be happy to assist with this formula.

 

Amy Burns

This Worked!  Thank you!

Caroline

Is it possible to add a formula to a report? For example, I built a report with report builder summarizing the information from one of my sheets and now I would like to run a formula to calculate the # of projects to:

• -be scheduled
• -request for quote
• -quoted
• -quotes in progress

 

Let me know if this is possible to add this type of info in a report once the report is run. I was actually expecting to be able to run this type of formula in Sights within the metric widget but doesn't appear that this it's possible.

 

Thanks,

Caroline

Kennedy Stomps

Hi Caroline--

 

It isn't currently possible to run calculations in Reports, but I've added your vote for this to our enhancement request list. Thanks for your input!

 

In the meantime, you would need to perform these calculations in the source sheets, which you could then pull into your reports or into a Sight in a widget.