Is there a formula to utilize ratios?

[d_tech]

In column A I have random numbers. Column B is supposed to show the amounts of column A but calculated to not deviate more than a certain percent from the average. The total of column A and the total of column B should equal the same amount.

For example A1 is 51, A2 is 53, A3 is 55, and A4 is 57. I've set the percentage at 2%

B1 should then be 52.92, B2 53.64, B3 54.36, B4 55.08

If this is possible, an example would be much appreciated!

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[awoohaw]

I'm confused. Are you saying you want to enter an amount (a value) into column B and then have it processed to show the maximum of that value or 102% of the value in column A?

 

[awoohaw]

Here is a grid with getting the max of 102% of A and a value entered in column B
.
If this isn't what you want, then please post a mini worksheet with an expected view of what you want to see.

Book1
	A	B	C
1	51	52.92	52.92
2	53	53.64	54.06
3	55	54.36	56.1
4	57	55.08	58.14
Sheet11

Cell Formulas
Range	Formula
C1:C4	C1	=MAX(B1,1.02*A1)

 

[d_tech]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thanks, done

 

[Peter_SSs]

Thanks, done

Thanks. Try this.

23 02 28.xlsm
	A	B	C	D
1	51	52.92		2%
2	53	53.64
3	55	54.36
4	57	55.08
5	216	216
Scale

Cell Formulas
Range	Formula
B1:B4	B1	=AVERAGE(A$1:A$4)*(1-D$1)+(AVERAGE(A$1:A$4)*(1+D$1)-AVERAGE(A$1:A$4)*(1-D$1))/(MAX(A$1:A$4)-MIN(A$1:A$4))*(A1-MIN(A$1:A$4))
A5:B5	A5	=SUM(A1:A4)

 

[d_tech]

Here is a grid with getting the max of 102% of A and a value entered in column B
.
If this isn't what you want, then please post a mini worksheet with an expected view of what you want to see.

Book1
	A	B	C
1	51	52.92	52.92
2	53	53.64	54.06
3	55	54.36	56.1
4	57	55.08	58.14
Sheet11

Cell Formulas
Range	Formula
C1:C4	C1	=MAX(B1,1.02*A1)

Just got it ready but another helpful member has got my question answered

 

[d_tech]

Thanks. Try this.

23 02 28.xlsm
	A	B	C	D
1	51	52.92		2%
2	53	53.64
3	55	54.36
4	57	55.08
5	216	216
Scale

Cell Formulas
Range	Formula
B1:B4	B1	=AVERAGE(A$1:A$4)*(1-D$1)+(AVERAGE(A$1:A$4)*(1+D$1)-AVERAGE(A$1:A$4)*(1-D$1))/(MAX(A$1:A$4)-MIN(A$1:A$4))*(A1-MIN(A$1:A$4))
A5:B5	A5	=SUM(A1:A4)

Thank you so much!!!

 

[Peter_SSs]

Thank you so much!!!

You're welcome. Thanks for the follow-up.