Forum Discussion
Converting Periodic Data to Quarterly Data
deniztopcu So if you want a more general solution using lookups for each you can do this:
=LET(Rvals,$A$179:$A$214,Cvals,$C$1:$U$1, luTable,$AA$179:$AT$214,luRows,$X$179:$X$214, luCols,$AA$109:$AT$109, factor,$C$2:$U$2*$A$5, MAKEARRAY(ROWS(Rvals),COLUMNS(Cvals),LAMBDA(r,c,LET( rr,FILTER(luTable,INDEX(Rvals,r)=luRows),cc,XMATCH(INDEX(Cvals,c),luCols), (INDEX(rr,cc)-IF(RIGHT(INDEX(Cvals,c),1)="3",0,INDEX(rr,cc+1)))/INDEX(factor,c)))) )basically the first 3 lines define each of the areas, then a MAKEARRAY
then line 5 finds the row of data and which column
then line 6 does the calculation
if that isn't what you wanted and you just want to do 1 row at a time using the prior formula just pass it the corresponding row:
=IFERROR(($AA$179:$AS$179-IF(RIGHT(C$1:U$1,1)="3",0,$AB$179:$AT$179))/C$2:U$2/$A$5,"")or if you need to do a lookup but only doing 1 row at time then:
=LET(rr, FILTER($AA$179:$AT$214,A179=$X$179:$X$214),IFERROR((DROP(rr,,-1)-IF(RIGHT(C$1:U$1,1)="3",0,DROP(rr,,1)))/C$2:U$2/$A$5,""))
I really like your approach. Thank you.
How can we get the numerical data from the relevant line in one go by searching with "product code"?
itemcode
3B
3C
3CA
3CAA
3CAB
3CAC
.
.
Quarterly Data:
1- array formula to do the entire row at once.
2- Using an array formula for the entire block. ---> OK.
deniztopcu So if you want a more general solution using lookups for each you can do this:
=LET(Rvals,$A$179:$A$214,Cvals,$C$1:$U$1,
luTable,$AA$179:$AT$214,luRows,$X$179:$X$214, luCols,$AA$109:$AT$109,
factor,$C$2:$U$2*$A$5,
MAKEARRAY(ROWS(Rvals),COLUMNS(Cvals),LAMBDA(r,c,LET(
rr,FILTER(luTable,INDEX(Rvals,r)=luRows),cc,XMATCH(INDEX(Cvals,c),luCols),
(INDEX(rr,cc)-IF(RIGHT(INDEX(Cvals,c),1)="3",0,INDEX(rr,cc+1)))/INDEX(factor,c))))
)basically the first 3 lines define each of the areas, then a MAKEARRAY
then line 5 finds the row of data and which column
then line 6 does the calculation
if that isn't what you wanted and you just want to do 1 row at a time using the prior formula just pass it the corresponding row:
=IFERROR(($AA$179:$AS$179-IF(RIGHT(C$1:U$1,1)="3",0,$AB$179:$AT$179))/C$2:U$2/$A$5,"")or if you need to do a lookup but only doing 1 row at time then:
=LET(rr, FILTER($AA$179:$AT$214,A179=$X$179:$X$214),IFERROR((DROP(rr,,-1)-IF(RIGHT(C$1:U$1,1)="3",0,DROP(rr,,1)))/C$2:U$2/$A$5,""))
- hi m_tarler
Thank you for your efforts.
You are great
When considering memory consumption, which form produces less memory?- I don't know if I'm qualified to answer that but I believe a single formula would have a tiny bit smaller memory footprint. In terms of processing speed that would be a whole other consideration and I know sometimes the LAMBDA helper functions were slower and there are some here that have successfully used Thunks (basically defining a 'skeleton' LAMBDA function as a variable) and then only at the end apply the values for the Thunks to act on with great improvements in processore speeds but hopefully your sheet isn't in the realm where this sort of exercise is necessary.