Calculate Base Value for Sliding Scale

I have a scenario where there is a sliding scale. If this was a commission I could figure it out based on the initial value using an approximate VLOOKUP. This specific situation is a fee. Say $20 order gets a $4 fee but a $70 gets $9.

Setting up a spreadsheet to take a base and give me the final amount is easy. How can I take a final price and calculate the base price. So in the $70 example the total would be $79, how can I enter $79 and get it to tell me it's a $9 fee and $70 base?

Happy to attach a spreadsheet with some example values if that helps.

Okay should be added now. There will be scenarios where an end price isn't going to be possible due to the fee jumps. In the attached example at an $82 price point the base price would have to be $73.

Originally Posted by alansidman

An alternative with Xlookup since you are running 365

Excel 2016 (Windows) 64 bit

	A	B	C	D	E	F
1					Base Price	Fee
2	Final Price	Fee	Base		$ -
3	=C3+B3	=XLOOKUP(C3,E2:E9,F2:F9,"not found",-1)	$ 65.00		$ 20.00	$ 1.00
4					$ 30.00	$ 2.00
5					$ 40.00	$ 3.00
6					$ 50.00	$ 4.00
7					$ 60.00	$ 5.00
8					$ 70.00	$ 9.00
9					$ 80.00	$ 12.00

Sheet: Sheet1

Close but this is the reverse of what I was looking for. If I know the final price I want to calculate the base price. Your XLOOKUP the variable/input is the base price.

Originally Posted by MrShorty

I don't see why an approximate match VLOOKUP() won't work here.

1) Insert a column between E and F and enter the "final price" that corresponds to each fee jump. (=E2+G2 copied down).
2) In B3, enter the lookup formula =VLOOKUP(A3,$F$2:$G$9,2,TRUE)
3) In C3, subtract the fee from the final price =A3-B3.

As far as I understand the problem, that seems to work. Is there something I am misunderstanding about the problem?

This is very close! However doesn't accurately account for the sliding scale.

Example: Say I enter a final price of $75, in your example it calculates the fee at $5 and the base at $70, but a $70 base gets a $9 fee so the correct total would be $79 (same is true for the $71-$78 base prices).

It's like it needs an AND criteria where it needs to match on both the base and the base + fee (column F that was added).

I see what you're doing and like it but the math is working out. Example I put $67 in there it tells me the base price is $60 and the fee is $5 which doesn't add up to $67. If I add a helper cell that subtracts A3-B3 to get the true base, it works except in the ranges where certain numbers are not possible. Example: $71 as the final, it will give me a $9 fee and $70 base, which is clearly off. The correct math in that would be $66 base and $5 fee. Also some prices like $76 will never be possible.

It almost works. Problem is on the changes. Example: A final price of $75 isn't possible, but it tells me that $75 would be a $5 fee and $70 base, but that the base of $70, then it's a $9 fee.

You're correct $75 is an impossible price. I can do this math all day. My marketing colleagues are struggling with this. They keep picking prices, and I keep having to explain how the math works and I'm getting tired of having to do this for them. Sadly that means I have to simplify and be full proof.

If I am following correctly, I'm just creating a new table with every possible integer. If that's the case I could just take those base numbers do the math (in another hidden sheet) and then just have B3 and C3 vlookup from the table with every integer.

Your approach makes a lot of sense. Since I am dealing with a somewhat limited scope I don't mind a helper table of integers. I will likely fuss with it later and add a condition that if the value is clearly at the higher scale to just follow that. However as the scales do change and I'm making this for myself and another colleague at a different company I opted for the most dynamic solution.

Using your approach (and thank you for the idea), I added another table in sheet2 (which will be hidden before sending around). I did some fussing with my formulas in B3 and C3 to ensure it could handle float numbers and not just integers.

Ran the integer list up to 250 as we rarely go above it and frankly marketing doesn't usually touch those prices.