Complex (I think) calculation

Hi,

We have a price tool where we add a % margin to our buy price. Sometimes we need to sell something via an industry framework and if we're not on that framework we have to go through a provider that is. These providers charge us for it, usually a %. We send them a particular sheet that they copy the rows from into their own tool and add the %.

So what I need to be able to calculate is an appropriate reduction on our direct sale price so that when the framework provider adds their % back on, it equals the exact same value as our direct sale price, AND I need this all rounded to 2 decimal places. Because the customer seems our direct sale price and the framework provider price.

E.g.:

Unit Buy Price: $99.49
Our Margin: 5%
Framework Provider Charge: 2%
Unit Sale Price: $99.49 + 5% = $104.4645 (which would round to $104.46 with ROUND function)
Unit Framework Price: $104.46 - ??? = $102.41 (the result also needs to be rounded to 2 decimal places)
Framework Provider Quote: $102.41 + 2% = $104.4582 (which would round to $104.46 with ROUND function)

Then, I need the same thing for the total columns. Using the example above but quantity 5:

Unit Buy Price: $99.49 * 5 = $497.45
Our Margin: 5%
Framework Provider Charge: 2%
Unit Sale Price: $99.49 * 5 + 5% = $522.3225 (which would round to $522.32 with ROUND function)
Unit Framework Price: $522.32 - ??? = $512.08 (the result also needs to be rounded to 2 decimal places)
Framework Provider Quote: $512.08 + 2% = $522.3216 (which would round to $522.32 with ROUND function)

I hope that makes sense!

Thanks

I am using VBA to actually write the calculated values into the other sheets rather than using inline formulas in those sheets since we need to break out these other sheets and send to a customer/framework provide, so the main sheet will not be included so the formula references would be broken.

Thanks for the quick response. Will have a look a bit later

There is one minor thing, and I'm not sure how it can be solved.

When you multiple row 1 by 5 you don't get the same result as row 2 which is already x5. 795.9 vs 795.92.

H2 formula is E2*5 and I2 formula is G2*5
H3 formula is E3/5

So I get it's a rounding issue on unit price vs total price.

Attachment 865031

You also have an invalid attachment we can not access to comment upon. but which answer do you want? you need to probably need to round one of the answers by encasing the formula in a round function