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
-
-
@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")
-
@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!
-
@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!
-
