round values formula excel

[beblak]

Hello,

how to round (using 1 formula) with original values ​​from column A to values ​​equal to those in column B. My not 100% good proposition in column C.

BR
beblak

[mikeava]

How about just =ROUND(A2,2)

[MrShorty]

I do not see the problem. I assume that somewhere along the line, you were seeing C12 return 0.18, but C12 is returning 0.19 when I open your file. Can you explain a little more about your problem? Where/when is it failing? Have you checked calculation to make sure it is automatic?

[beblak]

Using the function (maybe it will not be just a rounding function) I would like to get the results according to column B based on the value in column A, I proposed a rounding function in column C, but this is not a formula that reflects the expected value in 20/20 cases. The row for which there is no value coverage is different from 0 in column D (comparative column). Correctly proposed function will display only zeros in column D. I can extend the spreadsheet if necessary with further data. I see a relationship, but I can't describe it with a formula.

[MrShorty]

In your sample file, column D does not contain a formula. If I replace the values in column D with =D2-C2 (copied down), all of column D returns 0 -- including C12, which your sample file claims would return -0.01. Do you have more examples where it fails? Have you checked for things (like calculation setting) that would be specific to your installation of Excel?

[beblak]

Exactly, the value in C12 returns -0.01 which means there is a mismatch and the formula I propose is not correct. I suspect that this may not be possible with other round-off formulas, and this requires a more complex function.

I attach a file with more sample data.

BR

[MrShorty]

Your two sample files do not appear to be consistent, so I wonder if there is a typo in one of them.

In your original sample file, you appear to be using standard arithmetic rounding where you round half up. Your example of 0.185 in your original sample file is shown rounding up to 0.19. The formula you proposed in the OP round 0.185 up to 0.19.

In this new sample file, you appear to be using banker's rounding where you round half to even. You are now showing 0.015 rounding up to 0.02 and 0.025 rounding down 0.02. It will help us help you if you are clear and consistent in describing your problem.

Excel's built in rounding algorithm is the standard arithmetic "round half up" algorithm. However, VBA's built in rounding algorithm is banker's rounding. I usually think that the easiest way to implement banker's rounding in Excel is to use a VBA user-defined function. See example and discussion here (including a non-VBA solution): https://www.excelforum.com/excel-for...-rounding.html Of course, if you put something like "banker's rounding excel" into your favorite search engine, you should find more tutorials and discussions.

Are we getting closer to understanding the question?

[beblak]

Yes, we are getting closer to understanding the question. Thank you MrShorty for your willingness to help.

You are now showing 0.015 rounding up to 0.02 and 0.025 rounding down 0.02

Non-standard rounding occurs for numbers that end in 5 and the digit preceding it is even. (x.x05, x.x25, x.x45, x.x85). This is the rule of that strange rounding that I noticed. In all other cases, rounding is standard arithmetic "round half up" algorithm.

Can you do this with if function? IF numbers end in 5 AND the digit preceding 5 is even THEN round down, ELSE round up

[oeldere]

Please show the expected results manualy in your file.

[Phuocam]

Try this

=ROUND(A2,2)-OR(MOD(A2*1000,100)={5,25,45,65,85})*0.01

[MrShorty]

An IF() function might be useful. It's hard to say, since I'm not sure of the actual rounding rule we are using. We can keep guessing at formulas if you want but they're just guesses until we get a good description of the rounding algorithm.

You say it's not regular rounding and you didn't seem to like banker's rounding. Now you have some vague idea about even and odd-- but not banker's rounding. Let's pin down the desired algorithm, then we can work out s formula.

[beblak]

Please show the expected results manualy in your file.

hi, attached the file

BR

[MrShorty]

It still looks like banker's rounding to me, and, as I noted in the other thread, I think a VBA UDF is the easiest way to implement banker's rounding in Excel. Are you allowed to use a VBA UDF for this, or do we need to come up with a native Excel way to implement banker's rounding?

[beblak]

It still looks like banker's rounding to me, and, as I noted in the other thread, I think a VBA UDF is the easiest way to implement banker's rounding in Excel. Are you allowed to use a VBA UDF for this, or do we need to come up with a native Excel way to implement banker's rounding?

I prefer the option without using VBA

BR

[beblak]

Try this

=ROUND(A2,2)-OR(MOD(A2*1000,100)={5,25,45,65,85})*0.01

this formula does not work unfortunately

[MrShorty]

Adapting the formula proposed here: https://www.excelarticles.com/excelb..._astm_e29.html
=IF(MOD(100*A2,1)=0.5,MROUND(100*A2,2),ROUND(100*A2,0))/100 or some equivalent variation (needs to account for both floating point errors as well as the banker's rounding) should work.

[Phuocam]

this formula does not work unfortunately

It worked for me.

[beblak]

It worked for me.

actually works, I used a different version of "{5 \ 25 \ 45 \ 65 \ 85}" that's it!

THANK YOU