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

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.

--
Andy.


"Wileycat" wrote in message
...
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

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.

"Andy B" wrote:

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.

--
Andy.


"Wileycat" wrote in message
...
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

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.

Wileycat wrote:

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

--

Dave Peterson

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.

"Dave Peterson" wrote:

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.

Wileycat wrote:

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

--

Dave Peterson

"Dave Peterson" wrote in message
...
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.


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