Default numeric value

T

thomson

I am creating a spreadsheet dealing with dollars. There are several formulas
that I have created using percentages. One particular formula multiplies a
percent in one cell by the $ value in another. If I have no $ value in the
cell the formula uses a '1' instead of what I would expect '0'. How can I
set up my sheet so that if a cell is blank the default value will be '0'?
 
T

thomson

This works great thanks! Here is what the formula ended up being ....
=SUM(IF(ISBLANK(D9),0,D$1*D9),IF(ISBLANK(E9),0,E$1*E9),IF(ISBLANK(F9),0,F$1*F9),IF(ISBLANK(G9),0,G$1*G9),IF(ISBLANK(H9),0,H$1*H9))

I still have the question on why a 'blank' would end up being evaluated as a
'1' when you find a product. When the formula is a 'SUM' the blank is
considered a '0' ... so why is it different if it it s product? Maybe
someone from Microsoft can tell me??
 
J

Jason Clement

You can use an array formula to make it shorter. Enter:

=SUM(IF(ISBLANK(D9:H9),0,D$1:H$1*D9:H9))

To make it an array formula, press ctrl+shift+enter to enter the formula as
opposed to pressing enter alone.
 
Top