Calculate an average not including today

Andrea Westrich

I'm trying to write a formula that calculates the percentage of appointments held by our team. I have a Date Appt Set and a Yes or No for Appt Held. What I want to do is not include appointments scheduled for today (even though they are in the sheet), I want a rolling 30-day average and I don't want to include weekends in that 30 day average.

How can I do that in a single Summary formula?

Thanks

Heather Duff

Hi @Andrea Westrich ,

Try this:

=IFERROR(countifs([date appt set]:[date appt set],>TODAY(-31),[date appt set]:[date appt set],<TODAY(),[Appt Held]:[Appt Held],"Yes")/countifs([date appt set]:[date appt set],>TODAY(-31),[date appt set]:[date appt set],<TODAY()),"")

Let me know if it works for you.

Best,

Heather

Heather Duff

@Andrea Westrich Actually, I just did a test, and 41 is the magic number. No matter what 41-day date range you choose in the year, the net workdays is always 30. So, try this:

=IFERROR(countifs([date appt set]:[date appt set],>TODAY(-41),[date appt set]:[date appt set],<TODAY(),[Appt Held]:[Appt Held],"Yes")/countifs([date appt set]:[date appt set],>TODAY(-41),[date appt set]:[date appt set],<TODAY()),"")

Heather Duff

Hi @Andrea Westrich ,

Try this:

=IFERROR(countifs([date appt set]:[date appt set],>TODAY(-31),[date appt set]:[date appt set],<TODAY(),[Appt Held]:[Appt Held],"Yes")/countifs([date appt set]:[date appt set],>TODAY(-31),[date appt set]:[date appt set],<TODAY()),"")

Let me know if it works for you.

Best,

Heather

Andrea Westrich

@Heather D,

Thank you! The formula does work. Can you tell me how this eliminates weekends?

I appreciate your help!

Heather Duff

@Andrea Westrich Well, it doesn't really. I honestly skipped that part!

A follow-up question, though: do you have any appointments scheduled on weekends? Or, do you just want it to be a rolling 30 work days (so, approximately 38 days, which includes 4 weekends)? We can tweak the formula a bit.

Andrea Westrich

@Heather D, we do book appts on the weekend, but they are reported in totality on Monday. So, I want to show and average 30 working days. To do this I should change the formula to 38 (8 days a month)?

Heather Duff

@Andrea Westrich Actually, I just did a test, and 41 is the magic number. No matter what 41-day date range you choose in the year, the net workdays is always 30. So, try this:

=IFERROR(countifs([date appt set]:[date appt set],>TODAY(-41),[date appt set]:[date appt set],<TODAY(),[Appt Held]:[Appt Held],"Yes")/countifs([date appt set]:[date appt set],>TODAY(-41),[date appt set]:[date appt set],<TODAY()),"")