Urgent - Help making totals add up after rounding to whole numbers

[Nick24]

Morning guys,

I have a column of values containing 3050 rows. Each row has a value from 0 - 50. The majority of cells are 1's, 2's etc.

I need to create two new columns to the left, one of which has 50% of the value, and the next having 30% of the value.

The trouble is, I've done previous analysis and the totals MUST add up to totals I've previously specified (the total is 3550, 50% is 1775 and 30% is 1183. They must also be whole numbers.

So far i've gotten about 200 off the 50%, and 50 off of the 30%.

I've tried stating rules with an IF function for the 50% of: =IF($O4=1,1,IF($O4=2,1,IF($O4=3,1,IF($O4=4,2,IF($O4=5,2,IF($O4=6,3,IF($O4=7,3,IF($O4=8,4,IF($O4=9,4,ROUND($O4*$Q$3,0)))))))))) but this is whats still putting me over 200 off.

Can anyone help me?!

Thanks,

 

[Special-K99]

How do you get 1183 ?
30% of 3550 is 1065.

Why are you rounding? If you start rounding you're "creating/losing" numbers that didn't originally exist hence the totals may not add up.

You're probably better off posting a spreadsheet.

Attach a sample spreadsheet with expected results, remove any sensitive data.
You cant attach files on this forum.
Upload the file to an online storage site then place a link to it on this forum.