How to balance several cells to a predetermined value

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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
news:[email protected]...
> 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