Adjust values below with specific value

It's really not clear how you're determining the Adjusted WT. You need to provide a better explanation of how you determine the Adjusted WT for each of the items. For example, S.NO. 2 is 22.67 and S.NO. 5 is 24.56 but they both have the same Adjusted WT of 23.65. Why is that the case? We will need better explanations for each item.

I try to put that value below 23.7, because 23.7 is my first choice,

but you can use any choice and put formula, no need to follow my manual values

I'm no mathematician but this is one method

in cells G2 to G5

=IF(ABS(F2-$J$2)<ABS(F2-$J$3),$J$2,$J$3)

in G6 =F7 -SUM(G2:G5)

How did you calculate your manual results?

I am sure the mathematicians in the Excel fraternity will find a suitable formula.

23.7
23.7
27.7
27.7
23.070
125.870

Thanks for you reply,
But displaying same value like 23.7 or 27.7.
I want different values,
This is transport container values, each container have separate value. So need to manage different values but below 23.7 or 27.7

The problem is that Excel is just like any other program in the sense that it only does what you tell it to do. You haven't provided any rules for determining the Adjusted WT other than your preference that the Adjusted WTs be under 23.7 or 27.7. If you can't provide any rules, then the numbers you're picking are just arbitrary. We can't come up with a formula for that.

As stated previously, there are no obvious rules other than "trial and error". The formula below illustrates this - there no pre-defined rule(s)

in G2:G5

=IF(ABS(F2-23.7)<ABS(F2-27.7),23+RANDBETWEEN(5,10)/10,27-RANDBETWEEN(1,5)/10)



G6 =F7-SUM(G2:G5)

Finally I got below result with some changes in fourmula :

=IF(ABS(F3-23.7)<ABS(F3-27.7),23.05+RANDBETWEEN(4.1,6.6)/10,27.45+RANDBETWEEN(1,2)/10)

23.650
23.550
23.650
23.550
27.650

in above values last 3 digits continuously coming like 550, 650. can I get different values like 520, 540, 560, 620, 670, 630, between 450 to 690.

Please ignore above replay

I had some changes in formula as
=IF(ABS(F3-23.7)<ABS(F3-27.7),23.05+RANDBETWEEN(4.1,6.6)/10,27.45+RANDBETWEEN(1,2)/10)

But values came as below
23.650
23.550
23.650
23.550
27.650
27.650
23.650
27.550
23.650
27.550
27.650
23.550
23.550
27.650
27.650

(I little bit extend that statement, please see attachment)
in above values last 3 digits continuously coming like 550, 650. can I get different values like 480, 530, 660, 450, 500, 620, 670, 630 like that, between 480 to 690. with zigzag order.