Calculating Max, Min, Ave etc of a large region
 

I have the following task:
1900 1901 1902 out til 2005
Jan 1
Jan 2
Jan 3
down to Dec 31

I want to creat a table for the Max, Min, Ave High, etc. for each month of
each year.
For example:
1900 1901 1902 etc
Jan
Feb

Can I use some array formula that will be copiable that will save me making
up hundreds of formulas?

Thanks

Calculating Max, Min, Ave etc of a large region
 

Jim

Assuming for data, dates (Jan 1 etc) as text in column A beginning at
row 3, and years in row 2 beginning at column B. For table, Jan, Feb
etc in column G beginning row 3, years in row 2 begining column H, use
the following. Expand/move to fit data as necessary.

=MAX((LEFT($A$3:$A$368,3)=$G3)*($B$2:$E$2=I$2)*$B$ 3:$E$368)

Enter as an array (CTRL+SHIFT+ENTER) and copy to remainder of table.

Regards

Murray

Calculating Max, Min, Ave etc of a large region
 

Thanks for tyhe help. I have noticed that if a whole month is negative
the MAX array comes up with Zero. If I change the array to MIN, it only
works for the below Zero temps. From Mar thru Nov it comes up with Zeros
for the minimum temp. It this a flaw in Excel?


*** Sent via Developersdex http://www.developersdex.com ***

Calculating Max, Min, Ave etc of a large region
 

Jim,
MAX and MIN work OK if you simply assign a range of data e.g
=MAX(a3:a32) but as you have found doesn't appear work with an array formula.
Perhaps a post to one of the gurus (Tom Ogilvy, Bob Philips et al) may get an
answer: no offence to Murray intended.

Just a thought: how are you handling leap years?

"postman Jim" wrote:


Thanks for tyhe help. I have noticed that if a whole month is negative
the MAX array comes up with Zero. If I change the array to MIN, it only
works for the below Zero temps. From Mar thru Nov it comes up with Zeros
for the minimum temp. It this a flaw in Excel?


*** Sent via Developersdex http://www.developersdex.com ***

Calculating Max, Min, Ave etc of a large region
 

Curious! It clearly pays to take into account the location of your
correspendent. In Northern Australia we don't get negative temps :-).

Off the top of my head I don't know the answer to this one - hopefully
one of the real gurus will step in as I would like to know the answer
myself.

Presumably zero is also a valid value, so the formula must take into
account positives, negatives and zeroes as valid data.

Toppers - no offence taken - I didn't see the flaw in my cunning plan.

Regards

Murray

Calculating Max, Min, Ave etc of a large region
 

Jim

It seems that the answer can be found on Chip Pearsons pages - try
he

http://www.cpearson.com/excel/lists.htm and look at the section dealing
with values in an interval.

Thus is you modify my original formula to look like this:

=MAX(IF(($B$3:$E$368=-1000)*($B$3:$E$368<=1000)*(LEFT($A$3:$A$368,3)=$G3 )*($B$2:$E$2=I$2),$B$3:$E$368,FALSE))

it should work OK. Note that the -1000 and 1000 are the LLim and ULim
described in Chip's formula - I have simply set them to what are
hopefully outrageous values.

Regards

Murray