formula should return zero, but gives 0.05 as percentage

[tstorzuk]

Hello all.

I'm trying to put together a nice little invoice template for myself to be able to use for any job I work on.

I seem to be having a problem with one of the calcualtions that I am performing. Please take a look at it;

=IF(PRODUCT(E40,0.05)>0,PRODUCT(E40,0.05),"")

This is in a line below a SubTotal line (E40) to calculate a 5% tax on all items. When there is nothing in the SubTotal, this should show a zero......but it's showing 0.05. How do I make it show a zero instead? Is it just something simple like the formatting on the cell?

Thanks for the help on this!

Cheers!

[shg]

Please read our Forum Rules about thread titles, and then edit yours to be descriptive of your problem.

[wmorrison49]

Hey tstorzuk, try this:

=IF(E40<>"",(IF(PRODUCT(E40,0.05)>0,PRODUCT(E40,0.05),"")),"")

[tstorzuk]

Super........that works great!!

Thanks a TON.................and the forum continues to work for us novices.

[daddylonglegs]

Thanks for changing the thread title.......

PRODUCT function ignores empty cells (it doesn't treat it like zero) so when E40 is blank the product is 0.05

You could change to

=IF(E40*0.05>0,E40*0.05,"")

or, as E40*0.05 can only be greater than 0 when E40 is greater than zero, you can simplify further to

=IF(E40>0,E40*0.05,"")

Note: I'm assuming that E40 doesn't contain a formula that returns a blank - if it does change to

=IF(E40="","",E40*0.05)