Tweak formula to not count blanks as 0

[andy62]

I need to modify this array-entered formula to not count blanks as zero. The
challenge seems to be that the cobination of INDIRECT() and N() is converting
blanks to zero.

{=MIN(N(INDIRECT("Rater" & ROW(1:4) & "!G"&$B$3)))}

In plain english, the function looks at the same cell (e.g., G7, when cell
B3 is set to 7) across 4 worksheets (Rater1 thru Rater4).

I don't need to first function to be MIN, it could be a COUNT. I just need
it to stop treating blanks as 0.

TIA

 

[Luke M]

Actually, your INDIRECT function is incorrect, and is generating the #VALUE!
error, which is then converted to 0 by you N function. You do not have your
indirect function returning the proper formatting of an array callout.

Also, the N function does not return an array of numbers, thus defeating the
purpose of your equation. The better question is why you are attempting to
use the N function?

If you could provide more detail about the data you're working with, and
what you are trying to accomplish (MIN number, or count??) we can probably
help you.

 

[andy62]

Hi Luke (and/or others) - Happy to elaborate, although was trying to keep it
simple and not scare off everyone with all the gory details. The function is
intended to check if the data in the four cells is a) all 1's, b) all 0's, or
c) a mix. If either a) or b) are true the function should produce that
single digit. If the function is false then it returns a concatenated string
of all the data. Here goes:

{=IF(MIN(N(INDIRECT("Rater" & ROW(1:4) & "!B"&$B$3)))=MAX(N(INDIRECT("Rater"
& ROW(1:4) & "!B"&$B$3))),MAX(N(INDIRECT("Rater" & ROW(1:4) &
"!B"&$B$3))),CONCATENATE(INDIRECT("Rater1!B"&$B$3),"
",INDIRECT("Rater2!B"&$B$3)," ",INDIRECT("Rater3!B"&$B$3),"
",INDIRECT("Rater4!B"&$B$3)))}

I used (with help from someone on this board) the "MIN=MAX" construct to
efficiently cover conditions a or b (when all four cells match, MIN=MAX) and
return the correct output. It seems to work fine - including the false
condition - except when one of the cells is blank. for instance, three 1's
and a blank produce the following:

1 1 1

which is all four cells concatenated together. So it is treating the blank
cell as 0 or as you suspect an error that gets concerted to 0, thus MIN<>MAX.
I want it to consider three 1's and a blank as all 1's, and return a 1.

Hope that helps!

 

[Shane Devenshire]

Hi,

The basic idea is to use something like this

=MIN(IF(A4:A11<>0,A4:A11,""))

Of course this is an array formula.

If you are using N to convert numbers entered as text to numbers then modify
this idea to avoid the N:

=MIN(IF(A4:A11<>0,--A4:A11,""))

 

[andy62]

But that doesn't accommodate piecing together the reference (the use of
INDIRECT) or working across four worksheets.

The underlying data is either 1, 0, . (period), or blank. The 1's and 0's
are entered as numbers. To be candid I'm not clear why the previous person
used the N function; I thought it was because the INDIRECT function returned
numbers as text, but maybe it was for another reason.