Spreadsheet: 782452d1654064444-formula-to-calculate-discount-for-set-margin-discount-formula-dataset-example-ans
Sheet: Sheet1

 | DATA SET
 | Part | Name | Metric | Qty | Price | Discount Amount | Discount % | Discounted Price | Cost % | Cost | Margin | Margin % |  | Hi All,
 | PART-00001 | NAME-00001 | Each | 2 | 60000 |  |  |  |  | 30000 | 30000 | 0.5 |  | Just need a bit of help reverse engineering a Margin % formula.
 | [NAME] | NAME-00002 | Each | 2 | 60000 |  | 0 |  |  | 30000 |  |  |  | So I have Price, Discount %, Discounted Price, Cost %, Cost, Margin and Margin %.
 | PART-00003 | NAME-00003 | Each | 1 | 140000 |  | 0 |  |  | 70000 |  |  |  | Currently Price and Cost is populated, Discounted Price, Margin and Margin % are formulated and Discount % is a value only that I can type in.
 | PART-00004 | NAME-00004 | Each | 1 | 425000 |  | 0 |  |  | 250000 |  |  |  | What I usually do is to achieve a specific Margin %, I use the Goal Seek function for that specific line.
 | PART-00005 | [NAME] | Each | 1 | 11000 |  | 0 |  |  | 6000 |  |  |  | When I have a few lines this is fine to do, however sometimes I have almost 100 lines and doing them one by one is time consuming using [NAME].
 | PART-00006 | NAME-00006 | Each | 1 | 14000 |  | 0 |  |  | 7000 |  |  |  | I am trying to work out what formula would go into the Discount % cell, so that if I set Margin % to be say 30%, the Discount % would auto calculate what it needs to be based of my input.
 | PART-00007 | NAME-00007 | Each | 1 | 185000 |  | 0 |  |  | 100000
 | PART-00008 | NAME-00008 | Each | 10 | 105000 |  | 0 |  |  | 60000 |  |  |  | For the avoidence of doubt I do not want a Macro. Just a formula.
 | PART-00009 | NAME-00009 | Each | 200 | 70000 |  | 0 |  |  | 35000
 | [NAME] | NAME-00010 | Each | 200 | 530000 |  | 0 |  |  | 265000 |  |  |  | e.g. Currently
 | PART-00011 | NAME-00011 | Each | 1 | 30000 |  | 0 |  |  | 20000 |  |  |  | Price = 10,000 [input]
 | PART-00012 | NAME-00012 | Each | 2 | 120000 |  | 0 |  |  | 80000 |  |  |  | Discount % = 25% = [input]
 |  |  |  |  |  |  |  |  |  |  |  |  |  | Discounted Price = 7,500 = sum(P)-sum(PxD%) [formula]
 |  |  |  |  |  |  |  |  |  |  |  |  |  | Cost = 4,000 = [input]
 |  |  |  |  |  |  |  |  |  |  |  |  |  | Margin = 3,500 = sum(DP-C) [formula]
 | FINAL RESULT |  |  |  |  |  |  |  |  |  |  |  |  | Margin % = 46% = sum(M/DP) [formula]
 | Part | Name | Metric | Qty | Price | Discount Amount | Discount % | Discounted Price | Cost % | Cost | Margin | Margin %
 | PART-00001 | NAME-00001 | Each | 2 | 60000 |  |  |  |  | 30000 |  | 0.3 |  | What I want to get to is, as follows:
 | [NAME] | NAME-00002 | Each | 2 | 60000 |  |  |  |  | 30000 |  | 0.3 |  | Price = 10,000 [input]
 | PART-00003 | NAME-00003 | Each | 1 | 140000 |  |  |  |  | 70000 |  | 0.3 |  | Discount % = 25% = ??? [formula]
 | PART-00004 | NAME-00004 | Each | 1 | 425000 |  |  |  |  | 250000 |  | 0.3 |  | Discounted Price = 7,500 = sum(P)-sum(PxD%) [formula]
 | PART-00005 | [NAME] | Each | 1 | 11000 |  |  |  |  | 6000 |  | 0.3 |  | Cost = 4,000 = [input]
 | PART-00006 | NAME-00006 | Each | 1 | 14000 |  | 0.2855310788679246 |  |  | 7000 |  |  |  | Margin = 3,500 = sum(DP-C) [formula]
 | PART-00007 | NAME-00007 | Each | 1 | 185000 |  | 0.2275796415224088 |  |  | 100000 |  |  |  | Margin % = 46% [input]
 | PART-00008 | NAME-00008 | Each | 10 | 105000 |  | 0.1836435277690377 |  |  | 60000
 | PART-00009 | NAME-00009 | Each | 200 | 70000 |  | 0.2855089447025496 |  |  | 35000 |  |  |  | Thanks in advance for help or confirmation that it cant be done via formula.
 | [NAME] | NAME-00010 | Each | 200 | 530000 |  | 0.2855114525551815 |  |  | 265000
 | PART-00011 | NAME-00011 | Each | 1 | 30000 |  | 0.04754475000000023 |  |  | 20000
 | PART-00012 | NAME-00012 | Each | 2 | 120000 |  | 0.04754475000000023 |  |  | 80000
 |  |  |  |  |  |  |  |  |  |  |  |  |  | Range M21:M25 filled manually
 |  |  |  |  |  |  |  |  |  |  |  |  |  | H21:and I21 formula given
 |  |  |  |  |  |  |  |  |  |  |  |  |  | H21:I21 formula copied down to H25:I25