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

  • 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 Apr 3 '16 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 Apr 3 '16 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 Apr 3 '16 at 9:55
  • Ok thanks, done the edit and added this information in the question. – 3uchl0 Apr 3 '16 at 9:58
up vote 1 down vote accepted

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.

  • Thanks, works perfectly. Good idea with that MATCH function. – 3uchl0 Apr 4 '16 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 Apr 4 '16 at 9:25
  • Why are there two MAX functions? I think it also works with only one – 3uchl0 Apr 4 '16 at 13:46
  • Did you try it the multiplication of 2 arrays will give , comma separator between the values to calculate Max – user555689 Apr 4 '16 at 15:32
  • The second Max to notice the value with no and the range with yes – user555689 Apr 4 '16 at 15:33

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.