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.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Gaurav" wrote:

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

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

"John Bundy" (remove) wrote in message
...
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.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Gaurav" wrote:

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

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

"John Bundy" (remove) wrote in message
...
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.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Gaurav" wrote:

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

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

"Gaurav" wrote in message
...
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

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).

"Gaurav" wrote in message
...
Actually it gives me a 0 if i press enter and a #NUM! error if i press
CTRL+SHIFT+ENTER

"John Bundy" (remove) wrote in message
...
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.
--
-John
Please rate when your question is answered to help us and others know
what
is helpful.


"Gaurav" wrote:

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 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


Replace @mailinator.com with @tiscali.co.uk


"Gaurav" wrote in message
...
Actually it gives me a 0 if i press enter and a #NUM! error if i press
CTRL+SHIFT+ENTER

"John Bundy" (remove) wrote in message
...
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.
--
-John
Please rate when your question is answered to help us and others know
what
is helpful.


"Gaurav" wrote:

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

Worked. thanks a ton


"Don Guillett" wrote in message
...
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

"Gaurav" wrote in message
...
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