number #value

[George.]

Hey Guys -

www.lincolneather.com/tektips.jpg

I have attached the worksheet, if you get time to look at, could explain to me why I keep getting the dreaded “#VALUE” argghh.
What the goal is to have user insert how many packs they want in the months required – which then gives a total number of packs in the total box. That figure is then used to calculate the price for all the packs. Make sense?

I can get it to work if I put a number in, but if leave the cells empty (with or without a space) I get #VALUE in the end total cell......

Thanks for any help

[Peo Sjoblom]

What's the formula in the cells where you get the errors? If you use
cell1+cell2 etc change that to SUM(cell1,cell2) If you multiply like
cell1*cell2 change that to PRODUCT(cell1,cell2)

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"George." <[email protected]> wrote in
message news:[email protected]...
>
> Hey Guys -
>
> www.lincolneather.com/tektips.jpg
>
> I have attached the worksheet, if you get time to look at, could
> explain to me why I keep getting the dreaded "#VALUE" argghh.
> What the goal is to have user insert how many packs they want in the
> months required - which then gives a total number of packs in the total
> box. That figure is then used to calculate the price for all the packs.
> Make sense?
>
> I can get it to work if I put a number in, but if leave the cells empty
> (with or without a space) I get #VALUE in the end total cell......
>
> Thanks for any help
>
>
> --
> George.
> ------------------------------------------------------------------------
> George.'s Profile:
> http://www.excelforum.com/member.php...o&userid=35028
> View this thread: http://www.excelforum.com/showthread...hreadid=547722
>

[Biff]

Hi!

The error in the Total cell is due to the error in the cell above it.

How about posting the formulas you're using in both cells.

Biff

"George." <[email protected]> wrote in
message news:[email protected]...
>
> Hey Guys -
>
> www.lincolneather.com/tektips.jpg
>
> I have attached the worksheet, if you get time to look at, could
> explain to me why I keep getting the dreaded "#VALUE" argghh.
> What the goal is to have user insert how many packs they want in the
> months required - which then gives a total number of packs in the total
> box. That figure is then used to calculate the price for all the packs.
> Make sense?
>
> I can get it to work if I put a number in, but if leave the cells empty
> (with or without a space) I get #VALUE in the end total cell......
>
> Thanks for any help
>
>
> --
> George.
> ------------------------------------------------------------------------
> George.'s Profile:
> http://www.excelforum.com/member.php...o&userid=35028
> View this thread: http://www.excelforum.com/showthread...hreadid=547722
>

[George.]

yeah sorry fellas had a shocker there.

the formula in the pack qty cell is
=IF(SUM(D64:I64)=0,"",SUM(D64:I64))

and the formula in the cost cell is
=IF(SUM(M64*L64)=0,"",SUM(M64*L64)*12)

i know that total cell formula is correct but aint working until i fix the other formulas

hope that helps.

[Biff]

Ok.........

Change this:

>and the formula in the cost cell is
>=IF(SUM(M64*L64)=0,"",SUM(M64*L64)*12)

To:

=IF(ISNUMBER(L64),L64*M64*12,"")

Just use a SUM(....) function for the Total. SUM will ignore TEXT.

Biff

"George." <[email protected]> wrote in
message news:[email protected]...
>
> yeah sorry fellas had a shocker there.
>
> the formula in the pack qty cell is
> =IF(SUM(D64:I64)=0,"",SUM(D64:I64))
>
> and the formula in the cost cell is
> =IF(SUM(M64*L64)=0,"",SUM(M64*L64)*12)
>
> i know that total cell formula is correct but aint working until i fix
> the other formulas
>
> hope that helps.
>
>
> --
> George.
> ------------------------------------------------------------------------
> George.'s Profile:
> http://www.excelforum.com/member.php...o&userid=35028
> View this thread: http://www.excelforum.com/showthread...hreadid=547722
>

[George.]

wow
thanks so much for that, ur formula worked perfectly!!!
ive been battling with this problem for far too long.

is there much chance of getting you to explain why it works as opposed to what i was using??

thanks again for your help.

[Biff]

>is there much chance of getting you to explain why it works as opposed
>to what i was using??

Sure, that's how we learn!

If this formula returned the blank (""):

=IF(SUM(D64:I64)=0,"",SUM(D64:I64))

Then this formula was trying to multiply 18.15 * ""

=IF(SUM(M64*L64)=0,"",SUM(M64*L64)*12)

The formula blank ("") is an empty TEXT string. So, 18.15 * TEXT = #VALUE!

This formula:

=IF(ISNUMBER(L64),L64*M64*12,"")

Tests the value in L64 to see if it is a number and if it is then carries
out the multiplication of L64*M64*12.

Biff

"George." <[email protected]> wrote in
message news:[email protected]...
>
> wow
> thanks so much for that, ur formula worked perfectly!!!
> ive been battling with this problem for far too long.
>
> is there much chance of getting you to explain why it works as opposed
> to what i was using??
>
> thanks again for your help.
>
>
> --
> George.
> ------------------------------------------------------------------------
> George.'s Profile:
> http://www.excelforum.com/member.php...o&userid=35028
> View this thread: http://www.excelforum.com/showthread...hreadid=547722
>