Calculating based on variables

[kuecker2]

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

 

[Marcelo Branco]

Maybe this...

	A​	B​	C​	D​
1​	Color​	Gallons​	Input​	Result​
2​	White​	6​		7,5​
3​	Blue​	8​	10​	10​
4​	Green​	9​		11,25​
5​	Purple​	11​		13,75​

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.

 

[Marcelo Branco]

oops...

Disregard my post above

Try

	A​	B​	C​	D​
1​	Color​	Gallons​	Input​	Result​
2​	White​	6​		7,5​
3​	Blue​	8​	10​	10​
4​	Green​	9​		11,25​
5​	Purple​	11​		13,75​

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.

 

[kuecker2]

That is what happens when I do math in my head and not in the spread sheet. Thank you.

 

[Marcelo Branco]

You are welcome.

M.