Solved

#VALUE error

Posted on 2013-06-04
5
173 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

831 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