Rounding question

Hi all,

I have a cell that you enter a volume size, and depending on what is entered, a calculation is performed. In order to try an keep the calculation to a simple thing, I need to round the volume size first. I need it rounded to either .0 or .05 ...

For example. Volume Size is 2.43, this requires to be rounded up to 2.5 for a maximum size and rounded down to 2.0 for a minimum size.
The volume size can vary from 0.00 upto 3.00, a max and min require to be calculated for this with a 0.05 tolerance.

Whats my best options of doing this?

Any help greatly appreciated - its doing my head in!!!

Peter.

Try the following...

=CEILING(A1,0.5)

AND

=FLOOR(A1,0.5)

Hope this helps!

Originally Posted by PeterG

Hi all,

I have a cell that you enter a volume size, and depending on what is entered, a calculation is performed. In order to try an keep the calculation to a simple thing, I need to round the volume size first. I need it rounded to either .0 or .05 ...

For example. Volume Size is 2.43, this requires to be rounded up to 2.5 for a maximum size and rounded down to 2.0 for a minimum size.
The volume size can vary from 0.00 upto 3.00, a max and min require to be calculated for this with a 0.05 tolerance.

Whats my best options of doing this?

Any help greatly appreciated - its doing my head in!!!

Peter.

Thanks Domenic, thats just what i need,

will that also work for slightly larger limits, the other part of the calculation uses resistance. The range is 0-50, 50-70, 70-90, 90-110 etc upto 510.

for instance - resistance value is 140.31, it should return a floor of 130 and a ceiling of 150, similarly 168.3 would return floor of 150, and ceiling of 170 etc.

Peter.

There may be a better way, but try the following...

1) Set up a table, let's say in Columns A and B, as follows:

0 50
50 70
70 90
90 110
110 130
130 150
150 170
170 190
190 210
210 230
230 250
250 270
270 290
290 310
310 330
330 350
350 370
370 390
390 410
410 430
430 450
450 470
470 490
490 510

Then, try the following formulas...

=LOOKUP(C1,A1:A24)

AND

=LOOKUP(C1,A1:B24)

...where C1 contains your resistance value.

Hope this helps!

Originally Posted by PeterG

Thanks Domenic, thats just what i need,

will that also work for slightly larger limits, the other part of the calculation uses resistance. The range is 0-50, 50-70, 70-90, 90-110 etc upto 510.

for instance - resistance value is 140.31, it should return a floor of 130 and a ceiling of 150, similarly 168.3 would return floor of 150, and ceiling of 170 etc.

Peter.

Domenic - great minds think a like, this is the solution that put together yesterday, after having posted my question. It works a treat.

Many thanks for your kind assistance.

Peter.