Compute MIN and MAX in table column according to some condition

Question

I have a datasheet with 5 columns, 3 for input values and 2 for output values A) Date B) Number C) Number

D) Number E) Number

Column B has set of dates with possible repetitions. Column C has the max value (and Column D has the min value) for an observation in a given date, which is the one in column B. Some values may be empty.

Given that in B I can have repetitions, I need to compute the following: for any given date in B, compute the max (or min) of all observations for that date and store it in column D (or E).

I cannot use MACROs.

Example. Suppose D1 and D2 are valid dates

Input:

A  B C
D1 9 3
D1 8 2
D2 7 5
D2   3

Output:

A  B C D E
D1 9 3 9 2 (the max for all dates of type D1 is 9, while the min is 2
D1 8 2 9 2
D2 7 5 7 3 (the max for all dates of type D2 is 7, while the min is 3
D2   3 7 3

How can I do that?

Does this answer help? http://stackoverflow.com/a/11570347/2258 — Richard Morgan, Jul 15 '14 at 14:56
possible duplicate of [Conditional Min and Max in Excel 2010](http://stackoverflow.com/questions/11570223/conditional-min-and-max-in-excel-2010) — MP24, Jul 15 '14 at 16:18

score 0 · Answer 1 · answered Jul 15 '14 at 15:30

0

=MIN(IF(A:A=A4,C:C)) works for me, if you add Control+Shift+Enter when you're in the cell.

It looks like excel adds {} around the whole formula when you do.

answered Jul 15 '14 at 15:30

adsweeny

26
3

that works is there are no empty cells. Otherwise, 0s are used as padding for those cells and 0 may become the min. – Manu Jul 16 '14 at 07:39

Compute MIN and MAX in table column according to some condition

1 Answers1