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,""))
deniztopcu In this case it appears the lookup column and row is the same as the target table column and row (i.e. each row of output table targets the same corresponding row in the lookup table and each column is looking at the corresponding column in the target) so the whole vlookup isn't even needed. and as for the subtraction you only need to have the IF enclose the difference instead of repeating the first value 2x. Here is my solution:
=IFERROR(($AA$179:$AS$214-IF(RIGHT(C$1:U$1,1)="3",0,$AB$179:$AT$214))/C$2:U$2/$A$5,"")
so I just refer to the range of values you want AA179:AS214 and subtract 0 if "3" or the same grid shifted to the right by 1 if not "3"
If however the lookups are not laid out the same between in and out and lookups are needed then you may need LAMBDA functions like BYROW, BYCOL, or MAP
- hi m_tarler
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?
