Most profitable value based number of columns selection

[Zubairsalam]

Hi team I have joined this forum today and need your help on the last bit of my complex formula, I want to link my formula with the cell B7 which contained data validation, so based on changing the number of columns I need headings on line 8, 9 & 10.

Further if existing formulas need any correction, please advise, because I feel its too long to understand.

 

[Peter Bartholomew]

I assume you do not have Excel 365? If you do, the formula might be

= LET(
    gross,  sales - cost,
    net,    gross - expense,
    values, VSTACK(sales, cost, gross, expense, net),
    order,  SWITCH(view,
      "Scenario by years", CHOOSEROWS(headers, 1),
      "Years by scenario", CHOOSEROWS(headers, 2),
      "Most Profitable",   -net),
    sorted, SORTBY(VSTACK(headers,values), order),
    sorted
  )

 

[Zubairsalam]

Many thanks Peter, may be I have not explained you correctly, I want to link this with cell B7 and for example we have selected 10 then the headings of 10 top NP should appear as descending order.

 

[Peter Bartholomew]

My challenge was replicating what you had with a formula I could understand. Once that is done, selecting the 10 top NP should be easy

= LET(
    gross,  sales - cost,
    net,    gross - expense,
    values, VSTACK(sales, cost, gross, expense, net),
    order,  SWITCH(view,
      "Scenario by years", CHOOSEROWS(headers, 1),
      "Years by scenario", CHOOSEROWS(headers, 2),
      "Most Profitable",   -net),
    sorted, SORTBY(VSTACK(headers, values), order),
    TAKE(sorted, , outputColumns)
  )