Trying to finalize consignment calculator with tiered rates

Hello Everyone,

Thank you for taking a look at the issue I am having. Ive spent hours searching online and trying various formulas but cant seem to find anyone that posted the same question.

I am trying to create a google sheets worksheet for a consignment shop. Reason we are using GSheets vs excel, is because he wants to put a QR code on some mailers that lead back to the Google Sheet form which has the consignment rates as well as, a 'Payout Estimator' that customers can use to estimate what they will get paid for an item.

I managed to create the formula using multiple IF statements and was also able to do it using VLOOKUP (both formulas copied from online), but they still don't work exactly as needed.

Two Issues:
1. The main problem is, the owner wants $1 substracted on one of the tiers $10 to $49.99. I can get the total but not minus $1.
2. The flyer says $3 flat fee for items under $10. I could not figure out how to keep the language the same but have the calculator show the $3 fee. The only way I could do it was to change the $3 to 70% which is effectively the same but might confuse customers and also, 70% just sounds more than $3.

See Below:

Sales Price Payout
Under $10 $3 flat fee
$10 to $49.99 85% minus $1
$50 to $99.99 86%
$100 to $499.99 87%
$500 to $999.99 88%
$1000 to $2499.99 89%
$2500 to $4999.99 91%
$5000 to $9999.99 93%
$10000+ 94%


This is the current formula:
=IF(N9<=$B$9,$F$9,IF(N9<=$B$10,$F$10,IF(N9<=$B$11,$F$11,IF(N9<=$B$12,$F$12,IF(N9<=$B$13,$F$13,IF(N9<=$B$14,$F$14,IF(N9<=$B$15,$F$15,IF(N9>=$B$16,$F$16,IF(N9<=$B$17,$F$17,IF(N9>=$A$18,F18))))))))))*N9

Here is the link to the spreadsheet - Please let me know your thoughts on the best approach to this or feel free to edit it. Thank you in advance!!

docs.google.com/spreadsheets/d/1ZKZcqZn0cbrYNerYpcSn8uTcz49EJsgqpFNtkjh0hKo/edit?usp=sharing

Well?
Have you tried the suggested solution?
Do you have any questions?
Do you have any feedback?
Did it work for you?
Are you just going to leave it there?

Thank you tremendously Croweater!! Apologies about the delayed response. We took some badly needed time off for Thanksgiving and then had to play catch up. We have been meaning to Thank you so much for putting this together. The sheet you put together is amazing! We spent about 2 hours trying to figure out how it worked and make a few adjustments and for the life of us, could not fully understand it.

Your suggestion would work perfectly as to hiding the text in the PQR columns. We do have a couple of questions...

In the process of using it, I moved the whole grid a few lines down to add a company logo etc and in result, it ended up screwing up the formula. I managed to get it close to working except for the (6,0) in the formula =VLOOKUP($P9,$A$9:$G$17,6,0). I could not figure out where it referenced. We had to go back to your original.
After we reviewed and discussed the entire thing, turns out, to not confuse people, we need to make 2 small adjustments. I am not sure if excel is even capable of doing it. We need a cell to say something “$3 Flat Fee” but have your formula/number hidden underneath the text.

Here is what I mean in detail:
1) Under PAYOUT, is it possible to have cell F9 say: "$3 Flat Fee" and the Payout calculate a $3 flat fee from $0 to $9.99.
70% won’t work because if the item is $1, our cost would be more than $.30. Reevaluating it, $3 Flat Fee would work for everyone.
2) Is it possible to have this cell F11 say: "85% minus $1" and the Payout calculate accordingly (85% less $1)?

The calculation is accurate, but we are worried someone might not see the -1 to the right and get confused or upset if the payout is different than what they saw or expect.
The only work out we could think of, is to put an image that states these things $3 Flat Fee and 85% minus $1 on top of the cells. To calculate the $3 flat fee, we could put 0% in F9 and put -3 in G9 then cover it with an image. This is at least our nonprofessional approach.

I have reattached the sheet with some comments and red highlights in the area. Really appreciate your help Croweater!!

Is the attached example what you wanted (see new attachment in post #6)?

Hey Croweater! - Yes it works!!! The labels are there and the formula is hidden! We researched online as well and found out how we can lock the columns so that no one can change the formulas. This is so once we send out the link to the google sheets site, someone cant go in there and mess with your formula.

Im not sure if something happened in the formula, but now regardless if there is anything in the Enter Item Price column, the payout is $3. In G9 there was a 3 and we changed it to a 1 which fixed the payout making it $0. But in order to get the $3 flat fee, we had to add -3 in H9. This caused the payout column to have a -$3 regardless if there is anything entered in the 'Enter Item Price' fields.
Essentially, if there is no data in Column O9:14, the total payout shows $-3. So for example if someone only has one item that is worth $10, it will calculate -$3 but the rows below show -$3 as well, so it actually deletes their entire payout when he looks at the total.

We made some progress with the 1.0 in G9 but retracted that by adding the -3 in H9. Any thoughts on getting the Payout to show $0 all the way down and only calculate if an 'Item Price' is entered in the adjacent field?

See attached - Comments added! Appreciate your help on these last few hurdles! Also, do we need the $999,999 in A18 or can it be deleted or hidden. We weren't sure if it was part of the formula.

We can see the light.... Its almost there!! That new code worked by fixing the Payout auto calculating, but now it doesn't calculate the payout correctly for some reason. For example, if we enter 0.01 in the item price, the payout to the customer is $3.00. Should be -$2.99. Reattached with comments in P9:12.
Cant Thank You enough for all your help!

Labyrin_3_Modified.xls

OK. I misunderstood what you meant by a 'flat fee'.

Try this in P9 and drag down.

=IF($O9=0,"",IF($R9>=1,$O9-$R9,$R9*$O9+$S9))

Is that 100% there now?

Originally Posted by HansDouwe

Question: Your formula didn't do any calculations with Quantity yet? Isn't that important?

If you actually bothered to read the thread, you'd see this was already addressed way back in post #3

"I'll leave it to you to sum the payout and multiply it by the quantity (if you want that) and put any headings etc. for the adjustment"