Unpivot matrix

Occasional Contributor

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.

 

 

4 Replies

@egspen2 Perhaps PowerQuery? See attached.

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

@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)

 

@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.

https://exceloffthegrid.com/power-query-introduction/