Getting rid of the #VALUE error

[MNN]

How do you show an empty cell for column P and U ?

Thanks.

[RetiredGeek]

MNN, Since you are already evaluating Col W in Col I try this in Col P { =IF(ISNUMBER(I6),I6-H6,W6)} and see if it is what you're after?

[MNN]

Retired Geek, Thanks for the try but the formula doesn't record the increment or decrement if a % amount is used instead of the increase or decrease amount. Any other IDEAS.

Thanks again.

[RetiredGeek]

MNN,

Could you please explain in words exactly how you want P6 Calculated?

[MNN]

The current formula works but with no input amount ( number or percent) it show a #VALUE!. It should show an empty cell or an amount when the input is entered.

Thanks

[RetiredGeek]

MNN,

Please note my formula (second set) returns the same as yours see below:
MNN.JPG
To get rid of the 0's use Tools->Options->View Tab and uncheck Zero Values in 2003 or equivalent in 2007/10.

Also I noted that you have not used the Round function in the formulas but merely formatted the cells to show no decimal points. This can be very dangerous. When I first looked at my formula vs yours I got 8.75 vs your 9. This can cause calculation errors later as it will always use 8.75 to calculate and if you add enough numbers you'll start coming up short or long depending in your totals and/or other formulas.

I hope this helps.

[sdckapr]

How about this for P6:

=IF(COUNT(H6:I6)<>2,"",IF(ISNUMBER(W6),W6,I6-H6))

And for W6:
=IF(P6&W6="","",IF(ISNUMBER(Y6),Y6,MAX(P6,W6)/H6))

Steve

[MNN]

It takes a villages. Thank you both.