average if

G

Gaurav

Hi All,

I am trying to take out the average of all the values in column B which have
"Start of Day" in Coumn A. I tried =AVERAGE(IF(A:A="Start of Day",B:B)) but
doesnt work...need help.

Thanks
Gaurav
 
J

John Bundy

That is what is known as an array formula, when you are done, instead of just
hitting enter, you have to hit CTRL+Shift+Enter. If you did it right you
should se a {} around your formula.
 
G

Gaurav

I know..i tried that as well....it gives me a 0.

John Bundy said:
That is what is known as an array formula, when you are done, instead of
just
hitting enter, you have to hit CTRL+Shift+Enter. If you did it right you
should se a {} around your formula.
Click to expand...
 
G

Gaurav

Actually it gives me a 0 if i press enter and a #NUM! error if i press
CTRL+SHIFT+ENTER
 
D

Don Guillett

Don't use the whole column and Array enter with ctrl+shift+enter instead of
just enter
=AVERAGE(IF(A2:A22="Start of Day",B2:B22))
 
S

Stephen

I believe it's correct to say that you cannot use whole column (or row)
references in array formulas. At any rate, your formula will work if you
specify a range of rows:
=AVERAGE(IF(A1:A100="Start of Day",B1:B100))
(with CTRL+SHIFT+ENTER).
 
S

Sandy Mann

That is because array formuals cannot work on whole columns. Try setting a
range like:

=AVERAGE(IF(A1:A1000="Start of Day",B1:B1000))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
Top