round up

%3CLINGO-SUB%20id%3D%22lingo-sub-2277974%22%20slang%3D%22en-US%22%3Eround%20up%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2277974%22%20slang%3D%22en-US%22%3E%3CP%3Ei%20have%20table%20were%20the%20values%20in%20Colum%20b%2Cthe%20first%2020%20increase%20by%205000%20and%20the%26nbsp%3B%2010%20000%20and%20then%2050%20000%3C%2FP%3E%3CP%3Eif%20i%20hava%20value%20of%20116000%26nbsp%3B%20then%20the%26nbsp%3B%20price%26nbsp%3B%20in%20Colum%20of%20120%20000%20must%20be%20use%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehow%20do%20i%20write%20formula%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22176%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22111%22%3E240%20000%2C00%3C%2FTD%3E%3CTD%20width%3D%2265%22%3E7%20600%2C00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E245%20000%2C00%3C%2FTD%3E%3CTD%3E7%20600%2C00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E250%20000%2C00%3C%2FTD%3E%3CTD%3E7%20600%2C00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E250%20000%2C01%3C%2FTD%3E%3CTD%3E8%20400%2C00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E260%20000%2C00%3C%2FTD%3E%3CTD%3E8%20400%2C00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E270%20000%2C00%3C%2FTD%3E%3CTD%3E8%20400%2C00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E280%20000%2C00%3C%2FTD%3E%3CTD%3E8%20400%2C00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E290%20000%2C00%3C%2FTD%3E%3CTD%3E8%20400%2C00%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2277974%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2278229%22%20slang%3D%22en-US%22%3ERe%3A%20round%20up%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2278229%22%20slang%3D%22en-US%22%3EIt's%20not%20altogether%20clear%20what%20you're%20asking%20for.%20So%20let%20me%20just%20refer%20you%20to%20the%20ROUND%20and%20ROUNDUP%20functions%2C%20either%20one%20of%20which%20will%20be%20able%20to%20do%20what%20you're%20asking%20for.%20If%20you%20can't%20make%20them%20work%2C%20please%20come%20back%20with%20a%20clearer%20explanation.%20It's%20very%20clear%20in%20your%20mind%2C%20so%20I'm%20sorry%20it's%20not%20coming%20through%20clearly%20to%20me.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2842289%22%20slang%3D%22en-US%22%3ERe%3A%20round%20up%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2842289%22%20slang%3D%22en-US%22%3EHi%20Mathetes%2C%20Hannes%20is%20looking%20for%20a%20formula%20that%20would%20use%20the%20larger%20value%20if%20the%20value%20falls%20between%20two%20values%20to%20return%20the%20higher%20cost.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2842426%22%20slang%3D%22en-US%22%3ERe%3A%20round%20up%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2842426%22%20slang%3D%22en-US%22%3EIt%20was%20in%20April%2C%20half%20a%20year%20ago%2C%20that%20the%20question%20was%20asked.%20I'm%20going%20to%20assume%20it%20got%20answered%20one%20way%20or%20another.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2844705%22%20slang%3D%22en-US%22%3ERe%3A%20round%20up%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2844705%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3BI%20was%20looking%20for%20the%20same%20scenario%20and%20that%20is%20how%20I%20got%20to%20this%20discussion.%26nbsp%3BIt%20would%20be%20nice%20to%20see%20the%20answer%20to%20this%20discussion%20for%20future%20reference.%26nbsp%3B%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3Emaybe%20you%20can%20help%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2845092%22%20slang%3D%22en-US%22%3ERe%3A%20round%20up%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2845092%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1030439%22%20target%3D%22_blank%22%3E%40hannesvdhitcoza%3C%2FA%3E%26nbsp%3BHi.%20Depending%20on%20Excel%20version%2C%20and%20needs%2C%20there%20are%20different%20possibilities.%3C%2FP%3E%3CP%3EIf%20XLOOKUP%20is%20not%20available%3B%3C%2FP%3E%3CP%3EB4%26nbsp%3B%3DVLOOKUP(B2%3BA10%3AM341%3B3%3B1)%3C%2FP%3E%3CP%3Evertically%2C%20lookup%20b2%20in%20range%20%3CSTRONG%3Ea%3C%2FSTRONG%3E10%3Am341%20and%20return%20that%20ranges%20column%20no%203.%3C%2FP%3E%3CP%3ELast%20parameter%201%20means%20approximately%20match%20instead%20of%20exact%3B%20in%20the%20ranges%20actual%20order%2C%20lookup%20the%20first%20higher%20value%2C%20step%20back%20one%20'and%20use%20that%20row.%3C%2FP%3E%3CP%3ELooking%20up%20in%20the%20helper%20column%20results%20in%20the%20next%2Fhigher%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20XLOOKUP%20is%20available%2C%20use%20it!!%20The%20formula%20becomes%20easier%3B%3C%2FP%3E%3CP%3EB5%20%3D%3CSTRONG%3EXLOOKUP(B2%3BB10%3AB341%3BC10%3AC341%3B%3B1)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20version%20also%20supports%20dynamic%20arrays%20(like%20Excel%20365)%20it%20is%20possible%20to%20get%20the%20whole%20data%20row%20in%20return%3B%3C%2FP%3E%3CP%3EB6%26nbsp%3B%3DXLOOKUP(B2%3BB10%3AB341%3B%3CSTRONG%3EB10%3AM341%3C%2FSTRONG%3E%3B%3B1)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22bosinander_1-1634196749528.png%22%20style%3D%22width%3A%20670px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F317289i476823E619CA7C09%2Fimage-dimensions%2F670x576%3Fv%3Dv2%22%20width%3D%22670%22%20height%3D%22576%22%20role%3D%22button%22%20title%3D%22bosinander_1-1634196749528.png%22%20alt%3D%22bosinander_1-1634196749528.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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,007 600,00
245 000,007 600,00
250 000,007 600,00
250 000,018 400,00
260 000,008 400,00
270 000,008 400,00
280 000,008 400,00
290 000,008 400,00
6 Replies
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.
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.
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.

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

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

bosinander_1-1634196749528.png

 

 

 

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