Want to return blank cell if any cell in the range has a numeric value

Question

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.

score 1 · Accepted Answer · edited Mar 20 '17 at 10:17

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.

Want to return blank cell if any cell in the range has a numeric value

1 Answers1