Solved

#VALUE error

Posted on 2013-06-04
5
171 Views
Last Modified: 2013-06-04
In the attached file, I'm getting a #VALUE error in columns AZ, BG and BL in the "All  Master" worksheet.  I don't know if there's an erroneous space somewhere in one of the other worksheets that's causing this or what, but I can't seem to get rid of it.  Help!!!!
agt-requirements---experts-excha.xlsx
0
Comment
Question by:esu4236
  • 3
  • 2
5 Comments
 

Author Comment

by:esu4236
ID: 39219928
I think I figured out my own problem.  Rather than having a formula like this:
=IF(Manager!Y6+RFTC!Y6+OtherMentor!Y6+JW!Y6=0,"",Manager!Y6+RFTC!Y6+OtherMentor!Y6+JW!Y6)

I needed this:
=IF(Manager!Y6+RFTC!Y6+OtherMentor!Y6+JW!Y6="","",Manager!Y6+RFTC!Y6+OtherMentor!Y6+JW!Y6)

where I replaced the =0 with =" because sometimes the result was nothing rather than a 0.

And then I had to deselect the option to "Show a Zero in cells that have a zero value."  

I think that takes care of it.  Anyone see anything else???
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39219957
SUM function shouldn't have + signs, use commas like this in AZ6

=SUM(Y6,AG6,AM6,AU6,AX6)

and then you don't need to change anything else. Similarly use SUM in BG and BL like

=SUM(BE6,BF6)

regards, barry
0
 

Author Comment

by:esu4236
ID: 39219969
Is using the plus signs going to cause the #VALUE error though?
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 39220081
Yes, because some of the values in the cells you are adding are what are known as "formula blanks", i.e. "" returned by a formula. If you try to add text values using + then you get a #VALUE! error (and "" is a text value) but when using SUM with commas text values are ignored. When I changed AZ6 to

=SUM(Y6,AG6,AM6,AU6,AX6)

I got a number (4) rather than an error, without changing anything else

regards, barry
0
 

Author Closing Comment

by:esu4236
ID: 39220101
Very good, thank you very much Barry!!  Appreciate it.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now