Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula That Counts First Value Only In Array As A Condition
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula That Counts First Value Only In Array As A Condition
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<"",R OW(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 wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula That Counts First Value Only In Array As A Condition
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 " wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula That Counts First Value Only In Array As A Condition
On Jan 23, 2:43 pm, Ron Coderre
wrote: 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 " wrote: 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")/COL*UMN(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.- Hide quoted text - - 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),"")) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find value in array | Excel Worksheet Functions | |||
Use of Offset function in array formula | Excel Worksheet Functions | |||
SUMPRODUCT clue needed | Excel Discussion (Misc queries) | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |