Excel 2007 : Averaging IF

**SEXYJANNY** · 07-31-2010, 11:27 AM

Dear all Excel guru,

I am a enviro student and came across an excel problem,

Basically to do with average temperature number

I have information for Column A, B and want to work out C and D

Column A Column B Column C Column D
Min Temp Max Temp (i want the average of A & B ( I want to work out the average
only IF both temp recorded temp for every 5 day but ONLY
for MIN and MAX if all 5 days has got temp, if
20 33 in anydays, there is no ave
18 (Blank) ? temp due to (Blank),
(Blank) 22 ?
22 34
18 19
(Blank) 23

Please please help me out, im very hopeless in excel!!!

many many kiss and thx in advance.

Please also see attachment if can spend sometimes or if i confused you ; ((

THanks

JAn

**SEXYJANNY** · 07-31-2010, 11:31 AM

GUys,

sorry ive confused you more, please see below table.

THANK

)) and waiting for your help

Day Month Year in DD/MM/YYYY format MAX TEMP MIN TEMP average temperature average 5 days temperature
01/12/1966 34.6 34.6 #DIV/0!
02/12/1966 BLANK 26.2 26.2 #DIV/0!
03/12/1966 BLANK #DIV/0! #DIV/0!
04/12/1966 32.3 32.3 27.67
05/12/1966 32.4 24.4 28.4 26.83
06/12/1966 31.9 20.3 26.1 25.29
07/12/1966 30.7 19.6 25.15 27.61
08/12/1966 32.2 20.6 26.4 28.32
09/12/1966 34.4 21.8 28.1 27.88
10/12/1966 20.7 20.7 27.71
11/12/1966 37.7 37.7 29.13
12/12/1966 35.7 21.7 28.7 27.54
13/12/1966 30.1 18.3 24.2 26.58
14/12/1966 31.8 22.7 27.25 28.96
15/12/1966 33.4 22.2 27.8 29.11
16/12/1966 35.2 24.3 29.75 29.22
17/12/1966 23.9 23.9 29.51

**shg** · 07-31-2010, 12:01 PM

Post a workbook with an example that shows your data layout and desired results.

**SEXYJANNY** · 08-01-2010, 06:50 AM

Hi Shg,

My worksheet sample is attachment.

Please help , thank ; ))))))

Jan

**gmahlert** · 08-01-2010, 08:11 AM

The formula In column D checks if the values in both columns B and C are numeric.
if this is true then calculate the average of the 2 numbers, if not leave a blank.
=IF( AND( is.number(B2) , is.number(c2) ) , AVERAGE(B2:C2) , "" )

The formula in column F works on the same principal, and could actually be accomplished by using the AND function again for five is.number() checks, but in the case of larger numbers of checks, it's better to use an array formula instead.

If the sum of 1 minus the is.number() checks is zero then do the average.
= IF ( SUM( 1 - is.number(D2:D6) ) = 0, AVERAGE(D2:D6) , "" )
Remember that you must use Control-Shift Enter for Array formulas.

**Marcol** · 08-01-2010, 09:26 AM

Try this

In D2

Please Login or Register  to view this content.

Drag/Fill Down

In F5

Please Login or Register  to view this content.

Drag/Fill Down
This will only display if there are 5 consecutive averages in Column D

Hope this helps

**SEXYJANNY** · 08-01-2010, 11:23 AM

Dear gmahlert and Marcol, much much appreciate your help on this.

So glad to have u guys and im loving this forum already.

hope to learn more from you.

Thanks for help

JAN ; )))))

**gmahlert** · 08-01-2010, 12:56 PM

Sorry for the confusion with extra dot in the isnumber function. I have an Italian version of Excel, so I can't just cut and paste the working formula. Instead I have to convert to English functions from memory.
see the difference in Italian Excel
=SE(E(VAL.NUMERO(B2);VAL.NUMERO(C2));MEDIA(B2:C2);"")
=SE(SOMMA(1-VAL.NUMERO(D2:D6))=0;MEDIA(D2:D6);"")