I'm trying to write a formula to return the AVERAGE by calendar quarter, IF
the quarter has ended.
For instance, IF Jan = 5, Feb = 20, Mar 5, Average would be 10.
But, if Mar was 0 (only meaning no activity) I still need it to calculate.

The formula I started with is -
=IF(A1=0,0,AVERAGE(A1:A3)

This works only if cells A1:A3 have a value greater than 0. How can I write
the formula so that as long as A3 is not blank that it will return the
AVERAGE?

Thanks!

Hi,

I'm sorry but i dont think i understand your question properly. If you want
to average all values greater than 0, use the following array formula
(Ctrl+Shift+Enter)

=average(if((range0),range))

Please pardon me if my interpretation of your question is incorrect. You
may mail me if you need any further clarifications.

Regards,

Ashish Mathur
India

"JMS" wrote:

I'm trying to write a formula to return the AVERAGE by calendar quarter, IF
the quarter has ended.
For instance, IF Jan = 5, Feb = 20, Mar 5, Average would be 10.
But, if Mar was 0 (only meaning no activity) I still need it to calculate.

The formula I started with is -
=IF(A1=0,0,AVERAGE(A1:A3)

This works only if cells A1:A3 have a value greater than 0. How can I write
the formula so that as long as A3 is not blank that it will return the
AVERAGE?

Thanks!

Just use

=AVERAGE(IF(A1:A3<"",A1:A3)

which is an array formula, so commit with Ctrl-Shift-Enter.

If the cells will contain 0, then use

=AVERAGE(IF(A1:A3<0,A1:A3)

instead

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JMS" wrote in message
...
I'm trying to write a formula to return the AVERAGE by calendar quarter,
IF
the quarter has ended.
For instance, IF Jan = 5, Feb = 20, Mar 5, Average would be 10.
But, if Mar was 0 (only meaning no activity) I still need it to calculate.

The formula I started with is -
=IF(A1=0,0,AVERAGE(A1:A3)

This works only if cells A1:A3 have a value greater than 0. How can I
write
the formula so that as long as A3 is not blank that it will return the
AVERAGE?

Thanks!

Thanks Bob, but that didn't quite get it.
What I have is a situation where I have each month of the year & enter
values based on a number of response each month. Some months may have
activity and have a number, other may have no activity and have 0 - ex:
Jan A1 10
Feb A2 20
Mar A3 0

Apr A4 10
May A5 20
Jun A6 30
etc....

Your suggestion returned the average for the given quarter even if the third
month had not been filled in yet. I want to wait to perform the average
until either a whole number or a 0 are entered.

Thanks for your help with this.

"Bob Phillips" wrote:

Just use

=AVERAGE(IF(A1:A3<"",A1:A3)

which is an array formula, so commit with Ctrl-Shift-Enter.

If the cells will contain 0, then use

=AVERAGE(IF(A1:A3<0,A1:A3)

instead

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JMS" wrote in message
...
I'm trying to write a formula to return the AVERAGE by calendar quarter,
IF
the quarter has ended.
For instance, IF Jan = 5, Feb = 20, Mar 5, Average would be 10.
But, if Mar was 0 (only meaning no activity) I still need it to calculate.

The formula I started with is -
=IF(A1=0,0,AVERAGE(A1:A3)

This works only if cells A1:A3 have a value greater than 0. How can I
write
the formula so that as long as A3 is not blank that it will return the
AVERAGE?

Thanks!

try adding another parameter
rngB0

--
Don Guillett
SalesAid Software

"JMS" wrote in message
...
Thanks Bob, but that didn't quite get it.
What I have is a situation where I have each month of the year & enter
values based on a number of response each month. Some months may have
activity and have a number, other may have no activity and have 0 - ex:
Jan A1 10
Feb A2 20
Mar A3 0

Apr A4 10
May A5 20
Jun A6 30
etc....

Your suggestion returned the average for the given quarter even if the
third
month had not been filled in yet. I want to wait to perform the average
until either a whole number or a 0 are entered.

Thanks for your help with this.

"Bob Phillips" wrote:

Just use

=AVERAGE(IF(A1:A3<"",A1:A3)

which is an array formula, so commit with Ctrl-Shift-Enter.

If the cells will contain 0, then use

=AVERAGE(IF(A1:A3<0,A1:A3)

instead

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JMS" wrote in message
...
I'm trying to write a formula to return the AVERAGE by calendar
quarter,
IF
the quarter has ended.
For instance, IF Jan = 5, Feb = 20, Mar 5, Average would be 10.
But, if Mar was 0 (only meaning no activity) I still need it to
calculate.

The formula I started with is -
=IF(A1=0,0,AVERAGE(A1:A3)

This works only if cells A1:A3 have a value greater than 0. How can I
write
the formula so that as long as A3 is not blank that it will return the
AVERAGE?

Thanks!

Thanks Ashish, would you see my reply to Bob's suggestion and see if this
helps to clarify the question?
Thanks.

"Ashish Mathur" wrote:

Hi,

I'm sorry but i dont think i understand your question properly. If you want
to average all values greater than 0, use the following array formula
(Ctrl+Shift+Enter)

=average(if((range0),range))

Please pardon me if my interpretation of your question is incorrect. You
may mail me if you need any further clarifications.

Regards,

Ashish Mathur
India

"JMS" wrote:

I'm trying to write a formula to return the AVERAGE by calendar quarter, IF
the quarter has ended.
For instance, IF Jan = 5, Feb = 20, Mar 5, Average would be 10.
But, if Mar was 0 (only meaning no activity) I still need it to calculate.

The formula I started with is -
=IF(A1=0,0,AVERAGE(A1:A3)

This works only if cells A1:A3 have a value greater than 0. How can I write
the formula so that as long as A3 is not blank that it will return the
AVERAGE?

Thanks!

I anticipated that by offering the alternative of

=AVERAGE(IF(A1:A3<0,A1:A3)


--

HTH

RP
(remove nothere from the email address if mailing direct)


"JMS" wrote in message
...
Thanks Bob, but that didn't quite get it.
What I have is a situation where I have each month of the year & enter
values based on a number of response each month. Some months may have
activity and have a number, other may have no activity and have 0 - ex:
Jan A1 10
Feb A2 20
Mar A3 0

Apr A4 10
May A5 20
Jun A6 30
etc....

Your suggestion returned the average for the given quarter even if the
third
month had not been filled in yet. I want to wait to perform the average
until either a whole number or a 0 are entered.

Thanks for your help with this.

"Bob Phillips" wrote:

Just use

=AVERAGE(IF(A1:A3<"",A1:A3)

which is an array formula, so commit with Ctrl-Shift-Enter.

If the cells will contain 0, then use

=AVERAGE(IF(A1:A3<0,A1:A3)

instead

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JMS" wrote in message
...
I'm trying to write a formula to return the AVERAGE by calendar
quarter,
IF
the quarter has ended.
For instance, IF Jan = 5, Feb = 20, Mar 5, Average would be 10.
But, if Mar was 0 (only meaning no activity) I still need it to
calculate.

The formula I started with is -
=IF(A1=0,0,AVERAGE(A1:A3)

This works only if cells A1:A3 have a value greater than 0. How can I
write
the formula so that as long as A3 is not blank that it will return the
AVERAGE?

Thanks!

Hi!

I want to wait to perform the average
until either a whole number or a 0 are entered.

=IF(COUNT(A1:A3)<3,"",AVERAGE(A1:A3))

Biff

"JMS" wrote in message
...
Thanks Bob, but that didn't quite get it.
What I have is a situation where I have each month of the year & enter
values based on a number of response each month. Some months may have
activity and have a number, other may have no activity and have 0 - ex:
Jan A1 10
Feb A2 20
Mar A3 0

Apr A4 10
May A5 20
Jun A6 30
etc....

Your suggestion returned the average for the given quarter even if the
third
month had not been filled in yet. I want to wait to perform the average
until either a whole number or a 0 are entered.

Thanks for your help with this.

"Bob Phillips" wrote:

Just use

=AVERAGE(IF(A1:A3<"",A1:A3)

which is an array formula, so commit with Ctrl-Shift-Enter.

If the cells will contain 0, then use

=AVERAGE(IF(A1:A3<0,A1:A3)

instead

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JMS" wrote in message
...
I'm trying to write a formula to return the AVERAGE by calendar
quarter,
IF
the quarter has ended.
For instance, IF Jan = 5, Feb = 20, Mar 5, Average would be 10.
But, if Mar was 0 (only meaning no activity) I still need it to
calculate.

The formula I started with is -
=IF(A1=0,0,AVERAGE(A1:A3)

This works only if cells A1:A3 have a value greater than 0. How can I
write
the formula so that as long as A3 is not blank that it will return the
AVERAGE?

Thanks!

Does this work for you:

=IF(COUNT(A1:A3)=3,AVERAGE(A1:A3),"Missing Info")
?
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"JMS" wrote in message
...
Thanks Bob, but that didn't quite get it.
What I have is a situation where I have each month of the year & enter
values based on a number of response each month. Some months may have
activity and have a number, other may have no activity and have 0 - ex:
Jan A1 10
Feb A2 20
Mar A3 0

Apr A4 10
May A5 20
Jun A6 30
etc....

Your suggestion returned the average for the given quarter even if the
third
month had not been filled in yet. I want to wait to perform the average
until either a whole number or a 0 are entered.

Thanks for your help with this.

"Bob Phillips" wrote:

Just use

=AVERAGE(IF(A1:A3<"",A1:A3)

which is an array formula, so commit with Ctrl-Shift-Enter.

If the cells will contain 0, then use

=AVERAGE(IF(A1:A3<0,A1:A3)

instead

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JMS" wrote in message
...
I'm trying to write a formula to return the AVERAGE by calendar
quarter,
IF
the quarter has ended.
For instance, IF Jan = 5, Feb = 20, Mar 5, Average would be 10.
But, if Mar was 0 (only meaning no activity) I still need it to
calculate.

The formula I started with is -
=IF(A1=0,0,AVERAGE(A1:A3)

This works only if cells A1:A3 have a value greater than 0. How can I
write
the formula so that as long as A3 is not blank that it will return the
AVERAGE?

Thanks!

Could you give me the full formula you're suggesting?
Thanks!

"Don Guillett" wrote:

try adding another parameter
rngB0

--
Don Guillett
SalesAid Software

"JMS" wrote in message
...
Thanks Bob, but that didn't quite get it.
What I have is a situation where I have each month of the year & enter
values based on a number of response each month. Some months may have
activity and have a number, other may have no activity and have 0 - ex:
Jan A1 10
Feb A2 20
Mar A3 0

Apr A4 10
May A5 20
Jun A6 30
etc....

Your suggestion returned the average for the given quarter even if the
third
month had not been filled in yet. I want to wait to perform the average
until either a whole number or a 0 are entered.

Thanks for your help with this.

"Bob Phillips" wrote:

Just use

=AVERAGE(IF(A1:A3<"",A1:A3)

which is an array formula, so commit with Ctrl-Shift-Enter.

If the cells will contain 0, then use

=AVERAGE(IF(A1:A3<0,A1:A3)

instead

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JMS" wrote in message
...
I'm trying to write a formula to return the AVERAGE by calendar
quarter,
IF
the quarter has ended.
For instance, IF Jan = 5, Feb = 20, Mar 5, Average would be 10.
But, if Mar was 0 (only meaning no activity) I still need it to
calculate.

The formula I started with is -
=IF(A1=0,0,AVERAGE(A1:A3)

This works only if cells A1:A3 have a value greater than 0. How can I
write
the formula so that as long as A3 is not blank that it will return the
AVERAGE?

Thanks!

Sorry Bob, but they both seemed to work the same.
If A3 (March) is blank (nothing entered in the cell) I want NO average
returned.
If A3 March has any number, including 0 I want the average for Jan, Feb,
March returned.
Thanks for your patience!

"Bob Phillips" wrote:

I anticipated that by offering the alternative of

=AVERAGE(IF(A1:A3<0,A1:A3)


--

HTH

RP
(remove nothere from the email address if mailing direct)


"JMS" wrote in message
...
Thanks Bob, but that didn't quite get it.
What I have is a situation where I have each month of the year & enter
values based on a number of response each month. Some months may have
activity and have a number, other may have no activity and have 0 - ex:
Jan A1 10
Feb A2 20
Mar A3 0

Apr A4 10
May A5 20
Jun A6 30
etc....

Your suggestion returned the average for the given quarter even if the
third
month had not been filled in yet. I want to wait to perform the average
until either a whole number or a 0 are entered.

Thanks for your help with this.

"Bob Phillips" wrote:

Just use

=AVERAGE(IF(A1:A3<"",A1:A3)

which is an array formula, so commit with Ctrl-Shift-Enter.

If the cells will contain 0, then use

=AVERAGE(IF(A1:A3<0,A1:A3)

instead

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JMS" wrote in message
...
I'm trying to write a formula to return the AVERAGE by calendar
quarter,
IF
the quarter has ended.
For instance, IF Jan = 5, Feb = 20, Mar 5, Average would be 10.
But, if Mar was 0 (only meaning no activity) I still need it to
calculate.

The formula I started with is -
=IF(A1=0,0,AVERAGE(A1:A3)

This works only if cells A1:A3 have a value greater than 0. How can I
write
the formula so that as long as A3 is not blank that it will return the
AVERAGE?

Thanks!

BINGO!
That got it.
Thanks everyone for your help.
Now, any suggestions for easily updating this formula into my worksheet in
60+ different areas?

Thanks again!

"Biff" wrote:

Hi!

I want to wait to perform the average
until either a whole number or a 0 are entered.

=IF(COUNT(A1:A3)<3,"",AVERAGE(A1:A3))

Biff

"JMS" wrote in message
...
Thanks Bob, but that didn't quite get it.
What I have is a situation where I have each month of the year & enter
values based on a number of response each month. Some months may have
activity and have a number, other may have no activity and have 0 - ex:
Jan A1 10
Feb A2 20
Mar A3 0

Apr A4 10
May A5 20
Jun A6 30
etc....

Your suggestion returned the average for the given quarter even if the
third
month had not been filled in yet. I want to wait to perform the average
until either a whole number or a 0 are entered.

Thanks for your help with this.

"Bob Phillips" wrote:

Just use

=AVERAGE(IF(A1:A3<"",A1:A3)

which is an array formula, so commit with Ctrl-Shift-Enter.

If the cells will contain 0, then use

=AVERAGE(IF(A1:A3<0,A1:A3)

instead

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JMS" wrote in message
...
I'm trying to write a formula to return the AVERAGE by calendar
quarter,
IF
the quarter has ended.
For instance, IF Jan = 5, Feb = 20, Mar 5, Average would be 10.
But, if Mar was 0 (only meaning no activity) I still need it to
calculate.

The formula I started with is -
=IF(A1=0,0,AVERAGE(A1:A3)

This works only if cells A1:A3 have a value greater than 0. How can I
write
the formula so that as long as A3 is not blank that it will return the
AVERAGE?

Thanks!