#VALUE error

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
esu4236Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
barry houdiniConnect With a Mentor Commented:
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
 
esu4236Author Commented:
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
 
barry houdiniCommented:
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
 
esu4236Author Commented:
Is using the plus signs going to cause the #VALUE error though?
0
 
esu4236Author Commented:
Very good, thank you very much Barry!!  Appreciate it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.