Populate verbatim with criteria

[kumara_faith]

Hi,

I have the following data:

Book1
	A	B	C	D	E	F	G	H	I
1	Branch	Average Ticket	4/2/2024	4/3/2024	4/4/2024	4/5/2024	4/6/2024	4/7/2024	4/8/2024
2	Branch1	150	150	204	204	204	204	204	204
3	Branch2	150	150	204	204	204	204	204	204
4	Branch3	150	150	204	204	204	204	204	204
5	Branch4	150	150	204	204	201	204	204	201
6	Branch5	180	180	300	204	204	204	204	204
7	Branch6	150	150	204	204	204	300	204	204
8	Branch7	125	125	204	204	204	204	204	204
9	Branch8	150	150	204	204	204	108	108	204
10	Branch9	150	150	204	204	204	204	204	204
11	Branch10	147	147	204	204	204	201	201	204
12	Branch11	150	150	204	201	204	204	204	204
13	Branch12	235	235	204	204	204	204	300	300
14	Branch13	150	150	204	204	204	204	204	204
15	Branch14	150	150	204	108	204	201	204	204
16	Branch15	125	300	204	204	204	300	204	204
17	Branch16	150	150	204	204	204	204	108	204
18	Branch17	150	150	204	204	204	204	201	204
19	Branch18	150	150	300	204	204	204	204	204
20	Branch19	125	300	204	204	204	204	204	204
21	Branch20	150	150	204	204	204	204	204	204
22	Branch21	150	150	204	204	204	204	204	204
23
24	Date	%
25	4/4/2024	10%
Data (2)

Book1
	A	B	C	D	E	F	G
27	Correct Result
28	Branch 15 exceeded the daily ticket volume by 140% compared to historical average
29	Branch 19 exceeded the daily ticket volume by 140% compared to historical average
Data (2)

Column A is the list of branches. Column B has the historical average daily volume for each branch.Column C to column I has the actual daily tickets. I am trying to build a formula to check the date in cell A25 and then check the actual daily ticket for that date, and if exceeds the historical average in column B by more than a certain percentage, in this case 10% in cell B25, to return a remark that the particular branch exceeded by certain % .

Example of the correct result is in cell A28 and A29. Is this possible with a formula ? Appreciate all the help.

 

[Fluff]

How about

=LET(p,(XLOOKUP(A25,C1:I1,C2:I22)-B2:B22)/B2:B22,f,FILTER(HSTACK(A2:A22,p),p>B25),INDEX(f,,1)&" exceeded the daily ticket volume by " & TEXT(INDEX(f,,2),"0%")&" compared to historical average")

 

[kumara_faith]

Hi Fluff,

Thank you so much and that worked . Is there a way to add the following criteria ?

To also return the same if the volume drops by more than the percentage in cell b25 ( i.e "Branch 15 daily volume dropped by % compared to historical average") and return blank if not match for both criteria.

 

[Fluff]

How about

=LET(p,(XLOOKUP(A25,C1:I1,C2:I22)-B2:B22)/B2:B22,f,FILTER(HSTACK(A2:A22,p),p<0),IF(COUNT(f)=0,"",INDEX(f,,1)&" exceeded the daily ticket volume by " & TEXT(INDEX(f,,2),"0%")&" compared to historical average"))

Change the wording to suit.

 

[kumara_faith]

Hi Fluff,

Thanks Fluff and really appreciate it. Is there a way to use both formula in the same cell i.e combining both formula so that the result will be populated accordingly if the volume was exceeded or reduced ?

 

[Fluff]

Try

=LET(p,(XLOOKUP(A25,C1:I1,C2:I22)-B2:B22)/B2:B22,fh,FILTER(HSTACK(A2:A22,p),p>B25),fl,FILTER(HSTACK(A2:A22,p),p<0),VSTACK(IF(COUNT(fh)=0,"",INDEX(fh,,1)&" exceeded the daily ticket volume by " & TEXT(INDEX(fh,,2),"0%")&" compared to historical average"),IF(COUNT(fl)=0,"",INDEX(fl,,1)&" exceeded the daily ticket volume by " & TEXT(INDEX(fl,,2),"0%")&" compared to historical average")))

 

[kumara_faith]

Thank you Fluff and really appreciate it. That worked, Have a wonderful day ahead .

 

[Fluff]

You're welcome & thanks for the feedback.