Pivot table showing zeros
I have three locations and different tasks associated with each location. I would like to set up a pivot table that shows me the average status of every task at each location. I set up a column that is using a formula to convert the progress bar status into a numerical value that I am trying to use in my pivot table. Can anyone tell me why my averages are coming out to zero?
Best Answer

@Allie Forbes There's the problem. When you use quotes around a number, it actually produces a text value. To produce a numerical value, remove the quotes from around the numbers.
=IF([% Complete]1 = "Empty", 0, IF([% Complete]1 = "Quarter", 0.25, IF([% Complete]1 = "Half", 0.5, IF([% Complete]1 = "Three Quarter", 0.75, IF([% Complete]1 = "Full", 1)))))
Answers

What is the formula you are using?

@Paul Newcome I think she's just using the general AVG formula, being calculated using the Pivot Premium app... is that correct, @Allie Forbes ?
In Pivot, could you try selecting your Numerical Status as a "Column" as well?
My second thought is that maybe the column in your final sheet isn't set up to show decimals... could you maybe try adjusting the column properties by using the main toolbar up at the top? (See HERE, under "Numeric Formatting")
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
@Genevieve P and @Allie Forbes
I actually should have been more specific. Sorry about that.
What formula are you using to generate the numbers in relation to the progress bars? The formula in [Numerical Status] in the first screenshot.
Being left justified points towards them possibly being text values (unless column formatting is in place) which in turn would not provide any numerical values to average.

Oh goodness, of course! Thanks, Paul!
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
@Genevieve P That's why I should try to wake up a little more before trying to help. I forget that people can't read my mind to know exactly what I mean. 🤣

I knew I should have included the formula in my original question! Below is the formula I'm using.
=IF([% Complete]1 = "Empty", "0", IF([% Complete]1 = "Quarter", "0.25", IF([% Complete]1 = "Half", "0.5", IF([% Complete]1 = "Three Quarter", "0.75", IF([% Complete]1 = "Full", "1")))))
The pivot table is unfortunately already set up to show decimals, just double checked. I've also tried to create the pivot table in all sorts of combinations of rows, columns, and values utilizing both the progress bar status and the numerical value of the status.

@Allie Forbes There's the problem. When you use quotes around a number, it actually produces a text value. To produce a numerical value, remove the quotes from around the numbers.
=IF([% Complete]1 = "Empty", 0, IF([% Complete]1 = "Quarter", 0.25, IF([% Complete]1 = "Half", 0.5, IF([% Complete]1 = "Three Quarter", 0.75, IF([% Complete]1 = "Full", 1)))))

Thank you @Paul Newcome !! That was the answer I needed!

Happy to help! 👍️
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives