#1   Report Post  
Old June 2nd 06, 03:54 AM posted to microsoft.public.excel.newusers
George.
 
Posts: n/a
Default #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
View this thread: http://www.excelforum.com/showthread...hreadid=547722


  #2   Report Post  
Old June 2nd 06, 04:07 AM posted to microsoft.public.excel.newusers
Peo Sjoblom
 
Posts: n/a
Default #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
View this thread: http://www.excelforum.com/showthread...hreadid=547722



  #3   Report Post  
Old June 2nd 06, 04:17 AM posted to microsoft.public.excel.newusers
Biff
 
Posts: n/a
Default #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
View this thread: http://www.excelforum.com/showthread...hreadid=547722



  #4   Report Post  
Old June 2nd 06, 04:23 AM posted to microsoft.public.excel.newusers
George.
 
Posts: n/a
Default #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
View this thread: http://www.excelforum.com/showthread...hreadid=547722

  #5   Report Post  
Old June 2nd 06, 05:03 AM posted to microsoft.public.excel.newusers
Biff
 
Posts: n/a
Default #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
View this thread: http://www.excelforum.com/showthread...hreadid=547722





  #6   Report Post  
Old June 2nd 06, 06:19 AM posted to microsoft.public.excel.newusers
George.
 
Posts: n/a
Default #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??

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

  #7   Report Post  
Old June 2nd 06, 06:47 AM posted to microsoft.public.excel.newusers
Biff
 
Posts: n/a
Default #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??

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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 01:51 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright ©2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017