Forum Discussion
round up
i have table were the values in Colum b,the first 20 increase by 5000 and the 10 000 and then 50 000
if i hava value of 116000 then the price in Colum of 120 000 must be use
how do i write formula
| 240 000,00 | 7 600,00 |
| 245 000,00 | 7 600,00 |
| 250 000,00 | 7 600,00 |
| 250 000,01 | 8 400,00 |
| 260 000,00 | 8 400,00 |
| 270 000,00 | 8 400,00 |
| 280 000,00 | 8 400,00 |
| 290 000,00 | 8 400,00 |
6 Replies
hannesvdhitcoza Hi. Depending on Excel version, and needs, there are different possibilities.
If XLOOKUP is not available;
B4 =VLOOKUP(B2;A10:M341;3;1)
vertically, lookup b2 in range a10:m341 and return that ranges column no 3.
Last parameter 1 means approximately match instead of exact; in the ranges actual order, lookup the first higher value, step back one 'and use that row.
Looking up in the helper column results in the next/higher value.
If XLOOKUP is available, use it!! The formula becomes easier;
B5 =XLOOKUP(B2;B10:B341;C10:C341;;1)
If the version also supports dynamic arrays (like Excel 365) it is possible to get the whole data row in return;
B6 =XLOOKUP(B2;B10:B341;B10:M341;;1)
hannesvdhitcoza NB - some values are changed to visualize matching the correct row. Make sure to use your correct data.
- It's not altogether clear what you're asking for. So let me just refer you to the ROUND and ROUNDUP functions, either one of which will be able to do what you're asking for. If you can't make them work, please come back with a clearer explanation. It's very clear in your mind, so I'm sorry it's not coming through clearly to me.
