Maximum/Minimum Formulas

[Drewmyster]

Hi there,

I'm having trouble using the formula above to work out what the largest and smallest figures are within a date range.

What I've been asked to do is find out the largest and smallest figures for any given month within a period going back 5 years or so.

My spreadsheet looks something like this going down;

Column A
Date
01/01/2011

Column B
Amount
£1,500

The formula I was shown on a previous topic to filter the month and year, doesn't seem to work properly, so please could somebody help me to fix it.

=MAX(IF($A$2:$A$30="June",IF(YEAR($A$2:$A$30)="2011",$B$2:$B$30)))

Many thanks.

 

[Jonmo1]

Try this enterd with CTRL + SHIFT + ENTER

=MAX(IF(TEXT($A$2:$A$30,"MMMMYYYY")="JUNE2011",$B$2:$B$30))

Hope that helps.

 

[Drewmyster]

Try this enterd with CTRL + SHIFT + ENTER

=MAX(IF(TEXT($A$2:$A$30,"MMMMYYYY")="JUNE2011",$B$2:$B$30))

Hope that helps.

I'd love to say it helped, but carefully following your formula, using CTRL + SHIFT + ENTER returns the following error message ;

"Microsoft Office Excel cannot calculate a formula. There is a circular reference in an open workbook, but the references that cause it cannot be listed for you. Try editing the last formula you entered or removing it with the Undo command."

Any further suggestions please?

 

[Jonmo1]

You cannot put the formula within a range referenced by the formula...

so it can't go in A2:A30 or B2:B30

 

[AlphaFrog]

This is another way (Ctrl+Shift+Enter)

=MAX(IF((MONTH($A$2:$A$30)=6)*YEAR($A$2:$A$30)=2011,$B$2:$B$30))