A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

#value



 
 
Thread Tools Display Modes
  #1  
Old June 2nd, 2006, 03:54 AM posted to microsoft.public.excel.newusers
external usenet poster
 
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  
Old June 2nd, 2006, 04:07 AM posted to microsoft.public.excel.newusers
external usenet poster
 
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  
Old June 2nd, 2006, 04:17 AM posted to microsoft.public.excel.newusers
external usenet poster
 
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  
Old June 2nd, 2006, 04:23 AM posted to microsoft.public.excel.newusers
external usenet poster
 
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  
Old June 2nd, 2006, 05:03 AM posted to microsoft.public.excel.newusers
external usenet poster
 
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  
Old June 2nd, 2006, 06:19 AM posted to microsoft.public.excel.newusers
external usenet poster
 
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  
Old June 2nd, 2006, 06:47 AM posted to microsoft.public.excel.newusers
external usenet poster
 
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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:43 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.