MEWC
FMWC
TAC
EEO
TASK
You are organizing the University of Tennessee’s Cornhole Championships! In Levels 1 through 4, you will determine the cost of various items and project the event’s revenue. In Levels 5 through 8, you will score individual tosses (throws of a bean bag), rounds, and entire games. Go get some sliders, watch out for hangers, and good luck!
WALKTHROUGH VIDEO PROVIDED BY ERIC KELLEY
FUNCTIONS USED
- named ranges, dynamic arrays
- data tables, approximate matching
- sum, sumproduct, countif, minifs, abs, iseven, isodd
- int, mod
- unique, sequence
- textsplit, len, substitute
- indirect
- tocol, take
Video is divided in parts so you can easily skip to the part that you are stuck on to find out best solution.
WALKTHROUGH VIDEO PROVIDED BY DIARMUID EARLY
FUNCTIONS USED
dynamic arrays, named ranges, approximate matching, data table, find and replace by format
textspit, xlookup
sum, max, minifs, countifs, int, iseven, if, max, round
len, substitute
take, wraprows, wrapcols, byrow, bycol, vstack, choosecols, groupby
lambda, let, scan
indirect, map, cell
xmatch
Video is divided in parts so you can easily skip to the part that you are stuck on to find out best solution.
WALKTHROUGH VIDEO PROVIDED BY EXCEL WIZARD
Live attempt at solving the case.
WALKTHROUGH VIDEO PROVIDED BY EXCEL WIZARD
Solving the case with single cell formulas.
Level 3 formula with nested reduct =LET(r,REDUCE(I126:J126,SEQUENCE(9),LAMBDA(a,i,LET(t,TEXTSPLIT(@+a,”; “),x,BYROW(XMATCH(pack,t),COUNT)=2,b, REDUCE(@+a,FILTER(pack,x,0),LAMBDA(b,v,SUBSTITUTE(b,v,,1))),HSTACK(b,SUM(Pcost*x,INDEX(a,2)))))),SUM(XLOOKUP(TEXTSPLIT(@r,”; “,,1),Item,Cost),r)*(1-XLOOKUP(G126,Dates,Discount,0,1)))
with Groupby =LET(z,TEXTSPLIT(I126,,”; “),g,GROUPBY(z,z,ROWS,0,0),t,TAKE(g,,1),n,BYROW(IFERROR(VLOOKUP(pack,g,2,0),0),MIN), SUM((DROP(g,,1)-XLOOKUP(t,TOCOL(pack),TOCOL(IFNA(n,pack)),0))*XLOOKUP(t,Item,Cost),n*Pcost)*(1-XLOOKUP(G126,Dates,Discount,0,1)))
Level 6 formula =LET(b,BYROW(WRAPROWS(XLOOKUP(H241:AE241,Abb,Pts),8),LAMBDA(a,ARRAYTOTEXT(BYROW(WRAPCOLS(a,2),SUM)))), m,MAP(b,LAMBDA(c,SUM(TEXTSPLIT(c,”,”)*{1,-1}))),SUM(ABS(m)))
Level 8 formula =LET(b,BYROW(WRAPROWS(CELL(“contents”,INDIRECT(“Board!”&G323:GX323)),8),LAMBDA(a,ARRAYTOTEXT(BYROW(WRAPCOLS(a,2),SUM)))), m,MAP(b,LAMBDA(c,SUM(TEXTSPLIT(c,”,”)*{1,-1}))), pa,SCAN(0,IF(SIGN(m)=1,m),SUM),pb,SCAN(0,IF(SIGN(m)=-1,-m),SUM),XLOOKUP(21,MAP(pa,pb,MAX),pa+pb,,1))
Dim’s method =LET(b,BYROW(WRAPROWS(BYCOL(WRAPCOLS(CELL(“contents”,INDIRECT(“Board!”&G321:GX321)),2)*{1;-1},SUM),4),SUM), pa,SCAN(0,IF(SIGN(b)=1,b),SUM),pb,SCAN(0,IF(SIGN(b)=-1,-b),SUM),XLOOKUP(21,MAP(pa,pb,MAX),pa+pb,,1))
Bonus 5 =SUM(BYROW(G323:GX342,LAMBDA(x,LET(p,WRAPCOLS(CELL(“contents”,INDIRECT(“Board!”&x)),2), b,BYROW(WRAPROWS(BYCOL(p*{1;-1},SUM),4),SUM), pa,SCAN(0,IF(SIGN(b)=1,b),SUM),pb,SCAN(0,IF(SIGN(b)=-1,-b),SUM), SUM(N(BYROW(WRAPROWS(TOCOL(TAKE(p,,XMATCH(21,MAP(pa,pb,MAX),1)*4)),4)=3,AND)))))))