Find MAX of column from using 2 criterias

  • Thread starter Thread starter Sunnyskies
  • Start date Start date
S

Sunnyskies

Spreadsheet (duh) with 3 columns:
Month, Vehicle Licence #, Odometer readings.

Now looking at the Month and Vehicle Licence #, what is the lowest odometer
reading and also what is the highest odometer reading, so as to calculate the
distance travelled.

Do not want to use Pivot tables - as columns will grow.
Do not want to use array formula as end user is as useless as putting butter
on bread with a toothpick.

Thanks in advance.
 
Hi,

Try this array formula.

=MIN(IF(MONTH(A1:A20)=D1,IF(B1:B20=E1,C1:C20)))

Change MIN to MAX to get the max value
D1 is a number i.e. 4 for April and E1 is the reg No you want. You can then
do a simple subtraction to get the mileage.


This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
OOPs,

Just spotted
Do not want to use array formula as end user is as useless as putting butter
on bread with a toothpick.
Click to expand...

I don't know how to do it without an array other than resorting to a UDF.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
Do not want to use Pivot tables
Do not want to use array formula
Click to expand...

A2:A15 = month
B2:B15 = licence #
C2:C15 = odometer readings

E1 = lookup month
F1 = lookup licence #

The max is fairly easy:

=SUMPRODUCT(MAX((A2:A15=E1)*(B2:B15=F1)*C2:C15))

The min is a bit convoluted:

=MIN(INDEX(((A2:A15=E1)*(B2:B15=F1)*C2:C15)+((A2:A15<>E1)+(B2:B15<>F1))*1E+100,0))

You could use array formulas and include a reminder for the user:

Array entered** :

=MIN(IF(A2:A15=E1,IF(B2:B15=F1,C2:C15)))+N("Array Entered - CSE")

=MAX(IF(A2:A15=E1,IF(B2:B15=F1,C2:C15)))+N("Array Entered - CSE")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
Back
Top