Require a formula help to return the total number of working days and the total percentages

[mmr1]

I needed a one-cell formula help to return the total number of working days and the total percentages based on the day-by-day data as shown in the expected results.

If the cell has SL text value, it is considered a working day


sample sheet with expected results enclosed.

Many thanks,

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
3																																Expected Results
4	25	26	27	28	29	30	31	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	Days	Percentage
5	10	10	OFF	10	10	10	10	10	10	OFF	10	10	10	10	10	10	OFF	10	10	10	10	LV	LV	LV	LV	LV	LV	LV	LV	LV	LV	1.8	7%
6	0	100	OFF	0	0	0	0	0	0	OFF	LV	LV	LV	LV	LV	LV	LV	LV	LV	LV	LV	LV	LV	LV	LV	LV	LV	LV	LV	LV	LV	1	4%
7	100	100	OFF	100	100	100	100	100	100	OFF	100	100	100	100	100	100	OFF	100	100	LV	LV	100	100	OFF	100	100	100	100	100	100	OFF	24	92%
8	10	10	OFF	LV	LV	LV	LV	LV	LV	LV	LV	LV	LV	LV	10	10	OFF	10	10	10	10	10	10	OFF	10	10	10	10	10	10	OFF	1.6	6%
9	100	100	OFF	100	100	100	100	100	100	OFF	100	100	100	100	100	100	OFF	100	100	100	100	100	100	OFF	100	100	100	100	100	100	OFF	26	100%
10	100	100	OFF	100	100	100	100	100	100	OFF	100	100	100	100	100	100	OFF	100	100	100	100	100	100	OFF	100	100	100	100	100	100	OFF	26	100%
11	LV	LV	LV	LV	LV	LV	LV	LV	LV	LV	LV	LV	LV	LV	50	50	OFF	50	50	50	50	50	50	OFF	50	50	50	50	50	50	OFF	7	27%
12	LV	LV	LV	LV	LV	LV	LV	LV	LV	LV	LV	LV	LV	LV	LV	100	OFF	100	100	100	100	100	100	OFF	100	100	100	100	100	100	OFF	13	50%
13	0	0	OFF	100	100	100	100	100	100	OFF	100	100	100	0	0	0	OFF	0	0	0	0	0	0	OFF	0	0	SL	SL	SL	SL	OFF	13	50%
14	100	100	OFF	100	100	100	100	100	100	OFF	100	100	100	100	100	100	OFF	100	100	100	100	100	100	OFF	100	100	100	100	100	100	OFF	26	100%
15	100	100	OFF	100	100	100	100	100	100	OFF	100	100	100	100	100	100	OFF	100	100	100	100	100	100	OFF	100	100	100	100	100	100	OFF	26	100%
Sheet1

 

[Fluff]

This seems to get the days

=BYROW(A5:AD15,LAMBDA(br,SUM(IF(br="sl",100,br))/100))

but may be wrong as you have explained how you get those results.

How are you calculating the percentage?

 

[mmr1]

Many thanks for your response.

Formula used for calculating days

AF5 formula=SUM(A5:AE5)/MAX(UNIQUE($A$5:$AE$15)) (SL Condition not included in the formula)

Formula used for calculating percentage

AG5 formula==ROUND(SUM(A5:AE5)/MAX($A$5:$AE$15)/MAX($AF$5:$AF$15),2)

 

[Fluff]

(SL Condition not included in the formula)

Does it need to be?

 

[mmr1]

Yes, SL condition is required and it needs to be added in the formula.

 

[Fluff]

Ok, how about

=LET(m,MAX(A5:AD15),b,BYROW(A5:AD15,LAMBDA(br,SUM(IF(br="sl",m,br))/m)),HSTACK(b,ROUND(b/MAX(b),2)))

 

[mmr1]

Many Thanks Fluff for your formula Solution.

 

[Fluff]

You're welcome & thanks for the feedback.