Find equal distance integers

**ndt2009** · 03-25-2009, 09:20 PM

I have a formula that I am trying to create. I have 8 categories which make up a section of a grade report for employees. Each category is weighted differently with the highest weighted total being 25% and the lowest being 5%. I am trying to figure out how I can decrease the weighted totals from 25% to 5% evenly so each decrease is of an equal amount. As of right now I came up with 2.85%, but the problem is, I cannot have the whole equal more than 100%. This is what I have so far:

25%, 22.15%, 19.3%, 16.45%, 13.6%, 10.75%, 7.9%, 5.05%

The problem is when you add up all the percentanges, it equalas 1.2. Please help.

**shg** · 03-25-2009, 10:26 PM

I can't follow that explanation.

**DonkeyOte** · 03-26-2009, 04:15 AM

If I've understood...

1. You have 8 bands
2. Max band must be 25%
3. Low band must be 5%
4. Each "step" from band to band must be even
5. Sum of Band % = 100%

... then I think as you've proved you can't do this given that for the steps between all bands to be even it must be 2.85% (25% - 5% / 7 = 2.85%) ... the summation of those bands as you have stated would fail point 5 (ie sum = 120%)

shg may be able to show an alternative method... my math is not up to it I'm afraid.

**squiggler47** · 03-26-2009, 05:45 AM

The constrainst wont allow a solution, Either the range has to change (20% for the high) or the steps need to be uneven.

=(High+Low)/2*Steps

Will give you the total Percentage

=100%/Steps*2

Will give you the Total that High+Low must make to work evenly, adding up to 100%!

=15%/Steps
Will give you the step increment!

**ndt2009** · 03-26-2009, 02:27 PM

Yes, I figured that out as well that my range must change if I am set on keeping the steps from 25% to 5% the same. I am set on keeping the high/lows at 25%/5%, but how can I figure out the formula which will provide me with the answer of what I should decrease the steps by to keep with the overall 100%

**DonkeyOte** · 03-26-2009, 03:15 PM

If you're saying you want Band 8 to be 25% and Band 1 to be 5% and step Band 2 to Band 7 evenly then...

Logically you have 70% available for Bands 2 to 7
If you assume each of the bands 2 to 7 start at 5% (min band) that leaves you with 40% to apportion incrementally (70% - (5%*6)) ... where:

Band 2 = 5% + 1 increment
Band 3 = 5% + 2 increments
Band 4 = 5% + 3 increments
Band 5 = 5% + 4 increments
Band 6 = 5% + 5 increments
Band 7 = 5% + 6 increments

Given each increment is constant you can see you have 21 increments by which to apportion the 40%... 1 increment therefore equals 1.90%

So if A1 = 5% & A8 = 25%

then

A2: =A1+(40%/21)
copied down to A7

So you get:

5%, 6.90%, 8.81%, 10.71%, 12.62%, 14.52%, 16.43%, 25%

total = 100%

Is that what you meant ?

**ndt2009** · 03-26-2009, 08:24 PM

Yes that does work, BUT it does not work if you have a total band of 10 or a total band of 5. Is there a formula that would work no matter how many bands you choose?

**DonkeyOte** · 03-27-2009, 04:26 AM

Does this help any ?
Changing the cells in Yellow should recalculated the bands as required.

By my reckoning you must have a min of 6 bands in order for the 25% to not be exceeded by the preceding bands (though with 6 band 5 also = 25%)

As a disclaimer - again I'm not a mathematician so although I think this is correct I wouldn't stake my house on it...

EDIT:

You could think of the incremental % for Bands 2 to Band x-1 in formula terms as:

=(100%-Min%-Max%-((Bands-2)*Min%))/(((Bands-2)/2)*(Bands-1))