I need help with worksheet function for searching and displaying the maximum value in excel. I have 3 input and 1 output columns defined as follows:
*COLUMN A: contains numerical values (mostly random).
*COLUMN B: represents first parameter, it can contain only text "text01" or "text02"
*COLUMN C: represents second parameter, it can contain only text "yes" or "no".
*COLUMN D: displays MAX values of the column A based on defined criteria from columns B and C.
The criteria for MAX function are following:
Return a maximal numerical value from numbers in column A using defined range of cells.
The range of cells for MAX function is defined like this: first cell of the range is defined by parameter in Column B, IF value in column B is "text01" the range will start with the cell in the next row (for example B1 = "text01", the MAX function range will start with cell A2). The end of the range is defined by parameter in Column C - the range continues only when value in column C is "yes". The end of the range is defined by the point where value "no" appears in the cell of Column C. For example: C2="yes", C3="yes", C4="no", that means the MAX function will use A4 as the last cell of the range.
The MAX function is only valid when column B value is "text01" and column C value of the next row is "yes" (for example B1 = "text01", C2="yes"). otherwise do not search for MAX value.
The MAX values for a corresponding ranges are shown in the column D, for each range the MAX value is displayed in the row that is defined by the beginning of the range. The other rows are filled with text "none".
What function should I type into column D? If possible please give both array and non-array type of formulas.
I've tried to use IF function for definition of the beginning of the MAX range, for example IF B="text01" then calc MAX value. For the MAX value range beginning, I used the cell in column A row number is +1 the row where the "text01"is located. The problem I'm struggling with is how to define the end cell of the range. I tried the OFFset function from the starting cell, but I don't know how to define the condition from the column C there.
Thank you.
I know the above description might by a little hard to understand therefore I enclose a little example of such table (found MAX values and its corresponding ranges are shown in bold):
A_______B__________C________D
2.1_____text02_____no_______none
9.8_____text01_____no_______none
5.4_____text02_____yes______6.2
1.7_____text02_____yes______none
4.6_____text02_____yes______none
6.2_____text02_____no_______none
7.3_____text02_____no_______none
8.5_____text02_____no_______none
9.7_____text01_____no_______none
6.7_____text02_____yes______9.1
9.1_____text02_____no_______none
9.9_____text02_____no_______none
9.1_____text01_____no_______none
1.1_____text02_____no_______none
1.9_____text02_____no_______none