Formula That Counts First Value Only In Array As A Condition

[mwheelock]

I need a formula that counts the first value only in an array as the
rest of the values in the array will be the same, but does not count
all the values in the array, that is, even if the
array(column)populates. This is the formula I have now and believe that
I am close but it has to be the first value and it could populate
anywhere in the array. This is what I have so far,

=IF(COUNTIF(G14:G34,"<1.85")/COLUMN(G14:G34),1-COUNTIF(G14:G34,"<1.85")/COLUMN(G14:G34)*COUNTIF(G14:G34,"<1.85")/COLUMN(G14:G34),"").


It works if one cell in the array is populated but when more than one
cell is populated it counts the other cells. I need it to only count
the first cell only. It's a % to match formula that will work off of
the very first values that populate the array. Thanks.

 

[vezerid]

An *array-formula* expression that will find the first value in a range
is:

=INDEX(G14:G34,MIN(IF(G14:G34<>"",ROW(G14:G34)-ROW(G14)+1)))

For example if I want to test if the first non-empty cell in G14:G34 is
0 and return "zero" if it is or twice the number if it is nonzero,

=IF(a,2*a,"zero")

Here a is the first non-empty cell. Then I would use the following
*array* formula:

=IF(INDEX(G14:G34,MIN(IF(G14:G34<>"",ROW(G14:G34)-ROW(G14)+1))),2*INDEX(G14:G34,MIN(IF(G14:G34<>"",ROW(G14:G34)-ROW(G14)+1))),"zero")

An *array* formula must be entered with the combination
Ctrl+Shift+Enter.

I based my example on yours, assuming that the COUNTIF construct
attempts to find the cell. Note that your formula will necessarily
return 0 if an element <1.85 is found.

HTH
Kostis Vezerides

 

[mwheelock]

I'm not quite sure what you're trying to calculate,
but see if this gets you pointed in the right direction:

Assuming the G14:G34 cells with values will each contain the same value or
be blank, these formulas test if that value is less than 1.85

=IF(COUNTIF(G14:G34,"<1.85")>0,1/COLUMN(G14:G34),"")
Or
=IF(COUNTIF(G14:G34,"<1.85")>0,MAX(G14:G34)/COLUMN(G14:G34),"")

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP





- Show quoted text -

Actually I am still at it. I believe that I am almost there but I
need one more thing. I need to test for the first value in an array
based on 2 arguments. Return the first value if:

1. The value is greater than 0

2. And if the value is greater than 0, it is the same value as all
the other values in the array.

So if I have 50% for the first value that populates I want to only
return that value if the other cells in that array are 50% or blank as
well, that is if they populate at all. There will be blanks
included.

I came up with this and believe that I am close. The "=" seems to be
giving me the problem. I think that you can see what I am trying to
do by looking at the formula. Thanks again.

=IF(AND(COUNTIF(Q16:Q336,">0",Q16:Q336,"=Q16:Q336'")/
COLUMN(Q16:Q336),MAX(Q16:Q336),""))