Remember Me?

 George. Posts: n/a #value

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

 Peo Sjoblom Posts: n/a #value

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." wrote in
message ...

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

 Biff Posts: n/a #value

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." wrote in
message ...

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

 George. Posts: n/a #value

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

 Biff Posts: n/a #value

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." wrote in
message ...

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

 George. Posts: n/a #value

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??

--
George.
------------------------------------------------------------------------
George.'s Profile: http://www.excelforum.com/member.php...o&userid=35028

 Biff Posts: n/a #value

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." wrote in
message ...

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??

--
George.
------------------------------------------------------------------------
George.'s Profile:
http://www.excelforum.com/member.php...o&userid=35028

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

All times are GMT +1. The time now is 01:51 AM. Copyright ©2004-2021 ExcelBanter.