0

I’m getting #VALUE errors because there are blank cells.  Is there a way get rid of this?  Also is there a way I can make it so that any numeric value in the range of cells will return ""?

=IF((B2:G2)<>0, "", IF(G2="out",-100,)+
                    IF(F2="out",-100,)+
                    IF(E2="out",-100,)+
                    IF(D2="out",-100,)+
                    IF(C2="out",-100,)+
                    IF(B2="out",-100,)
   )

The second question was asked because when 0 is present it returns a 0 in the cell.

This formula is to assign a numeric value based on the number of “out” days there are. But if all of the cell in the range have values, I want to return "".

------------------------------------------------------ For example ------------------------------------------------------

Here are some sample data sets (in Columns B-G) and the value I want to get (in Column H):

  B        C        D        E        F        G        H        I
day1      day2     day3     day4     day5     day6   Average    missed
-175       out                                         -175      -100           
-175       out                                         -175     `#VALUE`   
 325       100                                         212.5    `#VALUE`

Line 1 is what I need Line 2 & 3 are what I get

this is a performance sheet for a project the will base performance "Out" meaning you weren't there the average (h) should average only cells with a Numeric value and (I) should be blank if there is a numeric value or "" in all cells in the range B1:G1. I hope this helps out, not very good with spreadsheets and i am sure it took the long way around on a lot of this. Thanks for the help.

Matthew
  • 13
  • 1
    It isn't very clear what you're asking. Can you describe what values can be in the cells ( Can B2:G2 contain numerical values, zero, "out", or blank)? What, exactly, are you trying to accomplish with (B2:G2)<>0 at the beginning? What range of output values do you want? Can you elaborate on the logic (do all cells need to have a value? Is the blank issue for any single cell or all of the cells?)? – fixer1234 Dec 03 '15 at 07:16
  • I have made up some possible inputs and results.  I would be astounded if any of them are right; I intentionally made them ridiculous, but the layout should give you an idea of what we need — [edit] your question, and fill in the example data section with data values that you expect to appear and the results that you want to get from them.  Then, for good measure, explain/describe what you’re doing. – Scott - Слава Україні Dec 03 '15 at 08:05
  • I have edited the original post (thanks Scott) for the layout. the Cells in the range will be blank until the day comes up meaning this is a 6 day chart. I cant enter any thing in day 6 until day 6 is over. Out in any cell should return -100*(as many times as it shows up) there will be numbers from -1000 to 1000,or "out" after all 6 days are done just trying to see if blank cells can be ignored. – Matthew Dec 03 '15 at 20:32
  • I wish you had given more than one example of valid data and expected results.  I get the impression from your words that you want I3 to be blank, but I'm still struggling to understand your words.  I've provided an answer based on my (possibly flawed) understanding of what you want.  Thanks for responding. – Scott - Слава Україні Dec 03 '15 at 21:32
  • The reason for getting #VALUE doesn't appear to be blank cells given your example. The title doesn't relate to your example, either. Hopefully, Scott's answer solves your problem. If not, you'll need to explain the problem better. – fixer1234 Dec 03 '15 at 22:10

1 Answers1

1

Well,

=AVERAGE(B2:G2)

in H2 should get you the average of the numbers, ignoring blanks and non-numeric text.  For I2, a first attempt would be

=-100*COUNTIF(B2:G2, "=out")

which counts the cells in the row that contain (=) the word out.  This will display 0 if there are no cells containing out.  You seem to want it to display blank in this case.  A purely formulaic (but redundant) way of achieving this is

=IF(COUNTIF(B2:G2,"=out")=0, "", -100*COUNTIF(B2:G2,"=out"))

A well known alternative is to stick with the first formula, but format the cell as General;-General;, which causes it to display as blank even if it evaluates to 0.  See Display Blank when Referencing Blank Cell for other techniques.

Community
  • 1
Scott - Слава Україні
  • 21,717
  • Nice answer, and good work on the question. We can only hope that this is what the question meant. – fixer1234 Dec 03 '15 at 22:12
  • Scott yes that is exactly what I was looking for and it works fine. Sorry for any confusion in the question as I was very confused on how to ask it. That is probably why I could not find it on a google search. – Matthew Dec 03 '15 at 22:38