#VALUE

[Krista]

Why am I getting #VALUE in the cell when E15 is blank in the below formula?

=IF(E15="","",IF(E15="1x",(SUM(G15*1)),IF(E15="3x",(SUM(G15*3)),IF(E15="6x",(SUM(G15*6)),IF(E15="10x",(SUM(G15*10)),0)))))*4.5

I thought that the IF(E15="","" portion of the formula would keep that from
appearing.

Any ideas?

Thanks much!
--
Krista

[daddylonglegs]

When E15 is blank the IF function does indeed return a blank but then you are trying to multiply that blank by 4.5, which will give you a #VALUE! error.

If you always want to multiply the number in E15 (without the "x") by G15 and then by 4.5 perhaps try

=IF(E15="","",G15*SUBSTITUTE(E15,"x","")*4.5)

[Krista]

Thanks!

--
Krista


"daddylonglegs" wrote:

>
> When E15 is blank the IF function does indeed return a blank but then
> you are trying to multiply that blank by 4.5, which will give you a
> #VALUE! error.
>
> If you always want to multiply the number in E15 (without the "x") by
> G15 and then by 4.5 perhaps try
>
> =IF(E15="","",G15*SUBSTITUTE(E15,"x","")*4.5)
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=558685
>
>

[Krista]

Oh Yeah, that formula is much much better.

Thanks again!
--
Krista


"daddylonglegs" wrote:

>
> When E15 is blank the IF function does indeed return a blank but then
> you are trying to multiply that blank by 4.5, which will give you a
> #VALUE! error.
>
> If you always want to multiply the number in E15 (without the "x") by
> G15 and then by 4.5 perhaps try
>
> =IF(E15="","",G15*SUBSTITUTE(E15,"x","")*4.5)
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=558685
>
>