Adjust weight based on Slab, Total must be same

Hi Friends,

From lot of days I am suffering with accurate formula for below problem. Please see attachment and advice me, Thank you advance.

I have two slabs as E column

A2 weight is 25450, so I adjust it to 23570 (like Random value of 23700)
A3 weight is 27460, so I adjust it to random value of E3 (like Random value of 2700)
A4 weight is 25560, so I adjust it to random value of E3 (like Random value of 2700)
A5 weight is 21450, so I adjust it to random value of E2(like Random value of 23700)

Main thing is below 25500 value can reduce to below 23700
above 25500 value can increase upto 27700
but total must be same, adjusted weight should automatically adjust based on net weight total

Originally Posted by rajeshn_in

A2 weight is 25450, so I adjust it to 23570 (like Randam value of 23700)
A3 weight is 27460, so I adjust it to randam value of E3 (like Randam value of 2700)
A4 weight is 25560, so I adjust it to randam value of E3 (like Randam value of 2700)
A5 weight is 21450, so I adjust it to randam value of E2(like Randam value of 23700)

Main thing is below 25500 value can reduce to below 23700
above 25500 value can increase upto 27700
but total must be same, adjusted weight should automatically adjust based on net weight total

A2 weight is 25450 how can you adjust it to 23570

Please explain???

For example Slab 1 = 1$, Slab 2 = 2$,
So, In that 4 Containers, actually I need to pay 2$ for each Container.
with adjustment I can pay 3 containers as 2$ for each, and for 1 container as 1$ for each, at the end I can save 1$, just like that.

Try with a helper column

H2

Formula:

Please Login or Register  to view this content.

drag down

Result in
I2

Formula:

Please Login or Register  to view this content.

drag down

Check the attached file.

Originally Posted by rajeshn_in

Thank you, Shukla, one small update. in number last digit must be 0, no need decimals. in your file result as below
23033.5
26833.5
27090.5
22962.5


I want like
23030
26830
27090
22960

Please update.

That would happen as you need to match your number A6.

If you'll press f9 key three to four time is it will give your number without decimal

Originally Posted by rajeshn_in

Hi John,

You formula 95% working, Thank you.

But, Because of Random formula, H column cell values are frequently changing, how can I control that without "copy value past".


One more thing is If I am using 100 containers, then total not coming same. Please see attachment.

Thank you for your Patience.......

You can try with another helper column.

Try

H2

Formula:

Please Login or Register  to view this content.

I2

Formula:

Please Login or Register  to view this content.

J2

Formula:

Please Login or Register  to view this content.

Check the attached file.

Hi Shukla,

Please check attachment, at result column, I am getting values either above 23700 or 27700, I think helper 2 not coming as my required. Please check and update.

Can we control Helper 2, like values must be below 23700 or 27700 like that.

Main think is all values must be below 27700.

Hi John Topley,

Please check attachment, at result column, I am getting values either above 23700 or 27700, I think result column value not coming as my requirement. Please check and update.

Can we control result column, like values must be below 23700 or 27700 like that.

Main think is all values must be below 27700.

Sorry, its not solved, please check, All Values must be below 27700, And some values can be adjust under 23700 in given attachment, but its not working.

Sorry, its not solved, please check, All Values must be below 27700, And some values can be adjust under 23700 in given attachment, but its not working.

Dear Rajesh, Refer attach file, in this file value distribution based on first two cell (Highlighted). I think this is similar with your requirement.

Attach your latest updated file with actual required output (mentioned exact value manually).

I have attach file. Kindly refer. In this file i calculated with criteria min, max (yellow cell) & Avg (Green cell)