Complex rounding with non-standard intervals in Excel

I need to round a calculated numb开发者_开发知识库er to the closest value in a table. The values in the table are not sequential, and the intervals between the values are not the same. (I actually haven't been able to find a pattern between the values.)

Here are the table values:

1
10
20
40
70
120
180
260
360
610
940
1350
1780
2220
2720
3490
4770
6500
8070
10000

So for instance, if I have a calculated number of 53.36, the formula should return a table value of 40. If the value was above 55, then it would return 70.

Use vlookup with a final parameter of 1 or true (not exact match).

Put your values in a range and vlookup on that range. It will find the nearest value that is not greater than the search value.

Edit: And...by the way, your numbers listed are WARF (weighted average rating factor) numbers used by Moody's in rating bonds. See here.

Edit #2: To get the "rounding up" you're looking for, just have a second column in your search range that has the next number up. E.g.: On the row where 20 is, the second column would show 40; on the row for 40, the second column would show 70. Then, in your formula, hava second vlookup on the same search range, but returning the second column. Once you've got the two numbers (40 and 70 for your example value of 55), you can do the math in your formula to determine which you'll show.

Should be a quick and easy if then loop using VBA. Check the difference between your number and the next 2 numbers in the list. When the difference is less with the first number you know that's the one you want to return.

You might want to think about an Array function (aka CSE Function). If your table is in range A1:A20, and your lookup value is in C1, then use this function:

=IFERROR(VLOOKUP(C1+MIN(ABS(A$1:A$20-C1)),A$1:A$20,1,FALSE),VLOOKUP(C1-MIN(ABS(A$1:A$20-C1)),A$1:A$20,1,FALSE))

and then Control-Shift-Enter (not just Enter). Basically, this finds the minimum absolute difference between C1 and any value in your table. Then it tries adding it and doing a VLOOKUP to see if it's in the table, if that fails, it subtracts it and does VLOOKUP. A little complicated, but might give you ideas.