A sumif type of problem but need average figures...

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

Guest

Hi all,
It's been a while but I have a new problem. I have 3 columns as follows:
A B C
Date Day Quantity
1-May Tuesday 34,725
2-May Wednesday 38,601
3-May Thursday 40,115
4-May Friday 34,505
5-May Saturday 12,280
6-May Sunday 0
7-May Monday 0
8-May Tuesday 42,044

and so on for the month. What I need to calculate is the following....
calculate the average for a weekday so that I can give an estimate for the
month and also the same for Saturdays. The idea being that I can estimate
the total amount expected for the month based on current data for the month
to date. The average cannot include days with 0 as this could be a non
working day.

regards
 
Niall,

And "AverageIF" is just SUMIF(....)/COUNTIF(...)

=SUMIF(C1:C10,"<>0")/COUNTIF(C1:C10,"<>0")

HTH,
Bernie
MS Excel MVP
 
Hi Bernie,

Appreciate the formula - I had calculated this but require the formula to
calculate the average for days that are not equal to 0 and are equal to
weekdays only and the other formula to caculate those not equal to zero and
are Saturdays only and then also these 2 formulae to calculate the number of
weekdays left in the month and also the number of Saturdays left in the month.

Cheers
 
Niall,
calculate the average for days that are not equal to 0 and are equal to
weekdays only
Click to expand...

That will require a SUMPRODUCT formula, along the lines of

=SUMPRODUCT((WEEKDAY(A2:A23 said:
the other formula to caculate those not equal to zero and
are Saturdays only
Click to expand...

=SUMPRODUCT((WEEKDAY(A2:A23,2)=6)*(C2:C23<>0)*(C2:C23))/SUMPRODUCT((WEEKDAY(A2:A23,2)=6)*(C2:C23<>0))

The best way to calc the number of workdays and Saturdays depends on your data structure - Do you
have the dates in the table, and then fill in as you go?

HTH,
Bernie
MS Excel MVP
 
Back
Top