Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why does blank cell assume value = 1?
I have created a simple =product formula that calculates the value of one
cell multiplie by a certain value. i.e. =product(h10,.5) The formula works fine, but when h10 refers to a blank cell, it assumes the value is 1. I want it to assume nothing really, but in reality I want it to assume 0, rather than 1. Help? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why does blank cell assume value = 1?
"C" wrote:
I have created a simple =product formula that calculates the value of one cell multiplie by a certain value. i.e. =product(h10,.5) Why not simply write: =H10*0.5 And in case H10 might be text (notably the null string): =N(H10)*0.5 if you want text to be treated as zero. (Sounds like you would.) The formula works fine, but when h10 refers to a blank cell, it assumes the value is 1. I want it to assume nothing really, but in reality I want it to assume 0, rather than 1. PRODUCT does not work that way. Read the Help page. It states that PRODUCT __ignores__ empty cells and cells containing text (notably the null string, which makes the cell appear empty), among others. "Ignores" means that PRODUCT does not include the cell in its evaluation. It does not mean that it treats the cell as 1 (or 0, for that matter). There is a contorted way to fix this. But I don't know why you would bother. Okay: =PRODUCT(if(H1="",0,H1),0.5) Please don't do that. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why does blank cell assume value = 1?
=IF(LEN(TRIM(H10))0,PRODUCT(H10,0.5),"")
-- Don Guillett Microsoft MVP Excel SalesAid Software "C" wrote in message ... I have created a simple =product formula that calculates the value of one cell multiplie by a certain value. i.e. =product(h10,.5) The formula works fine, but when h10 refers to a blank cell, it assumes the value is 1. I want it to assume nothing really, but in reality I want it to assume 0, rather than 1. Help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I would assume this is possible | Excel Discussion (Misc queries) | |||
Am I to assume... | Excel Discussion (Misc queries) | |||
What would Excel Assume if DATE left blank | Excel Worksheet Functions | |||
Number of days between 2 dates, if end date is blank assume todays | New Users to Excel | |||
Can I assume the program has crashed? | Excel Discussion (Misc queries) |