Finding 2 different Percentages in 2 different cells

[arossijr]

I know the description is vague but I am not sure how to explain this one. I am working on a sheet that has all the information required BUT I am having trouble figuring out the formula to do the math.
I want to do the following with the following information:
Columns D and E are fixed numbers which equal the cost of the items. To hopefully make this simple the package deal at cost would be D4+E4+E4=F4
What I am trying to do is take O to be a Fixed Percentage of increase of E4 (Qty 1) and it provide the Dollar Amount in N4.
Then have M4 create a Auto Percentage and L4 equal the dollar amount increase of D4. All of this using a fixed amount in P4 which would be $60.

ITem 1(Fixed)	Item 2(Fixed)	Item 1 ( New Price )	Item 1 ( Auto % )	Item 2 qty 1( New Price )	Item 2 Qty 1 (Fixed % )	PKG Total(Fixed) Item1+Item2+Item2
17.22	17.54	19.66	14.18%	20.17	15.00%	60.00
14.79	17.54	19.66	32.90%	20.17	15.00%	60.00
12.38	17.54	19.65	58.75%	20.17	15.00%	60.00

<tbody>
</tbody>

Item Once Dollar amount changes, Item 2 Dollar amount changes ( Base on Product Number ) In this Example Item 2 happens to be the same Product Number so its dollar amount is same in 3 fields... The Idea is to keep all Product numbers to have the same % and $ amounts and then have Item 1's % and $ amounts change to equal the $60 mark.
Anyone have an Idea.. I have tried a few and come up with BIG errors...

As always any assistance is greatly appreciated!!

Thank you!

 

[Tetra201]

If I understood you correctly, here is a formula for column M (Item 1 ( Auto % )):

=(P2-2*ROUND(E2*(1+O2),2))/D2-1

For column L (Item 1 ( New Price )), use =ROUND(D2*(1+M2),2)

 

[arossijr]

Tetra201, I think you are on the right track but hitting the same wall that I am. I am taking a good look at this and i think i goofed my description.
Item 1 Item2 are fixed they are the "Base" price, I am "fixing" the Item 2 Fixed Percent at 15% to equal the "new" fixed Item 2 Price at $20.17.
The "Package Price" cannot exceed $60, So I need to find the new Item 1 Price and Item 1 Percent.
In the end Item 1 New $ + Item 2 New $ + Item 2 New $ should equal Package Price of $60
When I use your formulas, which are way cleaner then mine, I get the Item 1 New $ as the same as the Item 1 Fixed price(base) and a 3 digit percentage price.

 

[Tetra201]

I am not hitting any wall; I am trying to help you.

You have to clearly identify 4 preset parameters, all others derive from them.

For example, "I have preset parameters in D2, E2, N2, and P2. What would be the formulas for L2, M2, and O2?"

 

[arossijr]

O2 Would be fixed number no Formula just straight 15% ( as an Example from Above )
M2 is open waiting for a percentage of L2, but L2 is dependent on the Remainder from N2+N2-P2 (P2 is a constant at $60)
I found a long about way of finding the info i can try to post the sample here:
Formulas First:

=SUM(D4+R4)	=IFERROR((L4-D4)/ABS(L4),0)	=E4*(1+O4)	0.15	60	=SUM(N4+N4+D4)	=SUM(P4-Q4)	=SUM(N4+N4+L4)
=SUM(D5+R5)	=IFERROR((L5-D5)/ABS(L5),0)	=E5*(1+O5)	0.15	60	=SUM(N5+N5+D5)	=SUM(P5-Q5)	=SUM(N5+N5+L5)
=SUM(D6+R6)	=IFERROR((L6-D6)/ABS(L6),0)	=E6*(1+O6)	0.15	60	=SUM(N6+N6+D6)	=SUM(P6-Q6)	=SUM(N6+N6+L6)
=SUM(D7+R7)	=IFERROR((L7-D7)/ABS(L7),0)	=E7*(1+O7)	0.15	60	=SUM(N7+N7+D7)	=SUM(P7-Q7)	=SUM(N7+N7+L7)
=SUM(D8+R8)	=IFERROR((L8-D8)/ABS(L8),0)	=E8*(1+O8)	0.15	60	=SUM(N8+N8+D8)	=SUM(P8-Q8)	=SUM(N8+N8+L8)
=SUM(D9+R9)	=IFERROR((L9-D9)/ABS(L9),0)	=E9*(1+O9)	0.15	60	=SUM(N9+N9+D9)	=SUM(P9-Q9)	=SUM(N9+N9+L9)
=SUM(D10+R10)	=IFERROR((L10-D10)/ABS(L10),0)	=E10*(1+O10)	0.15	60	=SUM(N10+N10+D10)	=SUM(P10-Q10)	=SUM(N10+N10+L10)
=SUM(D11+R11)	=IFERROR((L11-D11)/ABS(L11),0)	=E11*(1+O11)	0.15	60	=SUM(N11+N11+D11)	=SUM(P11-Q11)	=SUM(N11+N11+L11)
=SUM(D12+R12)	=IFERROR((L12-D12)/ABS(L12),0)	=E12*(1+O12)	0.16	60	=SUM(N12+N12+D12)	=SUM(P12-Q12)	=SUM(N12+N12+L12)
=SUM(D13+R13)	=IFERROR((L13-D13)/ABS(L13),0)	=E13*(1+O13)	0.16	60	=SUM(N13+N13+D13)	=SUM(P13-Q13)	=SUM(N13+N13+L13)
=SUM(D14+R14)	=IFERROR((L14-D14)/ABS(L14),0)	=E14*(1+O14)	0.16	60	=SUM(N14+N14+D14)	=SUM(P14-Q14)	=SUM(N14+N14+L14)
=SUM(D15+R15)	=IFERROR((L15-D15)/ABS(L15),0)	=E15*(1+O15)	0.16	60	=SUM(N15+N15+D15)	=SUM(P15-Q15)	=SUM(N15+N15+L15)
=SUM(D16+R16)	=IFERROR((L16-D16)/ABS(L16),0)	=E16*(1+O16)	0.16	60	=SUM(N16+N16+D16)	=SUM(P16-Q16)	=SUM(N16+N16+L16)
=SUM(D17+R17)	=IFERROR((L17-D17)/ABS(L17),0)	=E17*(1+O17)	0.16	60	=SUM(N17+N17+D17)	=SUM(P17-Q17)	=SUM(N17+N17+L17)
=SUM(D18+R18)	=IFERROR((L18-D18)/ABS(L18),0)	=E18*(1+O18)	0.16	60	=SUM(N18+N18+D18)	=SUM(P18-Q18)	=SUM(N18+N18+L18)
=SUM(D19+R19)	=IFERROR((L19-D19)/ABS(L19),0)	=E19*(1+O19)	0.17	60	=SUM(N19+N19+D19)	=SUM(P19-Q19)	=SUM(N19+N19+L19)

