Calculating based on variables

kuecker2

New Member
Joined
May 26, 2004
Messages
17
I have a column of numbers showing gallons of paint, (column 1) they are 6 (white paint), 8 (blue paint), 9 (green paint), 11 (purple paint) this is what is needed to mix and obtain a certain cover.

The paint supplier gives me varying quantities of each of those colors. It is not the same amount or color each time.
Sometimes I have 6 gallons of white paint. Sometimes I have 15. I needed to know what the pr increase of the other gallons. Based on the amount I have. So tomorrow if I have 20 gallons or clue paint I need to know the proportional increase of the other colors. But I never know which color or quantity will change.

I have my standard numbers in column 1 They remain constant.

I want to be able to enter the changed value any one of one of those numbers by a inserting the change into column 2. So, instead of the value of 8 in column 1, I would enter 10 by entering 10 in the adjacent cell in column two.

I know how to calculate the percentage difference. The difference is 20% So now column 1 is 8, column 2 is 10, and column 3 is 10 because that is the number I input into column 2.

If I enter 10 in the second column the difference is an increase of 20% over the original number. I want the output of that increase to show up in the 3rd column as 10, because that is the amount I want to increase it. But I also want 6, 9, 11 to recalculate based on the percentage increase between 8 and 10 show up in the 3rd column So, I want 6, 9 and 11 to increase by 20% in column 3.

Up to this point I know how to do this. But the thing I am grappling with is I want to be able to enter the new amount in any one of the cells in column 2 and then have all of the values in column 3 to increase by the same percentage difference as the difference between the base number in column 1 (the static number) and number I input into column 2.

In the example above then all 4 numbers in column 3 would be a 20% increase in each of the numbers but the only number I entered manually was 10.

I want all of the numbers to increase 20% in this example. But Sometimes I want to change 11 for 100% increase and I want all of the other cells to increase by that same percentage. Now column 1 would have 11, column 2 would have 22 and column 3 would have 22. I want 6, 8, 9 to also increase by 100% and show up in column 3.

So, I want to be able to manually input the new number then have them all increase by the percentage of the difference and show up in column 3.

I am stumped how to do this. Thank you in advance
 

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.
Maybe this...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A
[/td][td="bgcolor: #DCE6F1"]
B
[/td][td="bgcolor: #DCE6F1"]
C
[/td][td="bgcolor: #DCE6F1"]
D
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1
[/td][td]
Color​
[/td][td]
Gallons​
[/td][td]
Input​
[/td][td]
Result​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2
[/td][td]
White​
[/td][td]
6​
[/td][td][/td][td]
7,5​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3
[/td][td]
Blue​
[/td][td]
8​
[/td][td]
10​
[/td][td]
10​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4
[/td][td]
Green​
[/td][td]
9​
[/td][td][/td][td]
11,25​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5
[/td][td]
Purple​
[/td][td]
11​
[/td][td][/td][td]
13,75​
[/td][/tr]
[/table]


Formula in D2 copied down
=IF(COUNT(C$2:C$5),IF(C2=MAX(C$2:C$5),C2,MAX(C$2:C$5)/INDEX(B$2:B$5,MATCH(MAX(C$2:C$5),C$2:C$5,0))*B2),B2)

By the way, increasing from 8 to 10 means an increase of 25%, not 20%.

M.
 
Upvote 0
oops...

Disregard my post above

Try

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A
[/td][td="bgcolor: #DCE6F1"]
B
[/td][td="bgcolor: #DCE6F1"]
C
[/td][td="bgcolor: #DCE6F1"]
D
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1
[/td][td]
Color​
[/td][td]
Gallons​
[/td][td]
Input​
[/td][td]
Result​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2
[/td][td]
White​
[/td][td]
6​
[/td][td][/td][td]
7,5​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3
[/td][td]
Blue​
[/td][td]
8​
[/td][td]
10​
[/td][td]
10​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4
[/td][td]
Green​
[/td][td]
9​
[/td][td][/td][td]
11,25​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5
[/td][td]
Purple​
[/td][td]
11​
[/td][td][/td][td]
13,75​
[/td][/tr]
[/table]


Array formula in D2 copied down
=IF(COUNT(C$2:C$5),B2*MAX(C$2:C$5/B$2:B$5),B2)
confirmed with Ctrl+Shift+Enter, not just Enter

M.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top