CODEWITHSUNDEEP
excel
Amatya
Amatya

Reputation: 1243

Excel replace numbers with custom unequal ranges

Suppose I have the following data

  Name    Output
   A        0.1
   B         7
   C         0.4
   D        0.9
   E        1.1
   F        12
   G        22

I would like replace the output variable by custom ranges:

  Name    Output   Output_2
   A        0.1       0m-0.3m
   B        7         6y-10y
   C        0.4       0.4m-0.6m
   D        0.9       0.7m-1y
   E        1.1        1y-5y
   F        12        11y-20y
   G        22        21y-40y

Right now, I am doing this (a long list of nested IFs)

=IF([@Tenor]<= 0.25, "0m-3m", IF([@Tenor]<=0.5, "4m-6m", IF([@Tenor] <= 1, "7m-1y",IF([@Tenor]<=5,"2y-5y",IF([@Tenor]<=10,"6y-10y",IF([@Tenor]<20,"11y-20y",IF([@Tenor]<40,"20y-40y")))))))

and it works but I am concerned that as the number of ranges increases, this will be painful to write. I was hoping I could write down a range somewhere and ask excel to look it up and do some case type thing.

Upvotes: 0

Views: 246

Answers (1)

ttaaoossuu
ttaaoossuu

Reputation: 7894

Let's assume your first three columns are A, B and C (like in the second code block you posted). Add the following data into columns E and F (this will be your mapping data):

Output   Output2
0        0m-3m
0.25     4m-6m
0.5      7m-1Y
1        2y-5y
5        6y-10y
10       11y-20y
20       20y-40y
40       20y-40y

Then write the following formula into C2 cell and drag it down:

=INDEX($F:$F,MATCH(B2,$E:$E,1))

UPDATE: you can do this even simpler with approximate VLOOKUP:

=VLOOKUP(B2,$E:$F,2,1)

Upvotes: 2

Related Questions