• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Is there a way to invert formula that has multiple rounding in it?

Xen

Xen

Member
Hello,
I'm trying to reverse engineer piece of software we have here, so we can make our life easier for everyone, and I'm stuck at this place:

I found out that final result is calculated with formula: =round(P+round(P/C;2)*B ;2)= A
C and B are known naturally, and I found a way to calculate A using other premises we have.
P is that variable that should be entered in program, so we get final result (and what we want is to know what we should enter so we get exact A)

So, I have A B C and P, and A B C are known and the only thing we don't know is P. And every time I try to express P via known variables, I fail at the point where it rounds P divided by C. Removing rounding and expressing P without it gives somewhat close result, but it is always a little bit off, and precision is very important here. Right now, since I know what is desired result, I get approximate P, compare A that it gives to exact A that I found using other variables (those have no use of P, so no help there) and add manual corrections to straight formula until I get exact result.

I've uploaded sample file where all the variables known. If anyone knows what am I missing, please let me know.
 

Attachments

Hi Xen,

In your worked out example if you put a round function to 1 digit you will get 242.4

Can you try this in all the values.

Regards.,
 
Hello SM,
P can be virtually any number starting from zero and have 2 decimal places (in practice, P is <=2000,00, but it can really be anything), so rounding to 1 decimal won't work.

Other facts:
C = 1 to 180
A > or = P (A=P in case B=0, A=P=0 in case P=0)
B < C
P and/or B can be 0

Sometimes we know P = 0, so there is no problem, sometimes B = 0, so rounding P/C is no problem as it is not needed, and my current formula works just fine, in some rare cases, P divided by C equals number with 2 or less decimal places, then my approximate formula works fine as well. In all the other cases (which is majority), I'm stuck with adding/subtracting up to 0,1 (under normal conditions, virtually difference can be higher) to get desired or closest possible result to real A.

By the way, it is worth mentioning that because of round(P/C;2) getting exact A is sometimes impossible, so we have to use closes value, one that gives smaller difference.

I've added more sample data to file.

To make it more clear: P is bonus to salary here, C is total hours worked, and B is hours worked under non-normal conditions (night shifts, holidays, weekends, etc.). Software we have here somehow thinks that if a person worked some time under non-normal conditions, person have to get additional bonus according to percentage of hours worked under those conditions, and it calculates it i weird way (by dividing bonus by total hours, rounding this number and only then multiplying by non-normal hours).

Actual A is total bonus person gets, and is calculated easily by subtracting what belongs to him normally from what we'd like to pay. Problem is, thing that has to be entered into software is P, and what we know is A, and A is using that P in a weird way.
 

Attachments

Last edited:
Hi ,

I may be wrong , but inverses are relevant only as far as mathematical operations are concerned ; there is nothing like the inverse of rounding to 2 decimal places.

Secondly , even inverses will not yield the same results as far as floating point arithmetic is concerned , which is why a set of simple operations such as 1 divided by 3 , multiplied by 3 may or may not yield 1.

Lastly , even if you can get the same result in one case , you may or may not get similar results in other cases.

You would do better to put in a tolerance figure , and where ever the result is within the tolerance figure take the result as correct , rather than try for such accurate matches.

Narayan
 
Hello, Narayan
Thank you for your opinion, it is exactly what I thought, I was just thinking that I might be wrong and missing something (you know, sometimes when you spend too much time on one problem, you might miss obvious solution).


I guess since under normal conditions error in my formula is between +/- 0,00 and 0,10 and desired result is known, I'll do simple macro that evaluates formula 10 times with corrections added and picks exact/closest possible number.
 
Hi Xen,

Following the point raised by Narayan Sir & you to include a tolerance, I tried Goal Seak for your problem by setting the formula for A as you have given in your sample file, with the values of A,B &C, Goal seek gave an answer of P as 242.4021, which in your example is equal to the figure you gave to the first digit of decimal.

I think you should also try Goal Seek for few of the cases and try to see what limit of tolerance it is giving in the values.

Regards,
 
You must log in or register to reply here.
Back
Top