Hi Experts,
I'm looking Round formula according the criteria, like that:
if there is number 7,15 it will be rounded to 7.
if there is 7,16 it will be rounded to 7,5
if there is 7,65 it will be rounded to 7,5,
if there is 7,66 it will be rounded to 8
if there is 8,15 it will be rounded to 8,
if there is 8,16 it will be rounded to 8,5.
and so one, and before it.
attached screenshot and excel file.
Thank You in Advance,
Farhan
In D7, try:
=INT(B7)+LOOKUP(MOD(B7,1),{0,0.16,0.66},{0,0.5,1})
You may have to adjust the formula if you are using commas instead of decimal points.
I have attached the sheet for your reference (formula should automatically adjust).
63falcondude, wow excellent.
Thank you.
You're welcome. Thanks for the rep!
little have a problem.
the amount is not 7.15 this will be like that 7,15 when I using 7,16 formula is showing #value, can you fix it?
Attach the workbook.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
D7 =substitute(int(substitute(b7,",",".")+0)+lookup(mod(substitute(b7,",",".")+0,1),{0,0.16,0.66},{0,0.5,1}),".",",")
perfect 63falcondude .
Here is another way
Enter in B7 and copy down
Formula:
Please Login or Register to view this content.
then enter formula in D7 and copy down
Formula:Please Login or Register to view this content.
v A B C D 5 6 actual Needed 7 if there is number 7,15 it will be rounded to 7.* 7.15 7 8 if there is 7,16 it will be rounded to 7,5* 7.16 7.5 9 if there is 7,65 it will be rounded to 7,5, 7.65 7.5 10 if there is 7,66 it will be rounded to 8* 7.66 8 11 if there is 8,15 it will be rounded to 8,* 8.15 8 12 if there is 8,16 it will be rounded to 8,5. 8.16 8.5 13
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Perhaps try this formula, it works for your examples
=CEILING(A1-0.15,0.5)
Audere est facere
Clever. Since the OP's values are text and use commas instead of decimals, is there a better way to take this into consideration than converting to a value, CEILING calculation, then converting back to text value with commas?Originally Posted by daddylonglegs
That is:
=SUBSTITUTE(CEILING(SUBSTITUTE(B7,",",".")-0.15,0.5),".",",")
Sorry, I missed that, I was assuming that the commas were alternative decimal separators. If text then, yes, that seems like a reasonable approach, although it seems odd to be rounding text values in the first place......
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks