Track performance progress for the year

[Kentle]

Hello,
I'm currently trying to calculate performance against annual metrics. For example, my performance will be 18% of the expected metrics by the end of January. How do I create a dynamic formula to build into a speedometer graph to see if I am on target or lagging as the year progresses? Based on my current average, 18% for the end of January is good, but I need to see how that target looks daily.
I would be EXTREMELY thankful if someone could point me in the right direction.
thank you.

 

[awoohaw]

Posting your speedometer milestone points would be helpful.
Also, are you really looking for daily milestone or monthly(month to date included)?

Mr. excel has a tool called xl2bb add in. Link below. This allows you to share mini worksheets and helps the forum help you as they don't need to recreate your scenario which is time consuming, or make errors in assumptions which is frustrating.

Thanks in advance.

 

[Kentle]

Thank you for the early response. I tried downloading the xlbb add-in but kept getting an install error message.
I'm hoping my issue isn't as complex as I initially indicated.
I have three metrics that feed into one cell, representing the sum of those metrics as a percentage.
I want to take the value from that one cell and build it into a speedometer or other data visualization, which will show my performance dynamically as the year progresses. By my math, I have started the year strong at 18% of my annual goal, but I am concerned that my metrics are starting to drop.
It's long-winded, but I hope it's more descriptive of my conundrum than my original post.
Cheers and thanks to all.

 

[Kentle]

I'm trying to show my daily KPI progress for the year as a % to track my output on a daily basis..
For example, if my KPI measurement is 15% by the end of January, I should be in the 100 percentile.
I tried using the following formula, but I keep getting a 0. !!Grrrrrr!!
=SUM(1-J18)/(DATE(YEAR(TODAY())+1,1,1)-DATE(YEAR(TODAY())*1,1,1))
where J18 is my current overall percentage of 0.15
Any help cracking this mystery would be greatly appreciated.

 

[awoohaw]

I did this based on MyOnlineTrainingHub Youtube Tutorial. The link to that video is at the bottom.
These charts are not recommended for many dashboards and visualizations (as you can see by the empty real eastate below the horizontal.
The video shows how to make a stacked column type gauge chart that may be more effective.

Image is below, then the worksheet.
In the worksheet, LET functions may make it easier for you to follow.

Book3
	A	B	C	D	E	F	G	H	I	J	K
1
2	Yearly Goal:	1400
3
4	Date	Daily Performance	Aggregate Perfomrance	Agg Per % to Year		Doughnut Chart
5	2024-01-01	4	4	104.57%			Bands	Labels		Pie Chart
6	2024-01-02	4	8	104.57%		Red	50	50		Agg Pct to Expected YTD
7	2024-01-03	4	12	104.57%		Green	50	100		Performance	96.73%
8	2024-01-04	3	15	98.04%		Omitted	100	0		Marker	1%
9	2024-01-05	5	20	104.57%						Remainder	302.27%
10	2024-01-06	5	25	108.93%
11	2024-01-07	2	27	100.84%
12	2024-01-08	2	29	94.77%
13	2024-01-09	5	34	98.76%
14	2024-01-10	5	39	101.96%
15	2024-01-11	2	41	97.44%
16	2024-01-12	5	46	100.21%
17	2024-01-13	3	49	98.54%
18	2024-01-14	4	53	98.97%
19	2024-01-15	4	57	99.34%
20	2024-01-16	3	60	98.04%
21	2024-01-17	4	64	98.42%
22	2024-01-18	3	67	97.31%
23	2024-01-19	4	71	97.69%
24	2024-01-20	3	74	96.73%
25	2024-01-21
26	2024-01-22
27	2024-01-23
28	2024-01-24
29	2024-01-25
30	2024-01-26
31	2024-01-27
32	2024-01-28
33	2024-01-29
34	2024-01-30
35	2024-01-31
36	2024-02-01
37	2024-02-02
38	2024-02-03
Speedometer Chart

Cell Formulas
Range	Formula
C5:C24	C5	=SUM($B$5:B5)
D5:D24	D5	=$C5/($B$2*(COUNT($B$5:B5)/COUNT($A$5:$A$370)))
K7	K7	=(SUM(($B$5:$B$370)*($B$5:$B$370<>""))/($B$2*SUM(--($B$5:$B$370<>""))/(EOMONTH(DATE(2024,1,1),11)-DATE(2024,1,1)+1)))
K9	K9	=4-(K7+K8)

 

[Kentle]

I did this based on MyOnlineTrainingHub Youtube Tutorial. The link to that video is at the bottom.
These charts are not recommended for many dashboards and visualizations (as you can see by the empty real eastate below the horizontal.
The video shows how to make a stacked column type gauge chart that may be more effective.

Image is below, then the worksheet.
In the worksheet, LET functions may make it easier for you to follow.

View attachment 106263

Book3
	A	B	C	D	E	F	G	H	I	J	K
1
2	Yearly Goal:	1400
3
4	Date	Daily Performance	Aggregate Perfomrance	Agg Per % to Year		Doughnut Chart
5	2024-01-01	4	4	104.57%			Bands	Labels		Pie Chart
6	2024-01-02	4	8	104.57%		Red	50	50		Agg Pct to Expected YTD
7	2024-01-03	4	12	104.57%		Green	50	100		Performance	96.73%
8	2024-01-04	3	15	98.04%		Omitted	100	0		Marker	1%
9	2024-01-05	5	20	104.57%						Remainder	302.27%
10	2024-01-06	5	25	108.93%
11	2024-01-07	2	27	100.84%
12	2024-01-08	2	29	94.77%
13	2024-01-09	5	34	98.76%
14	2024-01-10	5	39	101.96%
15	2024-01-11	2	41	97.44%
16	2024-01-12	5	46	100.21%
17	2024-01-13	3	49	98.54%
18	2024-01-14	4	53	98.97%
19	2024-01-15	4	57	99.34%
20	2024-01-16	3	60	98.04%
21	2024-01-17	4	64	98.42%
22	2024-01-18	3	67	97.31%
23	2024-01-19	4	71	97.69%
24	2024-01-20	3	74	96.73%
25	2024-01-21
26	2024-01-22
27	2024-01-23
28	2024-01-24
29	2024-01-25
30	2024-01-26
31	2024-01-27
32	2024-01-28
33	2024-01-29
34	2024-01-30
35	2024-01-31
36	2024-02-01
37	2024-02-02
38	2024-02-03
Speedometer Chart

Cell Formulas
Range	Formula
C5:C24	C5	=SUM($B$5:B5)
D5:D24	D5	=$C5/($B$2*(COUNT($B$5:B5)/COUNT($A$5:$A$370)))
K7	K7	=(SUM(($B$5:$B$370)*($B$5:$B$370<>""))/($B$2*SUM(--($B$5:$B$370<>""))/(EOMONTH(DATE(2024,1,1),11)-DATE(2024,1,1)+1)))
K9	K9	=4-(K7+K8)

Thank you for being so supportive. Your recommendation was the catalyst to point out that I needed more than a simple formula.
I ended up building a temperature gauge using

 

[Kentle]

Thank you for being so supportive. Your recommendation was the catalyst to point out that I needed more than a simple formula.
I ended up building a temperature gauge (I did see the YouTube video you posted).

I had approached the process from a completely wrong conception. I have attached a picture of the data I ended up using.
Thank you again.