Stop *N/A in bar chart column

[scudo]

In the attached spreadsheet how do I stop the *N/A when a chart column is blank. For some reason I have managed to get a zero (which is acceptable) in the other bar charts in columns BJ:CC but for the life of me I dont know how I managed that.

[Pete_UK]

If you look at the formula in AA30, i.e.:

=IF(AC30=0,NA(),((E30+X30)-(Y30+Q26))*105%)

the red part shows how you are getting #N/A in that cell. The formula in AJ30 (which drives the graph to the right) does not have the IF, but if you change the NA() to zero then that is what you will return.

Hope this helps.

Pete

[scudo]

When I change:
=IF(AC30=0,NA(),((E30+X30)-(Y30+Q26))*105%)
To
=IF(AC30=0,0(),((E30+X30)-(Y30+Q26))*105%)

I get an error warning. Am I misunderstanding.

[Pete_UK]

Change it to this:

=IF(AC30=0,0,((E30+X30)-(Y30+Q26))*105%)

The two brackets belong to the NA() function.

Hope this helps.

Pete

[scudo]

Many thanks Pete_uk that is now working.

[Pete_UK]

You're welcome, thanks for the rep.

Pete