Help with Formula, Should I be using Max?

[Deb Pingel]

I have a spreadsheet that looks like this.
Date tank tank
1 2

1/10/06 999 7500



This data is kept on a daily basis. I am trying to find a formula that
will return the latest date, but only if the tank value is at 999, as
in tank 1
I have tried =MAX(IF(tank1=999,A3:A47," ")) returns Max of A3:A47
only
and =IF(tank1=999,MAX(A4:A48)," ") returns Max of A only
both return only the latetest date. What am I missing?
Thanks in advance
Deb

 

[Pete_UK]

The first formula is an array formula, so you should commit it using
CTRL-SHIFT-ENTER rather than the normal ENTER. If you do this correctly
then Excel will wrap curly braces { } around the formula - you should
not type these yourself. I would also suggest replacing the " " with 0
in both formulae.

I presume tank1 is a named range - it should cover the same number of
cells that your date range covers.

Hope this helps.

Pete

 

[Peo Sjoblom]

=MAX(IF(Tank_Range=999,Date_Range))

entered with ctrl + shift & enter

--
Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon

 

[Deb Pingel]

Dear Pete,
Thanks for looking at this. When I use your formula, I get a date, but
it isn't the most recent date with a 999 value. I checked my named
range and made sure the date range matches. I also added the "0" as
suggested.
The date returned is showing a value of 0, while the next date is
showing a 999. At least a date was returned!
=MAX(IF(tank1=999,A4:A45,0)) My formula
Deb

 

[Deb Pingel]

Hey Pete,
I messed with it a little more and checked my ranges again and now it
works.
It was adjusting down by one cell before, but my named range was off by
1 cell. Now it seems to be working.
Thanks so very much.
Deb

 

[Pete_UK]

Glad you got it working, thanks for feeding back.

Pete