Unpivot matrix

Question

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.

riny_van_eekelen · Answer

egspen2&nbsp;Perhaps PowerQuery? See attached.

egspen2 · Answer

Thanks - for my own educational purposes, could you layout the steps that you went through to get to this result?

mtarler · Answer

egspen2&nbsp; This is almost identical to another recent post:https://techcommunity.microsoft.com/t5/excel/unpivot-monthly-data-with-a-formula/td-p/3411637you can get a few different options/techniques from that thread.&nbsp;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)&nbsp;

riny_van_eekelen · Answer

egspen2&nbsp;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.https://exceloffthegrid.com/power-query-introduction/&nbsp;

Forum Discussion

Unpivot matrix

4 Replies

Resources