0

I have the following Excel spreadsheet:

         A           B         C                    D                
1                  Budget    Actual          Sort by Variancy (descending)
2     Product A     500       250                Product F
3     Product B     900       800                Product D
4     Product C     300       450                Product C
5     Product D     400       600                Product B
6     Product E     700       300                Product A
7     Product F     150       900                Product E

As you can see in Column A I have listed different products and in Column B I have their budget value and in Column C the actual value.

Now, I want to list those products based on their budget-actual-variancy in Column D in a descending order (starting from the highest positive variancy).

The only formula which comes in my mind is =LARGE(B2:B7,1) but it only sorts the products by the budget values (Column B) or actual values (Column C). Not by the difference between the two values.

Do you know any formula which I can use to sort the products in Column D based on their variancy?

Please note:
I know I could add a helper column in which I calculate the differences between Column B and Column C and then go with the LARGE function on this helper column but I am looking for a solution without such a helper column.

Michi
  • 4,663
  • 6
  • 33
  • 83
  • 1
    As long as you don't have any duplicate variances then `=INDEX($A$2:$A$7,MATCH(AGGREGATE(14,3,$C$2:$C$7-$B$2:$B$7,ROW(1:1)),INDEX($C$2:$C$7-$B$2:$B$7,),0))` in `D2` will work – JvdV Oct 19 '19 at 08:57
  • Perfect. Exactly what I needed. Do you also have an alternative if the values are not unique? – Michi Oct 19 '19 at 09:21

2 Answers2

1

If one has SORTBY()(Currently only available with Office 365 insiders) then put this in D2 and it will spill automatically:

=SORTBY(A2:A7,B2:B7-C2:C7,1)

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
0

You can use the following (even when there are duplicate variances):

enter image description here

Formula in D2:

=INDEX($A$1:$A$7,LARGE(INDEX(($C$2:$C$7-$B$2:$B$7=AGGREGATE(14,3,$C$2:$C$7-$B$2:$B$7,ROW(1:1)))*($A$2:$A$7<>D1)*ROW($A$2:$A$7),),1))

Drag down.

In case of duplicate variances it will grab the last value in column A that represents that variancy and has not been featured in column D as yet.

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • Thanks a a lot. Any ideas for this as well: https://stackoverflow.com/questions/58462194/vlookup-with-multiple-criterias-in-different-columns?noredirect=1#comment103260575_58462194 – Michi Oct 19 '19 at 09:55