| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How not to show "#VALUE"
In cell C32:C40 I have the formula below:
=IF(OR(A32="",B32=""),"",SUMPRODUCT(('Bolt List'!$D$9:$D$188=A32)*('Bolt List'!$J$9:$J$188=B32)*('Bolt List'!$E$9:$E$188="SAE Gr.5 Min. Cad. Plated"),'Bolt List'!$C$9:$C$188)) And in cell D32:D40 I have the following formula: =C32+(C32*0.05) Data is only in C32:C:33 for the moment & I get answers in cell D32:D33. Cell C34:C40 do not have entries yet, so I get "#VALUE" in cells D34:D40 Is there a simple remedy to not showing this? Thank you in advance. Serge |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How not to show "#VALUE"
Hi!
Try this in D32 copied down: =IF(C32="","",C32*1.05) Biff "Serge" wrote in message ... In cell C32:C40 I have the formula below: =IF(OR(A32="",B32=""),"",SUMPRODUCT(('Bolt List'!$D$9:$D$188=A32)*('Bolt List'!$J$9:$J$188=B32)*('Bolt List'!$E$9:$E$188="SAE Gr.5 Min. Cad. Plated"),'Bolt List'!$C$9:$C$188)) And in cell D32:D40 I have the following formula: =C32+(C32*0.05) Data is only in C32:C:33 for the moment & I get answers in cell D32:D33. Cell C34:C40 do not have entries yet, so I get "#VALUE" in cells D34:D40 Is there a simple remedy to not showing this? Thank you in advance. Serge |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How not to show "#VALUE"
Hello again Biff,
It works great. Thank you very much. Serge At your convenience under no oblication. If it's not too much trouble could explain (break down) the formula so I can understand it. "Biff" wrote: Hi! Try this in D32 copied down: =IF(C32="","",C32*1.05) Biff "Serge" wrote in message ... In cell C32:C40 I have the formula below: =IF(OR(A32="",B32=""),"",SUMPRODUCT(('Bolt List'!$D$9:$D$188=A32)*('Bolt List'!$J$9:$J$188=B32)*('Bolt List'!$E$9:$E$188="SAE Gr.5 Min. Cad. Plated"),'Bolt List'!$C$9:$C$188)) And in cell D32:D40 I have the following formula: =C32+(C32*0.05) Data is only in C32:C:33 for the moment & I get answers in cell D32:D33. Cell C34:C40 do not have entries yet, so I get "#VALUE" in cells D34:D40 Is there a simple remedy to not showing this? Thank you in advance. Serge |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How not to show "#VALUE"
"could
explain (break down) the formula so I can understand it" Literally, if C-32 is blank ("") this cell (D-32) is blank, otherwise this cell reads C-32 times 1.05. ed |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How not to show "#VALUE"
Sure........
The reason you were getting #VALUE! errors is because if your long formula returned a blank "",=IF(OR(A32="",B32=""),"",........ then the subsequent formula was trying to do math on a TEXT value: =C32+(C32*0.05) Which would evaluate to: =""+(""*0.05) A formula blank "" is a zero length TEXT string. =C32+(C32*0.05) This formula is just adding 5% and another way to express that is: C32*1.05 Fewer steps! Biff "Serge" wrote in message ... Hello again Biff, It works great. Thank you very much. Serge At your convenience under no oblication. If it's not too much trouble could explain (break down) the formula so I can understand it. "Biff" wrote: Hi! Try this in D32 copied down: =IF(C32="","",C32*1.05) Biff "Serge" wrote in message ... In cell C32:C40 I have the formula below: =IF(OR(A32="",B32=""),"",SUMPRODUCT(('Bolt List'!$D$9:$D$188=A32)*('Bolt List'!$J$9:$J$188=B32)*('Bolt List'!$E$9:$E$188="SAE Gr.5 Min. Cad. Plated"),'Bolt List'!$C$9:$C$188)) And in cell D32:D40 I have the following formula: =C32+(C32*0.05) Data is only in C32:C:33 for the moment & I get answers in cell D32:D33. Cell C34:C40 do not have entries yet, so I get "#VALUE" in cells D34:D40 Is there a simple remedy to not showing this? Thank you in advance. Serge |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How not to show "#VALUE"
thank you so much Biff,
You're like a brother. Serge "Biff" wrote: Sure........ The reason you were getting #VALUE! errors is because if your long formula returned a blank "",=IF(OR(A32="",B32=""),"",........ then the subsequent formula was trying to do math on a TEXT value: =C32+(C32*0.05) Which would evaluate to: =""+(""*0.05) A formula blank "" is a zero length TEXT string. =C32+(C32*0.05) This formula is just adding 5% and another way to express that is: C32*1.05 Fewer steps! Biff "Serge" wrote in message ... Hello again Biff, It works great. Thank you very much. Serge At your convenience under no oblication. If it's not too much trouble could explain (break down) the formula so I can understand it. "Biff" wrote: Hi! Try this in D32 copied down: =IF(C32="","",C32*1.05) Biff "Serge" wrote in message ... In cell C32:C40 I have the formula below: =IF(OR(A32="",B32=""),"",SUMPRODUCT(('Bolt List'!$D$9:$D$188=A32)*('Bolt List'!$J$9:$J$188=B32)*('Bolt List'!$E$9:$E$188="SAE Gr.5 Min. Cad. Plated"),'Bolt List'!$C$9:$C$188)) And in cell D32:D40 I have the following formula: =C32+(C32*0.05) Data is only in C32:C:33 for the moment & I get answers in cell D32:D33. Cell C34:C40 do not have entries yet, so I get "#VALUE" in cells D34:D40 Is there a simple remedy to not showing this? Thank you in advance. Serge |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How not to show "#VALUE"
Thank you Ed,
This makes it a lot easier. Serge " wrote: "could explain (break down) the formula so I can understand it" Literally, if C-32 is blank ("") this cell (D-32) is blank, otherwise this cell reads C-32 times 1.05. ed |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How not to show "#VALUE"
You're welcome!
Biff "Serge" wrote in message ... thank you so much Biff, You're like a brother. Serge "Biff" wrote: Sure........ The reason you were getting #VALUE! errors is because if your long formula returned a blank "",=IF(OR(A32="",B32=""),"",........ then the subsequent formula was trying to do math on a TEXT value: =C32+(C32*0.05) Which would evaluate to: =""+(""*0.05) A formula blank "" is a zero length TEXT string. =C32+(C32*0.05) This formula is just adding 5% and another way to express that is: C32*1.05 Fewer steps! Biff "Serge" wrote in message ... Hello again Biff, It works great. Thank you very much. Serge At your convenience under no oblication. If it's not too much trouble could explain (break down) the formula so I can understand it. "Biff" wrote: Hi! Try this in D32 copied down: =IF(C32="","",C32*1.05) Biff "Serge" wrote in message ... In cell C32:C40 I have the formula below: =IF(OR(A32="",B32=""),"",SUMPRODUCT(('Bolt List'!$D$9:$D$188=A32)*('Bolt List'!$J$9:$J$188=B32)*('Bolt List'!$E$9:$E$188="SAE Gr.5 Min. Cad. Plated"),'Bolt List'!$C$9:$C$188)) And in cell D32:D40 I have the following formula: =C32+(C32*0.05) Data is only in C32:C:33 for the moment & I get answers in cell D32:D33. Cell C34:C40 do not have entries yet, so I get "#VALUE" in cells D34:D40 Is there a simple remedy to not showing this? Thank you in advance. Serge |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Show last number entered | Excel Discussion (Misc queries) | |||
| Show data used and percent label | Charts and Charting in Excel | |||
| leading zeros are not showing in the formula bar but do show in ce | Excel Discussion (Misc queries) | |||
| Show values from other sheet | Excel Worksheet Functions | |||
| with formulas that show negative results I want to show zero inste | Excel Discussion (Misc queries) | |||
Linear Mode
