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

[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

 

[Bernie Deitrick]

Niall,

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

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

HTH,
Bernie
MS Excel MVP

 

[Guest]

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

 

[Bernie Deitrick]

Niall,

calculate the average for days that are not equal to 0 and are equal to
weekdays only

That will require a SUMPRODUCT formula, along the lines of

the other formula to caculate those not equal to zero and
are Saturdays only

=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