# Finding 2 different Percentages in 2 different cells

#### arossijr

##### Board Regular
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!

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### Tetra201

##### MrExcel MVP
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

##### Board Regular
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

##### MrExcel MVP
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

##### Board Regular
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>

 Pad \$ Pad GPM Rotor \$ Rotor GPM Pkg Total Testing Totals Difference Test Fix 2

<tbody>
</tbody>

#### arossijr

##### Board Regular
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

##### Board Regular
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

##### MrExcel MVP
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

##### Board Regular
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

##### MrExcel MVP
Glad to have been of help.

Replies
0
Views
136
Replies
0
Views
349
Replies
2
Views
880

1,191,177
Messages
5,985,131
Members
439,941
Latest member
robertv13

### 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