# round up

Occasional Visitor

# 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

# Re: round up

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.

# Re: round up

Hi Mathetes, Hannes is looking for a formula that would use the larger value if the value falls between two values to return the higher cost.

# Re: round up

It was in April, half a year ago, that the question was asked. I'm going to assume it got answered one way or another.

# Re: round up

@mathetes I was looking for the same scenario and that is how I got to this discussion. It would be nice to see the answer to this discussion for future reference.
@Hans Vogelaarmaybe you can help?

# Re: round up

@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)

# Re: round up

@hannesvdhitcoza NB - some values are changed to visualize matching the correct row. Make sure to use your correct data.