May 23 2022 08:42 AM
I am trying to make a current matrix of data. The matrix is currently set up such that the combination of % to quota (rows) and volume ranges (columns), results in a payout amount. I'm trying to find a way to unpivot (and / or possibly change the volume range format) this data to make it easier to ultimately use a SUMIFS or similar formula that allows me to input any volume and quota numbers that I want and the formula tells me what the bonus payout is based on the matrix inputs. I've attached the current structure of my matrix as well as an example of the three column output I'm looking for.
May 23 2022 09:01 AM
@egspen2 Perhaps PowerQuery? See attached.
May 23 2022 09:45 AM
May 23 2022 09:55 AM
@egspen2 This is almost identical to another recent post:
https://techcommunity.microsoft.com/t5/excel/unpivot-monthly-data-with-a-formula/td-p/3411637
you can get a few different options/techniques from that thread.
Here is my cell formula based answer from there modified slightly to your matrix (after I defined it as a table called "Data"):
=LET(rc,ROWS(Data),cc,COLUMNS(Data)-1,ungroup,MAKEARRAY(rc*cc,3,LAMBDA(r,c,CHOOSE(c,INDEX(Data,QUOTIENT(r-1,cc)+1,1),INDEX(Data[#Headers],MOD(r-1,cc)+2),INDEX(Data,QUOTIENT(r-1,cc)+1,MOD(r-1,cc)+2)))),ungroup)
May 23 2022 10:58 AM
@egspen2 If you are unfamiliar with PowerQuery, the link below would be a good place to start learning. In particular, chapter 13 deals with unpivoting data.