software

In cell C32:C40 I have the formula below:
=IF(OR(A32=quot;quot;,B32=quot;quot;),quot;quot;,SUMPRODUCT(('Bolt List'!$D$9:$D$188=A32)*('Bolt
List'!$J$9:$J$188=B32)*('Bolt List'!$E$9:$E$188=quot;SAE Gr.5 Min. Cad.
Platedquot;),'Bolt List'!$C$9:$C$188))

And in cell D3240 I have the following formula: =C32 (C32*0.05)

Data is only in C32:C:33 for the moment amp; I get answers in cell D3233.
Cell C34:C40 do not have entries yet, so I get quot;#VALUEquot; in cells D3440
Is there a simple remedy to not showing this?

Thank you in advance.

Serge

Hi!

Try this in D32 copied down:

=IF(C32=quot;quot;,quot;quot;,C32*1.05)

Biff

quot;Sergequot; gt; wrote in message
...
gt; In cell C32:C40 I have the formula below:
gt; =IF(OR(A32=quot;quot;,B32=quot;quot;),quot;quot;,SUMPRODUCT(('Bolt List'!$D$9:$D$188=A32)*('Bolt
gt; List'!$J$9:$J$188=B32)*('Bolt List'!$E$9:$E$188=quot;SAE Gr.5 Min. Cad.
gt; Platedquot;),'Bolt List'!$C$9:$C$188))
gt;
gt; And in cell D3240 I have the following formula: =C32 (C32*0.05)
gt;
gt; Data is only in C32:C:33 for the moment amp; I get answers in cell D3233.
gt; Cell C34:C40 do not have entries yet, so I get quot;#VALUEquot; in cells D3440
gt; Is there a simple remedy to not showing this?
gt;
gt; Thank you in advance.
gt;
gt; Serge
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.

quot;Biffquot; wrote:

gt; Hi!
gt;
gt; Try this in D32 copied down:
gt;
gt; =IF(C32=quot;quot;,quot;quot;,C32*1.05)
gt;
gt; Biff
gt;
gt; quot;Sergequot; gt; wrote in message
gt; ...
gt; gt; In cell C32:C40 I have the formula below:
gt; gt; =IF(OR(A32=quot;quot;,B32=quot;quot;),quot;quot;,SUMPRODUCT(('Bolt List'!$D$9:$D$188=A32)*('Bolt
gt; gt; List'!$J$9:$J$188=B32)*('Bolt List'!$E$9:$E$188=quot;SAE Gr.5 Min. Cad.
gt; gt; Platedquot;),'Bolt List'!$C$9:$C$188))
gt; gt;
gt; gt; And in cell D3240 I have the following formula: =C32 (C32*0.05)
gt; gt;
gt; gt; Data is only in C32:C:33 for the moment amp; I get answers in cell D3233.
gt; gt; Cell C34:C40 do not have entries yet, so I get quot;#VALUEquot; in cells D3440
gt; gt; Is there a simple remedy to not showing this?
gt; gt;
gt; gt; Thank you in advance.
gt; gt;
gt; gt; Serge
gt;
gt;
gt;

quot;could
explain (break down) the formula so I can understand itquot;

Literally, if C-32 is blank (quot;quot;) this cell (D-32) is blank, otherwise
this cell reads C-32 times 1.05.

edSure........

