#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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,902
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) )
 

ken2step

Well-known Member
Joined
Jan 9, 2003
Messages
632
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.
 

KenWright

Active Member
Joined
Jan 14, 2005
Messages
267
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)
 

mpartyka

Board Regular
Joined
Nov 11, 2004
Messages
73

ADVERTISEMENT

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. :)
 

mpartyka

Board Regular
Joined
Nov 11, 2004
Messages
73
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?
 

ken2step

Well-known Member
Joined
Jan 9, 2003
Messages
632

ADVERTISEMENT

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!
 

Todd Bardoni

Well-known Member
Joined
Aug 29, 2002
Messages
3,042
=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))
 

KenWright

Active Member
Joined
Jan 14, 2005
Messages
267
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.
 

mpartyka

Board Regular
Joined
Nov 11, 2004
Messages
73
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:
 

Forum statistics

Threads
1,147,689
Messages
5,742,633
Members
423,745
Latest member
rtaylor1987

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
Top