Roundup/Rounddown to match total to allowed

[jeffreybrown]

I can't seem to find the correct logic here...

I've tried a combination of roundups and downs, but as soon as I drag right it doesn't calculate right because of the changed in decimal places.

I have two pairs, the SR/SS in row 3/4 and the TS/MS in row 5/6.

Column D contains the total count from each pair which is multiplied by the percentage in column C.

Column E contains the percentage based on the total of column D from each row.

Starting in column F, this is the number of full time positions earned, which in this case begins with 42 all the way out to 70.

F3 says, when they earn 42 full time positions, 42% would give then 17.83 in the category of SR and so on...

Now without any rounding in any way, the total is 42.5, but the total should match 42 in row 2.

The rules >> Within the pairs, SR should always be more than SS, and TS should always be more than MS. SMS is always going to be 1.

In the end, the SR in row 3 are the lower pay grades and row 7 is the highest, so the logic could also follow, the lower pay grades are always perferred, but keeping in my the pairing if at all possible.

Any thoughts?

[vandan_tanna]

As you know the problem is that you need to make SMS 1 (using round up) and that in turn screws up percentages that you calculate in column E.

Will re proportioning the percentage based on result from row #7 do the trick? See attached.

baseline1.xlsx

[jeffreybrown]

I knew I would leave something out.

Each row needs to be a whole number since these are full time earned positions. So example, row 3, 17.83 needs to be a whole number either 17 or 18 and so on...

[vandan_tanna]

How about this:

SMS:1
SR: Round up
SS: Round Down
MS: round Down
TS: remaining

Take a look. baseline1.xlsx
SR is always GT SS
TS is always GT MS

Am I close?

[jeffreybrown]

Even though TS is always GT MS, the boss will not like the up and down of the TS.

Maybe, column G, SR stays at 18 while TS is 4.

Column H, SR goes to 19 while TS stays at 4.

So in other words, all rows should be at least same number if not changing up by one.

[vandan_tanna]

I guess couple of ifs will take care of that, right?

See attached: baseline1.xlsx

You see where I am going with this.

I have given SR highest priority, then SS, then TS and then MS. Adjust to suite your need.

Works?

[jeffreybrown]

I think this is going to work or at least get me headed in the right direction. Thank you very much for your time Vandan

[vandan_tanna]

You are welcome. Goodluck and thanks for the rep.