• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Balanced adjustment of cell values

jb

Member
Hello Helpers,

I have an excel sheet jb rank.xlsx. This excel sheet is a solution given by one member in this forum long back.

My problem was to add a value say 16 to range of cells in such a way that new value of cell after adding portion of value 16 to that cell should not exceed to any cell which has original value greater. and the distribution is to be done in such a way that all cells must get balanced values.

For example: 11, 6 , 9 , 15 , 8 ,17 will be balanced like 13, 12, 13, 15, 12, 17


Given solution gives correct output for some set of data. But when I enter following data 26 23 25 26 28 27 in B2 to G2 then it adjusts the cells with values 15 instead of 16.

Please help.
 

Attachments

vletm

Excel Ninja
jb
But when I enter following data 26 23 25 26 28 27 in B2 to G2 then it adjusts the cells with values 15 instead of 16.
... how? .. what do You mean?
>> anyway other way to solve something ... or not?
 

Attachments

CA Rohit

New Member
Hello Helpers,

I have an excel sheet jb rank.xlsx. This excel sheet is a solution given by one member in this forum long back.

My problem was to add a value say 16 to range of cells in such a way that new value of cell after adding portion of value 16 to that cell should not exceed to any cell which has original value greater. and the distribution is to be done in such a way that all cells must get balanced values.

For example: 11, 6 , 9 , 15 , 8 ,17 will be balanced like 13, 12, 13, 15, 12, 17


Given solution gives correct output for some set of data. But when I enter following data 26 23 25 26 28 27 in B2 to G2 then it adjusts the cells with values 15 instead of 16.

Please help.

This problem is occurring because 26 is repeated 2 times and hence two numbers have same rank - 1.2.3.3.5.6. As 4th rank is missing, the formula is not allocating any value, hence stuck at 15. If you change one of the rank given to 26 as 4, your formula will work.
 

jb

Member
CA Rohit,
Your suggestion gave me solution. I found formula to assign rank with no repeat values. And it worked.
Thanks a lot.
 

jb

Member
Oooops CA Rohit
Your solution not worked for all data.
When I tried to enter different data, distribution failed.

Look at the data entered in b2 to g2.
Distribution done upto value 18 when in any case it should go upto 16 only which is there in H1.
12 , 23 , 25, 26, 28, 27
 

Attachments

jb

Member
vletm and CA Rohit,

Final time I am disturbing helpers. Sorry for starting new thread. Actually I really need this excel sheet. I will not post anything again regarding this issue again.

H1 contains value 16. 16 points are to be distributed between 6 cell values entered in B2 to G2. It means that 11, 6 , 9 , 15 , 8 ,17 will be balanced like 13, 12, 13, 15, 12, 17. So sum(13, 12, 13, 15, 12, 17) = sum(11, 6 , 9 , 15 , 8 ,17) + 16.

It is not compulsory to increase values of all the cells. Above example: 15 and 17 remains as it is.

Due to rank formula, my distribution was not going upto desired value 16 as I have mentioned in starting of this thread. Then according to suggestion of CA Rohit, I updated that formula.

Case-1:
Given solution gives correct output for some set of data. But when I enter following data 12 23 25 26 28 27 in B2 to G2 then it adjusts the cells b2 to g2 with values 18 instead of 16.

Case-2:
Sum of vales added to b2 to g2 should be exactly 16.

Total 16 points is to be added in such a way that new value of cell after adding portion of value 16 to that cell should not exceed to any cell which has original value greater. For example, two cells having value 9 and 5 should not be increased like 9+4=13 and 5+9=14 because originally 9 is greater than 5, so final value of 9 (i.e. 13) can not be less than final value of 5 (i.e. 14). 9+4=13 and 5+8=13 is valid. This rule is to be checked against all cells.
For example: 26,23,25,26,28,27 is balanced with value 16 but the final values are
28,29,29,29,28,28 does not follow rules.
Because 26 becomes 28 and 23 becomes 29 and 25 becomes 29 which is wrong. Because 26 is greater than 23 and 25. so final value of 26 (i.e 28) can not be less than final value of 23 and 25 (i.e. 29). They can be equal.

And the distribution is to be done in such a way that all cells must get balanced values.
 

Attachments

jb

