MrExcel Publishing
Your One Stop for Excel Tips & Solutions

result not displayed when prcentage is used

Posted by robert on September 21, 2000 2:39 AM

i have created a spredsheet for which there are a few simple functions.

Formulas as follows.

subtotal =IF(SUM(H19:H45),SUM(H19:H45),"")
discount =IF(H46/100*D47,H46/100*D47,"")
price =IF(H46-H47,H46-H47,"")
vat =IF(D51/100*H48,D51/100*H48,"")
total =IF(H48+H51,H48+H51,"")

when i input a percentage into the discount cell
the price, vat, and the total show this:#VALUE
instead of showing the anwers in numbers. Help

Posted by Tim Francis-Wright on September 21, 2000 7:26 AM

I put your formulas into a workbook and found
that they worked, except when the discount or
vat rates (in D47 or D51) were zero. The formulas
put in a space rather than a number when either
the discount or VAT is zero.

I recommend taking out the IF structure of these
formulas. I suspect that you didn't want to
display zeroes. You can either suppress printing
of zero values under Tools/Options (to do so for
the entire sheet), or use a number format like #,##0;(#,##0); to do so for just some particular