• 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.

add values to range of cell so that all cell get almost same value

jb

jb

Member
Hi all,
I have some values in 6 cell say a1 to f1. these values can be different.
For e.g.
11 6 9 15 8 17

Now I want to add some number say 16 which is to be distributed in all the cells in such a way that all cell get almost same value.

I want formula so that I will get the value to added in cells a2 to f2.

But there is a catch: It should not happen that I add 5 to 6 which equal to 11 and 1 to 9 which equal to 10. because original values are 6 and 9. 9 is bigger. so final value of 6 can not be more than final value of 6. They can be equal.
This rule is to be checked against all cells.
 
Assuming
upload_2016-3-29_22-19-4.png

In A2: =$H$2*(A1/$H$1)
Copy across

This has distributed the 16 on a pro-rata basis between all cells in A1:F1
That is larger values get more of the 16
 
so final value of 6 can not be more than final value of 6
Click to expand...
I'm not sure what you're trying to say. Can you share some examples, of starting values and ending values illustrating how this should work?
 
Another way to distribute maybe as follows
upload_2016-3-29_22-27-13.png

Row 1 is your data
Row 2 is the difference between the Max Value of the Data (17) and the columns value eg: 17 - 11 = 6

Row 3 is the pro-rata distribution of the 16 across the Row 2
eg: A3: =$H$3*(A2/$H$2) copy across
Row 4 is the revised Data Row 1 + Row 3

This has tended to even out the values rather than maintaining the differences that my previous solution did
 
Sorry helpers,
I think I failed in explaining my problem. Sorry once again for that. Now I am uploading a file.

Look at 2nd, 4th and 6th row with title original, adjustment and final.

Original titled row shows actual data. Adjustment row B4 to G4 shows values to be added in B2 to G2 so that total of adjustment should not exceed 16.

Final row B6 to G6 is total of Original and Adjustment. Here B6 to G6 have values in almost same range.

This adjustment is valid because
B2 i.e. 12 getting 2 resulting in 14 , 7 is getting 6 resulting in 13. Here original value is 12 and 7 so final value of 12 should not be less than final value of 7.

B2 i.e. 12 getting 2 resulting in 14 , 13 is getting 2 resulting in 15. Here original value is 12 and 13 so final value of 12 should not be more than final value of 13.

Row 9, 11 and 13 shows example of wrong adjustment.

12+3 = 15, 13+1 = 14, 11+2=13

12 is smaller than 13 so its final value can not be 15.
 

Attachments

  • test.xlsx
    9.7 KB · Views: 6
Did you review my final post above?
It gives a similar result to your manual result and appears to follow the same rules

Please comment as to what is wrong with that solution?

It is attached as a file for your perusal
 

Attachments

  • Split values across range.xlsx
    10 KB · Views: 10
Hui said:
Did you review my final post above?
It gives a similar result to your manual result and appears to follow the same rules

Please comment as to what is wrong with that solution?

It is attached as a file for your perusal
Click to expand...

Hui sir,
according to your solution,
11, 6, 9, 15, 8, 17 becomes 14,11,13,16,12,17

I want to increase smaller values.

so, 11, 6, 9, 15, 8, 17 should become 13, 12, 13, 15, 12, 17
 
Hi ,

You are stating a requirement which seems to be inconsistent.

1. Assume there is a set of numbers ; is there any limit on how many there can be ? Is this set having a fixed number of values or will the number of values vary ?

2. Is there any limit of how much of variation there can be in the values ? For example , in your set , the minimum value is 6 while the maximum is 17 ; will there be a much wider variation ?

3. In your posted example , the addition is 16 ; will this addition always be a positive value or can there be a negative value also ? What will be the extent of variation in the value of this addition ? Can it be very small values or even bigger values ?

4. You say that the distribution of the addition value should be amongst the smaller values ; what if the small value is just one ? Depending on the number of small values , will this not result in some small value becoming bigger than some originally bigger value ? What will be the logic for the distribution ?

Narayan
 
My first two numbers are 14, 11
Yours are 13, 12

Mine is based on a pro-rata distribution of the 16 across the differences from the maximum value

What is the logic for your distribution?
 
Hi Hui and Narayank991,

Answer-1 There will be maximum 16 cells out of which at a time randomly maximum 8 cells will be having values. It means that there will be maximum 8 values in a set. They can be at any 8 or less positions in range of 16 cells.

Answer-2 There is no limit for variation.

Answer-3 Value to be added will be in range of +12 to +16 only. No other value.

Answer-4 I want to distribute a value say 16 in such a way that all values in a set will come in almost same range. For doing this, it will be okay to give some value to higher numbers also.

only one rule to be followed:
12 getting 1 resulting in 13
7 getting 7 resulting in 14 is not allowed.

