S.O.S.....

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
Thanks very much for your help!!! Much appreciated.
--
Regards,
Sarah


T. Valko said:
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
Click to expand...
 
Sarah said:
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.
Click to expand...

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
 
Okay, thanks Gordon. Is there an easier way to make this formula work?
Maybe I'm doing something wrong???
 
Sarah said:
Okay, thanks Gordon. Is there an easier way to make this formula work?
Maybe I'm doing something wrong???
Click to expand...

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.
 
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.
 
Roger Govier said:
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.
Click to expand...

Which the OP should do if the average is to be meaningful....
 
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
 
Back
Top