# Macro/Program to calculate adjusted sale price

#### Slpygry

##### New Member
There is a website, Selling Price Calculator, that will run a calculation for Sale Price where the sales price includes fees. There have been many questions on this forum regarding a “formula” for this function, and with all the research I’ve done, I think I’ve realized that a simple “formula” isn’t possible; (unless you’re getting the sales price one at a time, then there is an Excel Function). However, I need the calculation on thousands of items at once and I think it needs to be a program or macro that cycles through incrementing sale prices to reach the desired percent margin. Is this the right assumption?

I have already created an Excel Power Query that has a row for each item I’m trying to sell. Each row has (among other things) the % Margin I want, as well as the cost for the item (at this time there is only one cost, (shipping you may be thinking is another cost, but I’m going to charge shipping at actual cost, so it will not affect the % margin)). I was hoping to run a Macro that will “spit out” or calculate and display the sale price for each row, on the row it belongs to.

I’m assuming I’ve done my math correctly. If % Margin = Profit / Cost, then the formula’s I need to use are as follows:

For the sell price of \$250 or less: % margin = ((sell \$) - (cost + (sell \$ * .06))) / (cost + (sell \$ * .06))

For the sell price of \$250.01 or more: % Margin = ((sell \$) – (cost + \$15 + ((sell \$ – 250) * 0.06))) / (cost + ((sell \$ – 250) * 0.06))

I know enough about Macro’s to be dangerous but not effective, so I’m not sure what other information needs to be provided; nor am I positive that a Macro is the solution. Please let me know, if you can help, what you need from me. Thank you for your time, Karri

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Welcome to the Forum!

I can see that @EricW mentioned Solver / macro solutions here: Formula for calculating eBay selling price

But the requirements you've now posted seem very simple ...

If you want to measure margin on cost (that's up to you) then Sell Price = Cost Price * (1+required margin), as shown in the first option below. You could code this into a macro, but why bother?

It's not clear why you have a different formula above \$250: \$15 + 6% of (Sell - \$250) is the same as 6% of Sell.

There are other ways you may want to define your margin. The second option shown is based on a margin expressed as a % of Sell Price, rather than a % of Cost Price.

ABCDEF
1
2Cost priceRequired margin on costSell priceProfitProfit as % Cost
3
4\$1020%\$12\$220%
5\$2512%\$28\$312%
6\$3006%\$318\$186%
7
8
9Cost priceRequired margin on sell priceSell priceProfitProfit as % Sell price
10
11\$1020%\$12.50\$2.5020%
12\$2512%\$28.41\$3.4112%
13\$3006%\$319.15\$19.156%
Sheet1
Cell Formulas
RangeFormula
D4:D6D4=B4*(1+C4)
E4:E6,E11:E13E4=D4-B4
F4:F6F4=E4/B4
D11:D13D11=B11/(1-C11)
F11:F13F11=E11/D11

Thank you for welcoming me to the Forum. I am part of the discussion with @EricW in the thread you attached, towards the bottom of that thread, and he suggested that I pose my question in a separate thread.

Also, I apologize, I made a mistake when posing my initial question. For a sell price that is over \$250.00 the formula should read:
% Margin = ((sell \$) – (cost + \$15 + ((sell \$ – 250) * 0.035))) / (cost + ((sell \$ – 250) * 0.035))

The spreadsheet in the thread you attached does a great job of providing the sell price if my fees are constant (6% for the fee and 8% for the margin), however, it does not work with the added 3.5% fee on the sell price that is over \$250.00.

As an example, if my sell price is \$1040.39, then my fee is: (\$250 * 6%) + ((\$1040.39 - \$250) * 3.5%) = \$42.66

Therefore, the sell price of \$250.01 or more: Sell \$ = (cost + 15 + ((sell \$ - 250) * .035)) / (1 - % margin)

I can use the formula in the attached if the sales price will be less than \$250. However, is there is a formula that solves for the sell price over \$250?

Thanks for clarifying. And sorry, my first post oversimplified. Is this closer to what you're looking for?

ABCDEFGH
1Fees:\$2506.0%
2then3.5%CHECK:
3Cost priceRequired margin on sell priceSell priceFeesMarginMargin as % Sell price
4
5\$5020%\$67.57\$4.05\$13.5120%
6\$10012%\$121.95\$7.32\$14.6312%
7\$2506%\$283.15\$16.16\$16.996%
8\$30010%\$354.05\$18.64\$35.4010%
9\$50015%\$621.17\$27.99\$93.1715%
10
Sheet1
Cell Formulas
RangeFormula
F5:F9F5=D\$2*D5+(D\$1-D\$2)*MIN(D5,C\$1)
G5:G9G5=D5-F5-B5
H5:H9H5=G5/D5
D5:D9D5=IF(B5<=C\$1*(1-D\$1-C5),B5/(1-D\$1-C5),(B5+C\$1*(D\$1-D\$2))/(1-D\$2-C5))

I really wish I was this smart. I feel like you answered this from your elementary book and I struggled and struggled. I've accomplished a lot with Excel, but this formula made me feel "less than". You are a rock star, and I really appreciate your help. Thank you, so, so much.

Replies
27
Views
2K
Replies
2
Views
149
Replies
2
Views
606
Replies
7
Views
301
Replies
3
Views
765

1,219,957
Messages
6,151,146
Members
451,011
Latest member
Pigdog89

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back