Here original value is 12 and 7 so final value of 12 should not be less than final value of 7. They can be equal.
 
Hello, I believe I understand the goal so I will attempt to clarify. I believe jb is looking to add 1 to a value if it equals the minimum value and the total amount added is less than the distribution total. Repeat until the Distribution Total is distributed.

For a table starting in A1 and using the formula =IF(B1=MIN($B1:$G1),B1+1,B1) in B2, drag it across and we can illustrate it step by step as follows:
Code: 
Starting Values 11 6  9  15 8  17 0
First Pass      11 7  9  15 8  17 1
Second Pass     11 8  9  15 8  17 2
Third Pass      11 9  9  15 9  17 4
Fourth Pass     11 10 10 15 10 17 7
Fifth Pass      11 11 11 15 11 17 10
Sixth Pass      12 12 12 15 12 17 14

You can also add a running total formula in H1 with =SUM(B1:G1)-SUM(B$1:G$1) which shows 14 as the Running Total Added after six passes.

Unfortunately I'm not knowledgeable enough in Excel to create the formula to answer the question, but I hope this arms the ninjas with the info they need.
 
I actually had a revelation waking up this morning: we can use the RANK function in a helper row to finish my attempt at solving this question.

Can't post workbook but see if you can follow:
  1. Row 1 is the Helper row with =RANK(B2,$B2:$G2,1) in B1, drag it to G1. Key the Distribution Number into H1.
  2. Paste your Starting Values in B2:G2.
  3. Use =SUM(B2:G2)-SUM(B$2:G$2) in H2 and drag to H18.
  4. And finally, paste this whopper into B3 and drag down to G18: =IF($H2+COUNTIF($B2:$G2,MIN($B2:$G2))>$H$1,IF(B$1<=($H$1-$H2),B2+1,B2),IF(B2=MIN($B2:$G2),B2+1,B2))

jb, this creates the solution 12, 13, 12, 15, 13, 17 instead of 13, 12, 13, 15, 12, 17 that you gave in your previous post. Mine is different because in your initial post:
"It should not happen that I add 5 to 6 which equal to 11 and 1 to 9 which equal to 10. because original values are 6 and 9. 9 is bigger. so final value of 6 can not be more than final value of 6. They can be equal."
Your solution has original value 11 equal 13 and original value 9 equal 12 which goes against your initial message. My solution should follow your conditions.

Please let us know your thoughts. :)
 
Hi ,

I think what has been posted by jb is correct.

The original numbers were :

11 , 6 , 9 , 15 , 8 , 17

It is clear that the 3 bigger numbers 11 , 15 and 17 cannot be reduced.

What is important is that the 3 lower numbers 6 , 9 and 8 cannot be increased so much that they are bigger than 11 , 15 or 17.

Thus the original progression of numbers has to be maintained.

6 may be increased to X , and 8 may be increased to Y , but X has to be less than or equal to Y , since 6 is less than 8.

This same rule will apply to all other numbers.

Thus the resulting set of numbers :

13 , 12 , 13 , 15 , 12 , 17

ensures that 6 has increased to 12 , 8 has also increased to 12 , 9 has increased to 13 , 11 has increased to 13 , and 15 and 17 have retained their values.

Your suggested set of numbers :

12 , 13 , 12 , 15 , 13 , 17

would mean that 6 has increased to 13 , while 9 and 11 have both increased only to 12 ; thus while originally 6 was less than 9 and 11 , after the adjustment it would be bigger than both 9 and 11. Similarly 8 increasing to 13 while 11 increases to 12 is also not allowed.

Narayan
 
Last edited:
OK I believe I've fixed my attempt. Sorry for the confusion.

Can't post workbook but see if you can follow:
  1. Row 1 is the Helper row with =RANK(B2,$B2:$G2,1) in B1, drag it to G1. Key the Distribution Number into H1.
  2. Paste your Starting Values in B2:G2.
  3. Use =SUM(B2:G2)-SUM(B$2:G$2) in H2 and drag to H18.
  4. And finally, paste this whopper into B3 and drag down to G18: =IF($H$1=$H2,B2,IF($H2+COUNTIF($B2:$G2,MIN($B2:$G2))>$H$1,IF(AND(B2=MIN($B2:$G2),B$1-(COUNTIF($B2:$G2,MIN($B2:$G2))-($H$1-$H2))>0),B2+1,B2),IF(B2=MIN($B2:$G2),B2+1,B2)))

Hopefully this helps with the solution.
 
Shteven said:
See attached file.
Click to expand...
Sir Shteven,
Last solution you supplied in jb rank.xlsx 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.
 
You must log in or register to reply here.
Back
Top