Sum and rounding

[rob1]

Hi everybody

I need to build a certain number of buildings. For this example, 365 over a 4 year period.

I know the total required, and I need to divide it into years. For each year I can divide 365 by 4 and I get 91.25.

But I need each year to show an integer, i.e. 91 or 92 - so I'm actually building 91 in three years and 92 in one, to reach my total of 365.

Is there a way to get Excel to do this automatically?

Rob

[Fotis1991]

Hi and welcome to the forum.

Try with round....

=ROUND(365/4;0)

=ROUNDUP(365/4;0)

[rob1]

Thanks, Fotis. I can't get that to work though. It just tells me the formula contains an error. And looking through help, I can't see a construction of the round function other than number; num_digit.

The trouble is that this is something I need to be able to do repeatedly. Whatever the total number is, I need the sums in each of the four years to be integers, and for the total of the four years to match the original.

Rob

[Fotis1991]

Must be that you use comma, not semi-colons in formulae..!

=ROUND(365/4,0)

=ROUNDUP(365/4,0)

But i can not suggest something more, i think that we need a small sample workbook.

[rob1]

Hi Fotis

Thank you, that makes the formula work, but it doesn't give me what I need.

I've attached a sample that might show you better.

Rob

[Fotis1991]

An unusual way(but gives the corect result) is this.

In C10 , D10 , E10, put this formula.

=ROUND($D$5/4,0)

In F10, this formula.

=D5-SUM(C11:E11)

Is this, works for you?

[rob1]

Yes, that works. Thank you, Fotis, you are a genius.

Rob

PS you are right to be proud to be a Greek :-)

[Fotis1991]

Thank you very match Rob.

Specially for your second paragraph

[Marcol]

You could extend that to make it a little more dynamic.
Add or Delete a year in D9:M9, drag the formula further than M9 if required.
See this workbook.