ceiling and floor functions

I have a range of data that includes different types of data (numbers, errors, and strings) that I want to first of all find the max and min of (which thanks to a helpful user on this board I was able to do). All of my numbers are between 0 and 1. Then I want to get the ceiling of the max and the floor of the min both rounded off to the nearest 1/20th

I can do this with the worksheet function ceiling(max,.05) and floor(min,.05) but when I do it in VBA my numbers always come out as 0 and 1.

Here is what I've done... the code seems really straightforward but it isn't working.


Data:
A B
0.256 0.789
0.896 0.001
0.45 0.637
0.225 0.016
0.568 0.315

I get a max of .896 and a min of .001.

If I use:

RMax = Ceiling(MaxValue, 0.05)
RMin=Floor(MinValue,0.05)

where MaxValue = 0.896 and MinValue = 0.001 then I get RMax = 1 and RMin = 0. What gives?

Any ideas?

Thanks.

Hello JFamilo,

Check that you have explicitly declared RMin and RMax as either Variant or Single types. If you assign a floating point number to an integer, it rounds it.

Sincerely,
Leith Ross

Thank you so much. This worked great. I had RMin and Rmax declared as Long variables. I can't believe I missed something like that! Thanks.