AVERAGE AND or IF?

[ben_horlock]

Hi guy's and gals, your help would be appreciated.

Description of sheet>

Data collected across sheet in columns, twice daily.
Column header is date but also have a row listing the week number.
Row's filled with data, although some are left blank due to missing
data.

What I'm trying to achieve>

I am trying to get an average for a week number but only wish to
include the numbers that have been entered (not average all including
the blank cells).

What I have tried>

{=AVERAGE(IF(AND(E3:IV3=49,E29:IV29>0),E29:IV29,FALSE),E29:IV29)}

E3:IV3 is the range that contains the week numbers for each day.
E29:IV29 is the range containing the data I am trying to average.


I appologise if this is poorly worded. It's my first post so be
gentle.

Thanks for your help.

 

[Paul Falla]

Dear Ben

The AVERAGE() function only looks at non-empty cells in
your range, so there is no need to prepare convoluted
formulae to make it look only at cells which contain data.

Thus your formula would read =AVERAGE(E29:IV29)ie the
range containing the data you wish to average, and not the
range containing the week numbers.

Hope this helps

Paul Falla

 

[Jerry W. Lewis]

You assume that the OP wants to average all entered data in the range
E29:IV29. The OP talks about only averaging for a week number, so it is
possible that E29:IV29 also contains data from other weeks. An IF()
within the AVERAGE() would produce zeros corresponding to empty cells,
which would then have to be explicitly ignored.

The OP's original formula is almost right, but as posted, it averages
all data from E29:IV29 plus a second copy of those values from week 49
(developing with COUNT() or SUM() instead of AVERAGE() would make it
easier to debug). Drop the unconditional portion of the average and it
should work.

{=AVERAGE(IF(AND(E3:IV3=49,E29:IV29>0),E29:IV29,FALSE))}

The ",FALSE" within the IF is not necessary, so it could be shortened to

{=AVERAGE(IF(AND(E3:IV3=49,E29:IV29>0),E29:IV29))}

If negative numbers or zeros might be entered in E29:IV29 and should be
included in the average, then you would use the more general formula

{=AVERAGE(IF(AND(E3:IV3=49,ISNUMBER(E29:IV29)),E29:IV29))}

where in all cases the curly brackets signify array entry
(Ctrl-Shift-Enter) rather than characters that you type.

Jerry

 

[ben_horlock]

Guys,

Jerry you've hit the nail on the head with the problem I am
experiencing (i.e. the average including the empty cells as zero's).
The formula Paul describes is what I started with before I realised
that is was including the zeros.

However the formula you have described still does not work. Using the
evaluate formula function it would appear that the formula nearly
works as it returns the correct TRUE and FALSES, however it then
rationalises all of these into a false. Therefore the result I get is
zero. I'll have a go at trying to layout an approximate version of the
sheet to try and explain. Note that the blank wants to be there, but
does not want to be included in the calculation. Assume below starts
in cell A1.

WEEK 1 1 1 1 1 2 2 2 2 3 3 4 4 4
DATA 2 4 2 4 8 8 8 2 2 1 1

What I want:
Average for week 1= AVERAGE(2+4+2+4)= 12/4 = 3
Average for week 2= AVERAGE(8+8+8+2)= 26/4 = 6.5
Average for week 3= AVERAGE(2)= 2/1 = 2
Average for week 4= AVERAGE(1+1)= 2/2 = 1

What I get using {=AVERAGE(IF(AND($$B1:$O$1=1,$B$2:$O$2>0),$B$2:$O$2))}
(array function being used)

Average for week 1= 0
Average for week 2= 0
Average for week 3= 0
Average for week 4= 0

The Evaluate formula command gives the following

Week 1:
=AVERAGE(IF(AND({TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE},{TRUE,TRUE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,TRUE,FALSE,TRUE}),$B$2:$O$2))

which seems right until I go a step further and it turns this into....

=AVERAGE(IF(FALSE,$B$2:$O$2)

which results in 0.

Confusing huh?

I really appreciate your help guys, I'm sure I've killed hundreds of
braincells trying to work this out. There must be a way?

Ben.

 

[JE McGimpsey]

This produces the results you want:

=SUMPRODUCT(--(B1:O1=1),B2:O2)/SUMPRODUCT(--(B1:O1=1),--(B2:O2>0))

 

[Jerry W. Lewis]

The formula should work, but apparently the AND() function does not work
properly in array formulas. Replace
AND($$B1:$O$1=1,$B$2:$O$2>0)
with
($$B1:$O$1=1)*($B$2:$O$2>0)

For OR(), you would use "+"). I tried to leave the answer in a format
as close to your original as possible, but failed to test (I always use
"*" and "+" instead of AND() and OR()); sorry.

Jerry

 

[ben_horlock]

I know it's easy to say this afterwards, but I swear on my life that
is the exact formula I have been trying to write.

A big big thanks to you for that! I can now sleep at night!