The reason you were getting #VALUE! errors is because if your long formula
returned a blank quot;quot;,=IF(OR(A32=quot;quot;,B32=quot;quot;),quot;quot;,........ then the subsequent
formula was trying to do math on a TEXT value:

=C32 (C32*0.05)

Which would evaluate to:

=quot;quot; (quot;quot;*0.05)

A formula blank quot;quot; 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

quot;Sergequot; gt; wrote in message
...
gt; Hello again Biff,
gt; It works great.
gt; Thank you very much.
gt; Serge
gt; At your convenience under no oblication. If it's not too much trouble
gt; could
gt; explain (break down) the formula so I can understand it.
gt;
gt; quot;Biffquot; wrote:
gt;
gt;gt; Hi!
gt;gt;
gt;gt; Try this in D32 copied down:
gt;gt;
gt;gt; =IF(C32=quot;quot;,quot;quot;,C32*1.05)
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;Sergequot; gt; wrote in message
gt;gt; ...
gt;gt; gt; In cell C32:C40 I have the formula below:
gt;gt; gt; =IF(OR(A32=quot;quot;,B32=quot;quot;),quot;quot;,SUMPRODUCT(('Bolt
gt;gt; gt; List'!$D$9:$D$188=A32)*('Bolt
gt;gt; gt; List'!$J$9:$J$188=B32)*('Bolt List'!$E$9:$E$188=quot;SAE Gr.5 Min. Cad.
gt;gt; gt; Platedquot;),'Bolt List'!$C$9:$C$188))
gt;gt; gt;
gt;gt; gt; And in cell D3240 I have the following formula: =C32 (C32*0.05)
gt;gt; gt;
gt;gt; gt; Data is only in C32:C:33 for the moment amp; I get answers in cell
gt;gt; gt; D3233.
gt;gt; gt; Cell C34:C40 do not have entries yet, so I get quot;#VALUEquot; in cells
gt;gt; gt; D3440
gt;gt; gt; Is there a simple remedy to not showing this?
gt;gt; gt;
gt;gt; gt; Thank you in advance.
gt;gt; gt;
gt;gt; gt; Serge
gt;gt;
gt;gt;
gt;gt;
thank you so much Biff,
You're like a brother.
Serge

quot;Biffquot; wrote:

gt; Sure........
gt;
gt; The reason you were getting #VALUE! errors is because if your long formula
gt; returned a blank quot;quot;,=IF(OR(A32=quot;quot;,B32=quot;quot;),quot;quot;,........ then the subsequent
gt; formula was trying to do math on a TEXT value:
gt;
gt; =C32 (C32*0.05)
gt;
gt; Which would evaluate to:
gt;
gt; =quot;quot; (quot;quot;*0.05)
gt;
gt; A formula blank quot;quot; is a zero length TEXT string.
gt;
gt; =C32 (C32*0.05)
gt;
gt; This formula is just adding 5% and another way to express that is:
gt;
gt; C32*1.05
gt;
gt; Fewer steps!
gt;
gt; Biff
gt;
gt; quot;Sergequot; gt; wrote in message
gt; ...
gt; gt; Hello again Biff,
gt; gt; It works great.
gt; gt; Thank you very much.
gt; gt; Serge
gt; gt; At your convenience under no oblication. If it's not too much trouble
gt; gt; could
gt; gt; explain (break down) the formula so I can understand it.
gt; gt;
gt; gt; quot;Biffquot; wrote:
gt; gt;
gt; gt;gt; Hi!
gt; gt;gt;
gt; gt;gt; Try this in D32 copied down:
gt; gt;gt;
gt; gt;gt; =IF(C32=quot;quot;,quot;quot;,C32*1.05)
gt; gt;gt;
gt; gt;gt; Biff
gt; gt;gt;
gt; gt;gt; quot;Sergequot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt; In cell C32:C40 I have the formula below:
gt; gt;gt; gt; =IF(OR(A32=quot;quot;,B32=quot;quot;),quot;quot;,SUMPRODUCT(('Bolt
gt; gt;gt; gt; List'!$D$9:$D$188=A32)*('Bolt
gt; gt;gt; gt; List'!$J$9:$J$188=B32)*('Bolt List'!$E$9:$E$188=quot;SAE Gr.5 Min. Cad.
gt; gt;gt; gt; Platedquot;),'Bolt List'!$C$9:$C$188))
gt; gt;gt; gt;
gt; gt;gt; gt; And in cell D3240 I have the following formula: =C32 (C32*0.05)
gt; gt;gt; gt;
gt; gt;gt; gt; Data is only in C32:C:33 for the moment amp; I get answers in cell
gt; gt;gt; gt; D3233.
gt; gt;gt; gt; Cell C34:C40 do not have entries yet, so I get quot;#VALUEquot; in cells
gt; gt;gt; gt; D3440
gt; gt;gt; gt; Is there a simple remedy to not showing this?
gt; gt;gt; gt;
gt; gt;gt; gt; Thank you in advance.
gt; gt;gt; gt;
gt; gt;gt; gt; Serge
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;

Thank you Ed,
This makes it a lot easier.
Serge

quot; wrote:

gt; quot;could
gt; explain (break down) the formula so I can understand itquot;
gt;
gt; Literally, if C-32 is blank (quot;quot;) this cell (D-32) is blank, otherwise
gt; this cell reads C-32 times 1.05.
gt;
gt; ed
gt;
gt;

You're welcome!

Biff

quot;Sergequot; gt; wrote in message
news
gt; thank you so much Biff,
gt; You're like a brother.
gt; Serge
gt;
gt; quot;Biffquot; wrote:
gt;
gt;gt; Sure........
gt;gt;
gt;gt; The reason you were getting #VALUE! errors is because if your long
gt;gt; formula
gt;gt; returned a blank quot;quot;,=IF(OR(A32=quot;quot;,B32=quot;quot;),quot;quot;,........ then the subsequent
gt;gt; formula was trying to do math on a TEXT value:
gt;gt;
gt;gt; =C32 (C32*0.05)
gt;gt;
gt;gt; Which would evaluate to:
gt;gt;
gt;gt; =quot;quot; (quot;quot;*0.05)
gt;gt;
gt;gt; A formula blank quot;quot; is a zero length TEXT string.
gt;gt;
gt;gt; =C32 (C32*0.05)
gt;gt;
gt;gt; This formula is just adding 5% and another way to express that is:
gt;gt;
gt;gt; C32*1.05
gt;gt;
gt;gt; Fewer steps!
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;Sergequot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Hello again Biff,
gt;gt; gt; It works great.
gt;gt; gt; Thank you very much.
gt;gt; gt; Serge
gt;gt; gt; At your convenience under no oblication. If it's not too much trouble
gt;gt; gt; could
gt;gt; gt; explain (break down) the formula so I can understand it.
gt;gt; gt;
gt;gt; gt; quot;Biffquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; Hi!
gt;gt; gt;gt;
gt;gt; gt;gt; Try this in D32 copied down:
gt;gt; gt;gt;
gt;gt; gt;gt; =IF(C32=quot;quot;,quot;quot;,C32*1.05)
gt;gt; gt;gt;
gt;gt; gt;gt; Biff
gt;gt; gt;gt;
gt;gt; gt;gt; quot;Sergequot; gt; wrote in message
gt;gt; gt;gt; ...
gt;gt; gt;gt; gt; In cell C32:C40 I have the formula below:
gt;gt; gt;gt; gt; =IF(OR(A32=quot;quot;,B32=quot;quot;),quot;quot;,SUMPRODUCT(('Bolt
gt;gt; gt;gt; gt; List'!$D$9:$D$188=A32)*('Bolt
gt;gt; gt;gt; gt; List'!$J$9:$J$188=B32)*('Bolt List'!$E$9:$E$188=quot;SAE Gr.5 Min. Cad.
gt;gt; gt;gt; gt; Platedquot;),'Bolt List'!$C$9:$C$188))
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; And in cell D3240 I have the following formula: =C32 (C32*0.05)
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Data is only in C32:C:33 for the moment amp; I get answers in cell
gt;gt; gt;gt; gt; D3233.
gt;gt; gt;gt; gt; Cell C34:C40 do not have entries yet, so I get quot;#VALUEquot; in cells
gt;gt; gt;gt; gt; D3440
gt;gt; gt;gt; gt; Is there a simple remedy to not showing this?
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Thank you in advance.
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Serge
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt;
gt;gt;
gt;gt;