|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 06:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com