Index, Match, Max

[GB]

Hi

I am trying to write an index match max formula, to return the maximum "Sales" value where "Week" = 1 and "Day" = Tue. There may be a more efficient formula so I am open to any suggestion. I have attached the simple spreadsheet and all the data has named ranges.


http://dl.dropbox.com/u/60464004/Excel/IndexMatchMax.xlsx


Thanks

Birko

 

[Hui]

Birko


Give this a try:

=MAX(IF(Week=1,IF(Day="Tues",Sales,),))

Ctrl Shift Enter


ps: Good use of Named Formulas it makes the formula so readable


You may want to consider the following changes to the Named Formula

Week: =OFFSET(Sheet1!$A$6,,,COUNTA(Sheet1!$A:$A)-1)

Day: =OFFSET(Week,,1)

Sales: =OFFSET(Week,,2)

Doing the above allows you to add or delete data and the Named Formulas will adjust accordingly

 

[GB]

Hi Hui,

thanks for your very quick response although it didn't work for me??? I evaluated the formula and the "Week" component returned a "#VALUE". Any suggestion to rectify?

thanks

Birko

 

[Hui]

Whooops

Also forgot to mention it is an array formula so enter it with Ctrl Shift Enter

 

[GB]

Thanks Hui, that works a treat.

cheers

Birko