Joint CEILING and FLOOR for several cells

[ogygiasylph]

Hello once again!

I’m looking for a VBA code or Excel formula that combined both CEILING and FLOOR functions. My calculations require that I add, divide, or multiply a set of numbers that must always add up to 100%. Unfortunately, due to the rounding that occurs at other steps in the calculations, the summing and averageing does not always add up to 100%, often ending up at 99% or 101%.

Could someone help me ensure that the values in, say, 5 different cells, when calculated from other cells, systematically add up to 100%? Like I said, a macro or regular function to do this would suit me perfectly.

Thanks for the help!

All the best,

Calypso

[sweep]

Hi,

You could avoid using the FLOOR and CEILING rounding functions at all, and simply change the number format of the cells to specific decimal places?

[ogygiasylph]

But that still would automatically round it up or down to the nearest decimal, wouldn't it?

[sweep]

Only if you're only talking about the final calculations. Remove any rounding from your earlier calculations and you should be OK. (see sheet 1 of attachment)

[sweep]

Failing that, you could simply calculate out the error and maintain the ratio:

[William Okumu]

Please insert a sample of your worksheet