excel spreadsheet

alsmith · External Since: Aug 08, 2007 Posts: 2

simple spreadsheet which allocates numbers of duties depending upon number of
employees, allocations always need rounding up or down. I can do this but
need to put remainder ( i.e between -0.49 and +0.49 into a new cell in order
to carry forward to the next round of alocations) any ideas?

pdberger · External Since: Mar 17, 2006 Posts: 89

alsmith --
I'm not sure what you're trying to accomplish, but howze about:

A B C
1 # =round(A1,0) =B1-A1

or you might want to make C1 = -(B1-A1) so that rounding up shows a negative
availability.

HTH

"alsmith" wrote:

> simple spreadsheet which allocates numbers of duties depending upon number of
> employees, allocations always need rounding up or down. I can do this but
> need to put remainder ( i.e between -0.49 and +0.49 into a new cell in order
> to carry forward to the next round of alocations) any ideas?

Sandy Mann · External Since: Jun 14, 2005 Posts: 1292

Like pdberger I'm not sure what it is that you want but assuming that you
have a number divided between a number of people as evenly as possible then
to divide 100 between 7 people:

Put 100 in A14 then in A15 the formula:

=ROUND(A14/(7-(COLUMN(A15)-COLUMN())),0)

cell B15 the formula:

=ROUND(($A$14-SUM($A$15:A15))/(7-(COLUMN()-COLUMN($A$15))),0)

then drag it along to G15

The result should be:

14, 14.14, 15, 14, 15, 14

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2.DeleteThis@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

"alsmith" <alsmith.DeleteThis@discussions.microsoft.com> wrote in message
news:C34F4C96-1A32-4F77-914A-37005F40CF71@microsoft.com...
> simple spreadsheet which allocates numbers of duties depending upon number
> of
> employees, allocations always need rounding up or down. I can do this but
> need to put remainder ( i.e between -0.49 and +0.49 into a new cell in
> order
> to carry forward to the next round of alocations) any ideas?
>

alsmith · External Since: Aug 08, 2007 Posts: 2

Hi sandy/pdberger,

I want to put the answer to a calculation into a cell, this figure will be
between 0 and 20 and will be similar to (i.e.) 14.45. I want the cell to
display 14 (rounded down) and display +0.45 in the cell next to it. Another
example is if the figure is 14.55, I want the cell to display 15 ( rounded up
) and the next cell to display minus 0.45 I allocate duties to areas and
need to be accurate with carry overs

"Sandy Mann" wrote:

> Like pdberger I'm not sure what it is that you want but assuming that you
> have a number divided between a number of people as evenly as possible then
> to divide 100 between 7 people:
>
> Put 100 in A14 then in A15 the formula:
>
> =ROUND(A14/(7-(COLUMN(A15)-COLUMN())),0)
>
> cell B15 the formula:
>
> =ROUND(($A$14-SUM($A$15:A15))/(7-(COLUMN()-COLUMN($A$15))),0)
>
> then drag it along to G15
>
> The result should be:
>
> 14, 14.14, 15, 14, 15, 14
>
>
> --
> HTH
>
> Sandy
> In Perth, the ancient capital of Scotland
> and the crowning place of kings
>
> sandymann2.TakeThisOut@mailinator.com
> Replace @mailinator.com with @tiscali.co.uk
>
>
> "alsmith" <alsmith.TakeThisOut@discussions.microsoft.com> wrote in message
> news:C34F4C96-1A32-4F77-914A-37005F40CF71@microsoft.com...
> > simple spreadsheet which allocates numbers of duties depending upon number
> > of
> > employees, allocations always need rounding up or down. I can do this but
> > need to put remainder ( i.e between -0.49 and +0.49 into a new cell in
> > order
> > to carry forward to the next round of alocations) any ideas?
> >
>
>
>

Sandy Mann · External Since: Jun 14, 2005 Posts: 1292

Isn't that exactly what pdberger's suggestion does?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2 RemoveThis @mailinator.com
Replace @mailinator.com with @tiscali.co.uk

"alsmith" <alsmith RemoveThis @discussions.microsoft.com> wrote in message
news:D7AF23AA-F40B-46FE-9AAC-EF9EE61AE73A@microsoft.com...
> Hi sandy/pdberger,
>
> I want to put the answer to a calculation into a cell, this figure will
> be
> between 0 and 20 and will be similar to (i.e.) 14.45. I want the cell
> to
> display 14 (rounded down) and display +0.45 in the cell next to it.
> Another
> example is if the figure is 14.55, I want the cell to display 15 ( rounded
> up
> ) and the next cell to display minus 0.45 I allocate duties to areas
> and
> need to be accurate with carry overs