Help! Pivot table shows #DIV/0! error on all cells derived from IF/THEN formulas in source sheet

[beetelman]

I am using Excel 2010 on a Win7 OS. I created a pivot table based on a fairly small data set (20 rows), a combination of text, formulas, percentages, numbers, etc.). In the pivot table, the values from the source sheet that are derived from If/Then formulas show up in the pivot table as #DIV/0!, but the value in the source sheet shows up as 50% or 20% or some other percentage.

The 50% (or other percentage value) is a result of the following formula: =IF(O2=5,”0%”,IF(O2=4,”20%”,IF(O2=3,”40%”,IF(O2=2,”60%”,IF(O2=1,”80%”,IF(O2=0,”100%”)))))). In other words, I’m assigning a % value based on a numerical ranking from 0 to 5. However, the Pivot table does not recognize the formula-derived value. If I just hard key “50%” into the source sheet cell, I have no problem seeing it show up in the pivot table. The pivot table does not seem to "read" a value derived from this If/Then statement.

Also, I created "dummy" columns adjacent to the If/Then formula values and used an "=XX" where "XX" is the value of the cell in the adjacent column, but the Pivot table returned the same #DIV/0! value.

I can't find any other Excel message boards where this problem was discussed.


Thanks for any help you can offer.

 

[par60056]

what happens if you make your formula: =(5-O2)/5

I think part of the problem may be that your last IF "if(O2=0" is missing an the 3rd parameter. But pivot tables don't work very well when the value fields are strings. You could try it without the quotes on the percent values.

 

[beetelman]

Thanks for your reply. Both recommendations worked perfectly, and the =(5=O2)/5 formula is so much easier than the long If/Then string. However, removing the quotations from the percent values also worked, by the way.

Thanks again. I've Googled the heck out of this problem and haven't found an answer until now. Really appreciate it.

 

[par60056]

Thank you for the feedback.

Welcome to the forum.

I have searched numerous Excel forums and the best answers are here.