|
Switching Column Order in XCelsius
Question: We are receiving numbers in both percentiles against the current year and in annual values. This results in our rows returning with the annual values from current year to previous years but we want our chart to appear with the previous years first. How can we turn these columns around easily in Xcelsius?
Answer: The Index and Match functions are very versatile in Excel. By selecting just the item label rather than the row, you use Match to find the row number of the item and then use index to find the value associated with the row.
Here is how the spreadsheet is set up.
Use column L for your label values in your selector and input the chosen label into O5.
Use the Match formula as follows in N5:
=Match(O5:L10:L14,0)
Use the Index formula to reveal the values for the matching row:
=INDEX($P$10:$R$14,$N$5,3)
Note: I like to use the whole array rather than a single column, depending upon the performance hit, for easier copy/pasting. |
|