Hello:
How do I round a number to the next higher value of a set multiple and the previous lower value of that same set multiple? For example, I have a number 42 and I want to use a multiple of 5. So I want Excel somehow to give me 45 (higher rounding of the multiple) in one cell, and 40 (lower rounding of the multiple) on another cell. Similarly, with say 28 and multiple of 5, I want Excel to give me 30 on one cell and 25 on another cell. And so on.
The reason is I want to then take the higher and lower rounded numbers and lookup corresponding numbers from a chart and do a linear interpolation, which is easy to do if I have the higher and the lower rounding. The chart has values listed on multiples of 5, meaning it lists values for 5, 10, 15, 20 and so on and has corresponding values on the next column. But I need to figure out the corresponding values to numbers in between like 37, 46 etc.
The MROUND function gives only the "nearest" multiple, and does not seem applicable to find both the higher value and the lower value.
If you need any further clarification, please let me know.
Any help will be greatly appreciated.
Thank you.
Bookmarks