#value! error, why? (Stumped)

mpartyka

Board Regular
Joined
Nov 11, 2004
Messages
73
Cell W12 contains this formula:

=SUM(G12+H12+I12+J12+K12+M12+O12+Q12+S12+U12)

I cannot change the referenced cells (ie G12 etc, or make it G12:U12)

How can I make cell W12 blank even if the cells in the formula are blank, filled in, or partially filled in? I hope I explained myself well enough? :rolleyes:
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Try this. Hope it works. Dave
Code:
=IF(ISERROR(SUM(G12+H12+I12+J12+K12+M12+O12+Q12+S12+U12) ),"",SUM(G12+H12+I12+J12+K12+M12+O12+Q12+S12+U12) )
 
Upvote 0
Not totally clear when you want a blank to appear???? Do you mean blank until all cells have something in them? The way it reads you want that cell always blank, and I am sure that is not your intent.
 
Upvote 0
Are you putting text in any of those cells, else I',m not sure how you're getting your value error.

Your formula syntax is incorrect in that you don't need SUM to wrap cell references that have + between them, or you just lose the + eg

=G12+H12+I12+J12+K12+M12+O12+Q12+S12+U12

or

=SUM(G12,H12,I12,J12,K12,M12,O12,Q12,S12,U12)

Either way a good portion of your ranges are contiguous so you can use a range eg

=SUM(G12:K12)+M12+O12+Q12+S12+U12

To then give a blank if no data you could then use the construct (Assuming no negative values that could net the series to 0)

=IF((SUM(G12:K12)+M12+O12+Q12+S12+U12)=0,"",SUM(G12:K12)+M12+O12+Q12+S12+U12)
 
Upvote 0
Man was I tired! You are correct, I did not want cell W12 to be blank when all the others had data. The only data that will go into the range cells will be numbers 0 through 6. Thanks for your assistance, I will check it out momentarily. :)
 
Upvote 0
Okay, I tried those ideas and I got the same result, #value!. So, I will explain things a little more. I have 10 cells (they actually represent 1/2 day periods) which total into cell W12. Now, if all 10 cells are filled in everything works great. The problem comes into play at the beginning of the week when no 1/2 days have been filled in yet, so all 10 cells are blanks. Taking things a step further, as we go day by day (Mon- Fri) the cells will slowly be filled in with numbers. That range can be 0, 1, 2, or 3. Hypothetically 4, 5, and 6 might possibly be in there but that is highly unlikely in reality. That brings us to the #value! problem and this is what I need:

If (none of the cells), (one of the ten cells), or (9 of the 10 cells) are filled in I desire cell W12 to be blank. Ideally, I would like a running total of the week as each day is filled in but that appears to be another problem altogether? Any ideas? I thank you in advance for the assistance rendered. :rolleyes:

The 10 cells in question get their number values from another sheet, if that helps?
 
Upvote 0
Much more clear!! I am sure someone can help you now....I will be back in about 6 hours and I can help you then if no one has yet. Thanks for the better explaination!
 
Upvote 0
=IF(OR(COUNT(G12:K12,M12,O12,Q12,S12,U12)=0,COUNT(G12:K12,M12,O12,Q12,S12,U12)=1,COUNT(G12:K12,M12,O12,Q12,S12,U12)=9),"",SUM(G12+H12+I12+J12+K12+M12+O12+Q12+S12+U12))
 
Upvote 0
Now you really have me confused. First you say you want it to return blank unless ALL the cells are filled, but then you say you would like a running total as you go through the week?? You can have one or the other but not both, unless you utilise another cell.

Now lets just clarify:-

  • - You have 10 cells that are referred to by the formula, those being the ones above

    - Each of those cells gets its values from another sheet, so I assume you have formulas in each of these 10 cells - correct?

    - Assuming that is correct, what do those formulas return on the days that nothing has been filled in, ie a 0 a blank or what?

    - In your 10 cells, are there are any errors such as #VALUE anywhere.
Lastly, this would probably be easier if you could use Colo's HTMLMaker to upload a copy of this section of your worksheet.
 
Upvote 0
I think perhaps I do not explain myself well enough at times? (Obvious isn't it) :cry: Originally, I did want the result to be blank unless the whole week was filled in, true. Then I thought of running total grade sheets and the idea came why not a running total for this sheet? I did use Todd's example for W12 and then I had to modify the 10 range cell formulas. It all works well now with no type of errors whatsoever. I suspect if I was formally trained in Excel I would better unerstand the nuances of the functions and function combinations. (This is off the record but I cannot for the life of me figure out what is the difference between +or or *or) A lot of trial and error. However, I greatly appreciate all of your time and asisstance. As always, thank you. :biggrin:
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top