S.O.S.....
 

Hello,
I have a spreadsheet set up with column A for the date (one day per row),
and column D as the total daily takings. I want a formula that changes
automatically when I enter each new daily amount, and if I enter a daily
taking of $0, I don't want excel to include that day in the month's average.
So I want it to regognise that the $0 day is not to be included, and only
divide by say 29 days instead of 30 for June. Hope I've made sense...??
Thank you in anticipation.
--
Regards,
Sarah

S.O.S.....
 

Here's one way.

Try this array formula** :

=AVERAGE(IF(TEXT(A2:A20,"mmmyyyy")="Jun2007",IF(D2 :D200,D2:D20)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"Sarah" wrote in message
...
Hello,
I have a spreadsheet set up with column A for the date (one day per row),
and column D as the total daily takings. I want a formula that changes
automatically when I enter each new daily amount, and if I enter a daily
taking of $0, I don't want excel to include that day in the month's
average.
So I want it to regognise that the $0 day is not to be included, and only
divide by say 29 days instead of 30 for June. Hope I've made sense...??
Thank you in anticipation.
--
Regards,
Sarah

S.O.S.....
 

Thanks very much for your help!!! Much appreciated.
--
Regards,
Sarah


"T. Valko" wrote:

Here's one way.

Try this array formula** :

=AVERAGE(IF(TEXT(A2:A20,"mmmyyyy")="Jun2007",IF(D2 :D200,D2:D20)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"Sarah" wrote in message
...
Hello,
I have a spreadsheet set up with column A for the date (one day per row),
and column D as the total daily takings. I want a formula that changes
automatically when I enter each new daily amount, and if I enter a daily
taking of $0, I don't want excel to include that day in the month's
average.
So I want it to regognise that the $0 day is not to be included, and only
divide by say 29 days instead of 30 for June. Hope I've made sense...??
Thank you in anticipation.
--
Regards,
Sarah

S.O.S.....
 

"Sarah" wrote in message
...
Hello,
I have a spreadsheet set up with column A for the date (one day per row),
and column D as the total daily takings. I want a formula that changes
automatically when I enter each new daily amount, and if I enter a daily
taking of $0, I don't want excel to include that day in the month's
average.

If you don't include days of zero takings in the average then your average
will be wrong and misleading.

Gordon Burgess-Parker
Systems and Management Accountant

S.O.S.....
 

Okay, thanks Gordon. Is there an easier way to make this formula work?
Maybe I'm doing something wrong???
--
Regards,
Sarah


"Gordon" wrote:

"Sarah" wrote in message
...
Hello,
I have a spreadsheet set up with column A for the date (one day per row),
and column D as the total daily takings. I want a formula that changes
automatically when I enter each new daily amount, and if I enter a daily
taking of $0, I don't want excel to include that day in the month's
average.

If you don't include days of zero takings in the average then your average
will be wrong and misleading.

Gordon Burgess-Parker
Systems and Management Accountant

S.O.S.....
 

"Sarah" wrote in message
...
Okay, thanks Gordon. Is there an easier way to make this formula work?
Maybe I'm doing something wrong???

as there are no more than 31 days in any one month, do a SUM of your column
with the takings, in the cell on the 33rd row.....then use that to calculate
your average.

S.O.S.....
 

Hi Sarah

The Average function ignores blank cells anyway.
=AVERAGE(D2:D32)
will give your average for any month, providing you leave cells blank,
rather than entering 0.

If you do enter a 0, that is a valid number and the Average function
will take it into account.

--
Regards

Roger Govier


"Sarah" wrote in message
...
Okay, thanks Gordon. Is there an easier way to make this formula
work?
Maybe I'm doing something wrong???
--
Regards,
Sarah


"Gordon" wrote:

"Sarah" wrote in message
...
Hello,
I have a spreadsheet set up with column A for the date (one day per
row),
and column D as the total daily takings. I want a formula that
changes
automatically when I enter each new daily amount, and if I enter a
daily
taking of $0, I don't want excel to include that day in the month's
average.

If you don't include days of zero takings in the average then your
average
will be wrong and misleading.

Gordon Burgess-Parker
Systems and Management Accountant

S.O.S.....
 

"Roger Govier" wrote in message
...
Hi Sarah

The Average function ignores blank cells anyway.
=AVERAGE(D2:D32)
will give your average for any month, providing you leave cells blank,
rather than entering 0.

If you do enter a 0, that is a valid number and the Average function will
take it into account.


Which the OP should do if the average is to be meaningful....

S.O.S.....
 

You're welcome. Thanks for the feedback!

Biff

"Sarah" wrote in message
...
Thanks very much for your help!!! Much appreciated.
--
Regards,
Sarah


"T. Valko" wrote:

Here's one way.

Try this array formula** :

=AVERAGE(IF(TEXT(A2:A20,"mmmyyyy")="Jun2007",IF(D2 :D200,D2:D20)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"Sarah" wrote in message
...
Hello,
I have a spreadsheet set up with column A for the date (one day per
row),
and column D as the total daily takings. I want a formula that changes
automatically when I enter each new daily amount, and if I enter a
daily
taking of $0, I don't want excel to include that day in the month's
average.
So I want it to regognise that the $0 day is not to be included, and
only
divide by say 29 days instead of 30 for June. Hope I've made
sense...??
Thank you in anticipation.
--
Regards,
Sarah

S.O.S.....
 

I was under the assumption that the dates could be for any and all months.
If the dates listed are specific to only a single month and you still want
to exclude any hard 0s:

=SUM(D1:D31)/COUNTIF(D1:D31,"0")

Biff

"Roger Govier" wrote in message
...
Hi Sarah

The Average function ignores blank cells anyway.
=AVERAGE(D2:D32)
will give your average for any month, providing you leave cells blank,
rather than entering 0.

If you do enter a 0, that is a valid number and the Average function will
take it into account.

--
Regards

Roger Govier


"Sarah" wrote in message
...
Okay, thanks Gordon. Is there an easier way to make this formula work?
Maybe I'm doing something wrong???
--
Regards,
Sarah


"Gordon" wrote:

"Sarah" wrote in message
...
Hello,
I have a spreadsheet set up with column A for the date (one day per
row),
and column D as the total daily takings. I want a formula that
changes
automatically when I enter each new daily amount, and if I enter a
daily
taking of $0, I don't want excel to include that day in the month's
average.

If you don't include days of zero takings in the average then your
average
will be wrong and misleading.

Gordon Burgess-Parker
Systems and Management Accountant