<colgroup><col span="3"><col><col><col><col><col></colgroup><tbody>
</tbody>

Now the actual data:

$19.66	12.40%	$20.17	15.00%	$60.00	$57.56	$2.44	$60.00
$19.66	24.76%	$20.17	15.00%	$60.00	$55.13	$4.87	$60.00
$19.66	37.02%	$20.17	15.00%	$60.00	$52.72	$7.28	$60.00
$19.66	17.74%	$20.17	15.00%	$60.00	$56.51	$3.49	$60.00
$19.66	40.23%	$20.17	15.00%	$60.00	$52.09	$7.91	$60.00
$19.66	23.39%	$20.17	15.00%	$60.00	$55.40	$4.60	$60.00
$19.66	48.62%	$20.17	15.00%	$60.00	$50.44	$9.56	$60.00
$19.66	34.78%	$20.17	15.00%	$60.00	$53.16	$6.84	$60.00
$32.53	53.77%	$13.73	16.00%	$60.00	$42.51	$17.49	$60.00
$32.53	57.49%	$13.73	16.00%	$60.00	$41.30	$18.70	$60.00
$29.17	46.93%	$15.42	16.00%	$60.00	$46.31	$13.69	$60.00
$29.17	40.96%	$15.42	16.00%	$60.00	$48.05	$11.95	$60.00
$34.39	65.42%	$12.81	16.00%	$60.00	$37.50	$22.50	$60.00
$29.17	65.37%	$15.42	16.00%	$60.00	$40.93	$19.07	$60.00
$29.17	43.05%	$15.42	16.00%	$60.00	$47.44	$12.56	$60.00
$18.96	32.95%	$20.52	17.00%	$60.00	$53.75	$6.25	$60.00

<colgroup><col span="3"><col><col><col><col><col></colgroup><tbody>
</tbody>

I forgot the column headings:

Pad $

Pad GPM

Rotor $

Rotor GPM

Pkg Total

Testing Totals

Difference

Test Fix 2

<tbody>
</tbody>

 

[arossijr]

I forgot to to include the base price column that contains the "Fixed" amount

$17.22
$14.79
$12.38
$16.17
$11.75
$15.06
$10.10
$12.82
$15.04
$13.83
$15.48
$17.22
$11.89
$10.10
$16.61

<colgroup><col></colgroup><tbody>
</tbody>

 

[arossijr]

Tetra201,

Please dont get me wrong, Im not saying that you are hitting the wall, I apologize for that, my description is/was not the greatest. That is 100% on me.
I do appreciate the assistance greatly. If i had a way of putting the sheet up with what I have I would in a sec

Thanks for any and all assistance!

 

[Tetra201]

Here is what I gathered from your posts:

Columns D and E are fixed numbers

O2 Would be fixed number no Formula just straight 15%

P2 is a constant at $60

Based on this info, the formulas are:

for cell M2 =(P2-2*ROUND(E2*(1+O2),2))/D2-1
for cell L2 =ROUND(D2*(1+M2),2)
for cell N2 =ROUND(E2*(1+O2),2)

 

[arossijr]

Tetra201,

Giving this a shot now, going to my second test copy of the original file. But from what I am seeing on the file I am working on it is coming up all Aces!!

Thanks! Greatly appreciated!!

 

[Tetra201]

Glad to have been of help.