Member
jb
what is challenge with my file (#7 Reply)?
Dear vletm,
Issue with your file is it is not distributing value 16. Sir, it is distributing only value 4. B2 is 12 it is becoming 16. Rest C2 to G2 is same. Look at final value B18 to G18.
 

vletm

Excel Ninja
jb ... it's raining
If Your 'Add 1 to a value
if it equals the minimum value and
the total amount added is less than the Distribution Total.
'
is still Your rule.
Then only value B2 could add 1 four times until 'Distribution' is 16.
Other values C2:G2 are bigger than 16.
Something do not match - or what?
 

jb

Member
Hi ,

See if the attached file does what you want. The final result is in the range B22:G22.

Narayan

Sir,

When I checked your file with my data
28 21 23 26 18 22 in b2:g2 it gives me answer
26 25 26 26 25 26. It increases the total by 16 which is correct.

But sir, while doing adjustment we can not decrease the value of cell in order to do adjustment. We can increase value or keep as it is.

Here, original value 28 in b2 becomes 26 which is not allowed.

Can you help me sir?

thanks.
 

NARAYANK991

Excel Ninja
Hi ,

What you are asking for is mathematically impossible.

If we take the example you have posted , we start with the following numbers :

28 21 23 26 18 22

If we are to assume that no number can be less than its initial value , then we have to first consider the maximum number in the set ; in this case , it is 28.

Thus , if we cannot reduce 28 , we have to maintain it at 28.

Therefore , the remaining numbers all have to come up to 28 or at the least 27.

If we consider even 27 , then the result will be :

28 27 27 27 27 27

which gives us a total of 0 + 6 + 4 + 1 + 9 + 7 = 27 !

If we have to distribute 16 , you can see that there will be a lot of variation.

One possibility will be if we consider 16 as the number to be distributed , we need to reduce the above total of 27 by 11 , which means we have to reduce 2 from each number , and one number will have to be reduced by 3 , as follows :

28 25 25 25 24 25

Is something like this acceptable ?

We will have to see how this can be achieved using formulae.

Narayan
 

jb

Member
Sir,

28 21 23 26 18 22 can become
28 25 25 25 24 25 But there is one error.

4th value 26 is becoming 25 which is not allowed.

Sir, actually these six values are the marks of 6 subjects. Subjects can be more also.

Now, I need to give them extra 16 marks for their attendance component.

this 16 marks are to be distributed amongst 6 subjects in such a way that

1. We can not reduce achieved marks of subject. We can increase only if required.
2. there must be a balanced distribution. It means that trying to make them almost equal or bit less.
3. For example, two cells having value 9 and 5 should not be increased like 9+4=13 and 5+9=14 because originally 9 is greater than 5, so final value of 9 (i.e. 13) can not be less than final value of 5 (i.e. 14). 9+4=13 and 5+8=13 is valid. This rule is to be checked against all cells.
For example: 26,23,25,26,28,27 is balanced with value 16 but the final values are
28,29,29,29,28,28 does not follow rules.
Because 26 becomes 28 and 23 becomes 29 and 25 becomes 29 which is wrong. Because 26 is greater than 23 and 25. so final value of 26 (i.e 28) can not be less than final value of 23 and 25 (i.e. 29). They can be equal.
 

r2c2

Active Member
@jb

How about using Solver to minimize standard deviation of new values? See attached workbook. It takes input marks and allocation amount and distributes the amount to input marks so that SD of new vals is minimized while observing your rules. Please note that not all inputs can have an acceptable solution.
 

Attachments

NARAYANK991

Excel Ninja
Hi ,

The example that we have considered illustrates the problem perfectly !

If the marks are : 28 21 23 26 18 22

and if 28 and 26 cannot reduce , then we need to distribute 16 among only 4 subjects , and if we wish to reduce the variation among these 4 subjects , then one way would be :

28 25 25 26 25 25

Any further addition will increase the total distributed marks beyond 16 , while any reduction in one or more subjects will increase the variation.

Note : The distributed output I posted in my earlier comment had a mistake , and the reduction of 26 to 25 was really not necessary.

I doubt that any formula can give the desired end result , since this is a process of checking , eliminating those subjects where no reduction is possible , and then adding to the remaining subjects through an iterative process.

As Chandoo has suggested , the Solver is a tool which does exactly this ; of course running the Solver iteratively over several students may need a macro to make it convenient for you. That is also possible , if it is acceptable.

Narayan
 

jb

Member
@jb

How about using Solver to minimize standard deviation of new values? See attached workbook. It takes input marks and allocation amount and distributes the amount to input marks so that SD of new vals is minimized while observing your rules. Please note that not all inputs can have an acceptable solution.
Dear Helper r2c2,
I found solver in excel but when I tried to change values in blue cell, it started giving NAME error in calculation cells.
Am I doing something wrong?
Help me please.
 
Top