Friday, April 10, 2015

yogi_Given An Array Of Costs And An Array Of Net Profits Compute The Selling Prices To Include A Specified Tax To Be Levied On Sales

                                           Google Spreadsheet   Post  #1941
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI    Apr-10, 2015
question by Alex Staples:!category-topic/docs/spreadsheets/hlroyrsqFX4
How to formulate a selling price based on a desired specific profit range
My sample doc attached:

I have been trying to work on this and can't quite figure out how to get this to work right

If we were to be only working off of ROW 8  which has a TOTAL COST OF 201.59 (H)  I am trying to see what formula to use to in (J - N) that would tell me what I need to sell my product for to gain the profits shown in  J7 - N7

A contributer gave me the  formula "  =index(H8:H/(1-13%)+iferror(regexextract(J7:N7,"(\d+) Profit"))) " which gives me an accurate answer in column J, but kinda messes up the rest of the columns. plus i think i read that regexextract is for text not numbers.

The answer in K should be something like $237.47 because that would leave me my total cost + $5.00 after a 13% deduction L answer should be 243.21 leaving me with 10$ after fees and my cost ... etc

basically i need it to say something like [x = column J -N] then (x - 13% = (column H + "desired profit"))

i hope that makes sense.

If you were to say it in your head it would make sense.... "what minus 13% would give me a $5, $10, $25, or $50 profit over my cost" i need a formula that tells me what "what" is lol. thanks so much i know i posted this earlier but did not want it to get buried. I appreciate the contributors time and effort.... thanks.