Calculate daily adherence required to meet goal of 85% average over 4 weeks

[scoutfew]

Hi everyone,

May I please ask for assistance with working out a formula for my spreadsheet.

I am trying to calculate daily adherence required to meet the goal monthly adherence. Goal monthly adherence is 85% average over a 4 week period.

I would like to input daily adherence each day and have the spreadsheet tell me what would be required to meet that 85% average target. Is that possible?

This is my current idea:
The top section where I can input adherence data each day.
I was hoping I could create a formula where the bottom section would tell me what would be required over the month to meet the target 85%.

Any help is greatly appreciated!!!

- Scout.

 

[kevin9999]

Deleted

 

[kevin9999]

In future, you will probably get more assistance if you provide a sample of your data using the XL2BB add-in. Nobody wants to type out all your data from an image

I don't agree with the way you calculate the "Month average". You're using an average of weekly averages - which isn't the same thing as a true monthly average. Having said that, both solutions below end up giving you the same result anyway.

Book1
	A	B	C	D	E	F	G
1
2	IF the "Month average" is a true monthly average					Target	85%
3
4
5
6
7		Monday	Tuesday	Wednesday	Thursday	Friday	Wk Avg
8	Week1	83.00%	80.70%	82.00%	84.10%	85.00%	82.96%
9	Week2	84.00%	78.00%	83.50%	82.00%	84.00%	82.30%
10	Week3	85.00%	79.00%	86.00%	74.00%	85.00%	81.80%
11	Week4	51.00%					51.00%
12						Mnth Avg	80.39%
13
14
15
16	prediction
17		Monday	Tuesday	Wednesday	Thursday	Friday
18	Week1
19	Week2
20	Week3
21	Week4		103.43%	103.43%	103.43%	103.43%
22
Sheet2

Cell Formulas
Range	Formula
G8:G11	G8	=IFERROR(AVERAGE(B8:F8),"")
G12	G12	=IFERROR(AVERAGEIF(B8:F11,"<>",B8:F11),"")
B18:F21	B18	=IF(B8<>"","",($G$2*20-SUM($B$8:$F$11))/(COUNTBLANK($B$8:$F$11)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C18:G18,B18:F21	Expression	=B8=""	text	NO
G12	Expression	=G12>=0.85	text	NO
B8:G11	Expression	=B8>=0.85	text	NO

Book1
	A	B	C	D	E	F	G
1
2	IF the "Month average" is an average					Target	85%
3	of "weekly averages"
4
5
6
7		Monday	Tuesday	Wednesday	Thursday	Friday	Wk Avg
8	Week1	83.00%	80.70%	82.00%	84.10%	85.00%	82.96%
9	Week2	84.00%	78.00%	83.50%	82.00%	84.00%	82.30%
10	Week3	85.00%	79.00%	86.00%	74.00%	85.00%	81.80%
11	Week4	51.00%					51.00%
12						Mnth Avg	74.52%
13
14
15
16	prediction
17		Monday	Tuesday	Wednesday	Thursday	Friday
18	Week1
19	Week2
20	Week3
21	Week4		103.43%	103.43%	103.43%	103.43%
22
Sheet3

Cell Formulas
Range	Formula
G8:G11	G8	=IFERROR(AVERAGE(B8:F8),"")
G12	G12	=IFERROR(AVERAGE(G8:G11),"")
B18:F21	B18	=IF(B8<>"","",($G$2*20-SUM($B$8:$F$11))/(COUNTBLANK($B$8:$F$11)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B18:F21	Expression	=B8=""	text	NO
G12	Expression	=G12>=0.85	text	NO
B8:G11	Expression	=B8>=0.85	text	NO

 

[scoutfew]

Kevin you are an absolute legend!
Thank you and thank you for the XL2BB add in tip. I will be sure to use that in the future.
I hope you are having a great day!

- Scout.

 

[kevin9999]

Kevin you are an absolute legend!
Thank you and thank you for the XL2BB add in tip. I will be sure to use that in the future.
I hope you are having a great day!

- Scout.

Happy to help Scout, and thanks for the feedback