Total must be same

[rajeshn_in]

Hi Friends,

I have a problem in excel totals,

Please see attachment.

I apply one formula but total not coming same.

U can use different formula but main aim is put near or below 23.7 is first choice, otherwise 27.7 is second choice, please solve this ......

 

[Hui]

If the If is True it will return a random value between 23.41 & 23.66
If the If is False it will return a random value between 27.46 & 27.71

If you want to change that you need to change the values in the Randbetween formula

 

[rajeshn_in]

If the If is True it will return a random value between 23.41 & 23.66
If the If is False it will return a random value between 27.46 & 27.71

If you want to change that you need to change the values in the Randbetween formula

Please post formula .......

 

[rajeshn_in]

Just for your understanding......

These are container weights.....

In some countries Fee apply depends on container weight.

So, below 23.7 have some less charge.

So that between 22 to 25 + we need to put below 23.7. (i.e. 23.55, 23.61, 23.64 ....)

excess weight can forward and add to other containers which have 25 + to 27.7 (27.7 is maximum weight).

So, maximum try to adjust original weight upto 23.7, if not possible then,

26 + container weight can adjust 27.6 like that, than we can reduce 24 to 25 tones weight in to 23.7

 

[Luke M]

See attached. Formula structure is like this:

=IF(F2<25.5,
MAX(MIN(F2,23.7),F2-MAX(0,(MIN(SUMPRODUCT(($F$2:$F$102>=25.5)*(27.7-$F$2:$F$102)),SUMPRODUCT(($F$2:$F$102<25.5)*($F$2:$F$102>23.7)*($F$2:$F$102-23.7)))-(SUMIF(F$1:F1,"<25.5")-SUMIF(G$1:G1,"<25.5"))))),
MIN(MAX(F2,27.7),F2+MAX(0,(MIN(SUMPRODUCT(($F$2:$F$102>=25.5)*(27.7-$F$2:$F$102)),SUMPRODUCT(($F$2:$F$102<25.5)*($F$2:$F$102>23.7)*($F$2:$F$102-23.7)))-(SUMIF(G$1:G1,">=25.5")-SUMIF(F$1:F1,">=25.5"))))))

 

[Shteven]

Wow Luke, nice. Can you explain what the 25.5 value represents and why you picked it?

 

[Luke M]

Wow Luke, nice. Can you explain what the 25.5 value represents and why you picked it?

Not clear from just this thread, but OP had contacted my privately about this issue. I only stumbled across this thread after I had come up with a solution elsewhere. In our discussion, author has stated that he wanted to use 25.5 as a dividor for which values should go up, and which should go down.

From a functional use of the formula, it could be any number between 23.7 and 27.7, I suppose.