Using the LARGE function conditionally

[Pauljj]

Ihave a column (A) with days of the week in and column B with sales figures.

I need to use the LARGE function to work out what the 1st, 2nd etc etc largest sales days are based on the day of the week. So the best Monday, the best Tuesday and so on. does anyone have any ideas on this ?

Thanks

Paul

 

[Jonmo1]

=LARGE(IF(WEEKDAY(A1:A40)=2,B1:B40),1)

The bolded 2 is the number to indicate Weekday.
1=Sun
2=Mon
etc..

Bolded 1 is the # to change for nth largest..

Formula Confirmed with CTRL + SHIFT + ENTER

 

[Brian from Maui]

Ihave a column (A) with days of the week in and column B with sales figures.

I need to use the LARGE function to work out what the 1st, 2nd etc etc largest sales days are based on the day of the week. So the best Monday, the best Tuesday and so on. does anyone have any ideas on this ?

Thanks

Paul

Are the days of the week true date values?

 

[Pauljj]

Hi there Jonmo, I have tried this with minor modification

LARGE(IF(WEEKDAY('2006-2007'!BS$4:BS$1500)=2,'2006-2007'!CV$4:CV$1500),ROW(A2)-ROW(A$1))

then confirming with Ctrl + **** + Enter

but I get a #VALUE error ??

 

[Pauljj]

Sorry this does work, for some reason my calculations are all very slow, I think its because I have a lot of arrays on the sheet


many thanks for you help

Paul