Finding highest/lowest values over a set date period

[Terry-J]

Hi,

I am trying to set up a look up to find the highest value over the past 15 days in a list for a specific criteria.
I can achieve finding the highest value but not ovr the last 15 days.
Bear in mind the last date range for the last 15 days with increment by 1 day every day so the date range will be different each day I run the look up.
See attached spreadsheet.


Any help would be appreciated

Terry

[Pete_UK]

Do you mean to measure the time from today's date, or from the last date recorded for the particular company (28/09/2016 for A and 27/09/2016 for B)?

If the 15 days is measured from today, then you can put the company names A and B in cells F27 and F28, and put this array* formula in G27:

=MAX(IF(($B$2:$B$50=F27)*($A$2:$A$50>=TODAY()-15),$C$2:$C$50))

and copy this into G28 (or further if you have more than 2 names).

*NOTE: an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual < Enter >.

Note also that I have made the ranges down to row 50 so you can add some more data, but you will need to increase this if you have even more data.

Hope this helps.

Pete

[Terry-J]

Hi Pete,

That's spot on. It was from Today's date so works fine.

Thanks a bunch,

Terry

[Terry-J]

Pete,

I have a slight modification to the arrangement.
I have no requirement for the first part of the function ..(($B$2:$B$50=F27) - the bit which looks for the company names (A or B).
It is simply a question of looking back 10 periods from the current date (Todays Date) and finding the heighest value.

Tried re-arranging the formula but no joy.

Can you assist please?

Terry

[Pete_UK]

Thanks for the rep, Terry.

If you just want to find the highest value within the last 15 days, from ANY company, then the formula would be this:

=MAX(IF($A$2:$A$50>=TODAY()-15,$C$2:$C$50))

This is still an array formula, so you need to commit it using CSE, as previously advised.

Hope this helps.

Pete

[Terry-J]

Pete,

Many thanks

Terry