How to balance several cells to a predetermined value

W

Wileycat

I am using a spreadsheet that contains a list of hours worked. I need to
apportion the hours worked to a restricted number of hours. For example:
I have 2, 5, 6 & 3. I need the values to be apportioned to a ten hour period
rounded to two decimal places. When I do this I get values of 1.25, 3.13,
3.75 & 1.88 a total of 10.01

How can I guaratee my adjusted values come back to the predetermined value, 10
 
G

Guest

Hi
It depends how accurate you need to be. The easiest way would be to
calculate 3 of your values and then get the fourth value by taking the total
of those 3 away from 10.
Hope this helps.
 
W

Wileycat

This will work but is a weak solution. I could have varying number of numbers
to analyse, anything from one to 100. The report is then printed and keyed
into a different software application. It needs to be robust and idiot proof.
 
D

Dave Peterson

I did what Andy B suggested.

I put 2 5 6 3 in A1:D1
I put this formula in B1: =ROUND(10*(A1/SUM($A$1:$D$1)),2)
and dragged to C2

Then I used this formula in D2: =10-SUM(A2:C2)

But that means that the last number will be the one affected.
 
W

Wileycat

Doesn't work for what I need. I have a spreadsheet (timecard) that has over
one hundred lines. An individual enters the number of hours worked for each
project in a week, could be upto 100, could be as few as one. An individual
may enter all projects he works on as a template document. If he doesn't work
on the project the last roundinng function 10-SUM(A2:C100) would place the
rounding variance on a blank project. I must never have a negative rounding
figure in a blank line.
 
F

Fredrik Wahlgren

Dave Peterson said:
I did what Andy B suggested.

I put 2 5 6 3 in A1:D1
I put this formula in B1: =ROUND(10*(A1/SUM($A$1:$D$1)),2)
and dragged to C2

Then I used this formula in D2: =10-SUM(A2:C2)

But that means that the last number will be the one affected.
Click to expand...

Why didn't you use =ROUND(10*(D1/SUM($A$1:$D$1)),2) in C2?
I put the values vertically and made the necessary change to the range. I
got these values

1.25
3.13
3.75
1.88

/Fredrik
 
Top