average if

  • Thread starter Thread starter Gaurav
  • Start date Start date
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
 
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.
 
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...
 
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))
 
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).
 
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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Worked. thanks a ton


Don Guillett said:
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))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Click to expand...
 
Back
Top