S.O.S.....

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sarah - 12 Jun 2007 05:43 GMT

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.

Signature

Regards,
Sarah

Reply to this Message

T. Valko - 12 Jun 2007 06:40 GMT

Here's one way.

Try this array formula** :

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

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

Biff

> Hello,
> I have a spreadsheet set up with column A for the date (one day per row),
[quoted text clipped - 5 lines]
> divide by say 29 days instead of 30 for June. Hope I've made sense...??
> Thank you in anticipation.

Reply to this Message

Sarah - 12 Jun 2007 07:33 GMT

Thanks very much for your help!!! Much appreciated.

Signature

Regards,
Sarah

> Here's one way.
>
[quoted text clipped - 16 lines]
> > divide by say 29 days instead of 30 for June. Hope I've made sense...??
> > Thank you in anticipation.

Reply to this Message

T. Valko - 12 Jun 2007 18:40 GMT

You're welcome. Thanks for the feedback!

Biff

> Thanks very much for your help!!! Much appreciated.
>
[quoted text clipped - 22 lines]
>> > sense...??
>> > Thank you in anticipation.

Reply to this Message

Gordon - 12 Jun 2007 07:35 GMT

> 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

Reply to this Message

Sarah - 12 Jun 2007 10:25 GMT

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

Signature

Regards,
Sarah

> > Hello,
> > I have a spreadsheet set up with column A for the date (one day per row),
[quoted text clipped - 8 lines]
> Gordon Burgess-Parker
> Systems and Management Accountant

Reply to this Message

Gordon - 12 Jun 2007 11:19 GMT

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

Reply to this Message

Roger Govier - 12 Jun 2007 13:01 GMT

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.

Signature

Regards

Roger Govier

> Okay, thanks Gordon. Is there an easier way to make this formula
> work?
[quoted text clipped - 16 lines]
>> Gordon Burgess-Parker
>> Systems and Management Accountant

Reply to this Message

Gordon - 12 Jun 2007 13:52 GMT

> Hi Sarah
>
[quoted text clipped - 5 lines]
> 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....

Reply to this Message

T. Valko - 12 Jun 2007 19:57 GMT

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

> Hi Sarah
>
[quoted text clipped - 25 lines]
>>> Gordon Burgess-Parker
>>> Systems and Management Accountant

Reply to this Message