0

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:

  1. Return a maximal numerical value from numbers in column A using defined range of cells.

  2. 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.

  3. 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.

  4. 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

Improve this question
4
  • Please note that Super User is not a script writing service. If you tell us what you have tried so far (including any scripts you are using) and where you are stuck then we can try to help with specific problems. You should also read How do I ask a good question?.
    – DavidPostill ♦
    Commented Apr 3, 2016 at 9:39
  • Hi, 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 dont know how to define the condition from the column C there. That's about it.
    – 3uchl0
    Commented Apr 3, 2016 at 9:54
  • Please edit the question and add this information. The question should be self-contained and not rely on something in comments (which are temporary).
    – DavidPostill ♦
    Commented Apr 3, 2016 at 9:55
  • Ok thanks, done the edit and added this information in the question.
    – 3uchl0
    Commented Apr 3, 2016 at 9:58

1 Answer 1

Reset to default
2

Try this in D2
=IF(AND(B1="text01",C2="yes"),MAX(MAX(($A$1:$A$15)*($C$2:$C$15="yes"),INDIRECT("A"&ROW()-1+MATCH("no",$C2:$C$15,0)))),"none")

A1:A15, C1:C15 is the range of Data change it to correspond your range.

Improve this answer
5
  • Thanks, works perfectly. Good idea with that MATCH function.
    – 3uchl0
    Commented Apr 4, 2016 at 9:21
  • One additional question, I've noticed you used ($A$1:$A$15)*($C$2:$C$15="yes") as definition of the criteria that it is only valid for those rows that contain "yes". Does this concept have some general name? I'm curious why there is * between those ranges. Is there another way to do this, for example with AND function?
    – 3uchl0
    Commented Apr 4, 2016 at 9:25
  • Why are there two MAX functions? I think it also works with only one
    – 3uchl0
    Commented Apr 4, 2016 at 13:46
  • Did you try it the multiplication of 2 arrays will give , comma separator between the values to calculate Max
    – user555689
    Commented Apr 4, 2016 at 15:32
  • The second Max to notice the value with no and the range with yes
    – user555689
    Commented Apr 4, 2016 at 15:33

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .