How not to show "#VALUE"

[Serge]

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

[Biff]

Hi!

Try this in D32 copied down:

=IF(C32="","",C32*1.05)

Biff

"Serge" <[email protected]> wrote in message
news:[email protected]...
> 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

[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.

"Biff" wrote:

> Hi!
>
> Try this in D32 copied down:
>
> =IF(C32="","",C32*1.05)
>
> Biff
>
> "Serge" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[[email protected]]

"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

[Biff]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>>
>>
>>

[Serge]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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
> >>
> >>
> >>
>
>
>

[Serge]

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

"[email protected]" 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
>
>

[Biff]

You're welcome!

Biff

"Serge" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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
>> >>
>> >>
>> >>
>>
>>
>>