Default numeric value
 

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'?

Default numeric value
 

Would help to see your formula, but:
=if(isblank(a1),"",a1*b1)
************
Anne Troy
VBA Project Manager
www.OfficeArticles.com

"thomson" wrote in message
...
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'?

Default numeric value
 

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??





"Anne Troy" wrote:

Would help to see your formula, but:
=if(isblank(a1),"",a1*b1)
************
Anne Troy
VBA Project Manager
www.OfficeArticles.com

"thomson" wrote in message
...
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'?

Default numeric value
 

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.


"thomson" wrote:

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??





"Anne Troy" wrote:

Would help to see your formula, but:
=if(isblank(a1),"",a1*b1)
************
Anne Troy
VBA Project Manager
www.OfficeArticles.com

"thomson" wrote in message
...
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'?

Default numeric value
 

I tried this and it works well. I am new to using Excel ... very powerful.

"Jason Clement" wrote:

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.


"thomson" wrote:

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??





"Anne Troy" wrote:

Would help to see your formula, but:
=if(isblank(a1),"",a1*b1)
************
Anne Troy
VBA Project Manager
www.OfficeArticles.com

"thomson" wrote in message
...
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'?