4 is more than 5??

[b0unce]

Hello,

I have this strange issue when I try to round up all the figures 1-4 to 5. Let's say I have a value of 6.74 in cell A1 and want to round it up to 6.75. I use a function in A2:

=if(and(right(A1;1)>=1;right(A1;1)<5);roundup(A1;2)-0.05;A1)

But the strange thing is that "right(A1;2)<5" is evaluated as false. I tried this function using alone, but it just gives False all the time. Once I change "<" to ">" I get true. Though 4 is less than 5.
Is this an Excel bug, or I am using this function wrong? A1 is a calculated value, rounded to 2 figures. But even if I use fixed value it does not work either.
I am using Excel 2010.

Maybe anyone has other ideas, how can I roundup all 0.01 - 0.04 value to 0.05? Please share.
Thank you in advance!

 

[CROY1985]

Right is a text function, that could be messing with the results here:

try

=if(and(right(A1;1)*1>=1;right(A1;1)*1<5);roundup(A1;2)*1-0.05;A1)

 

[Rick Rothstein]

Hello,

I have this strange issue when I try to round up all the figures 1-4 to 5. Let's say I have a value of 6.74 in cell A1 and want to round it up to 6.75. I use a function in A2:

But the strange thing is that "right(A1;2)<5" is evaluated as false. I tried this function using alone, but it just gives False all the time. Once I change "<" to ">" I get true. Though 4 is less than 5.
Is this an Excel bug, or I am using this function wrong? A1 is a calculated value, rounded to 2 figures. But even if I use fixed value it does not work either.

Text values are always larger than numeric values... the result of the RIGHT function is a text string, 5 is a number. If you add 0 to a text string that looks like a number, Excel will treat it as a number. So, try your comparison this way...

(0+RIGHT(A1;2))<5

 

[hippiehacker]

if you use right(A1;2)<5 then 2 numbers from right will be used in your example the 74

but you can use

=CEILING(A1;0,05)

 

[steve the fish]

Have a look at using this formula. It may help with what you are trying to do:

=CEILING(A1,0.05)

 

[b0unce]

Hey guys,

thank you very much for help!
I also figured out, that I could just use VALUE function to convert the string I received from using RIGHT to number.
CEILING is also a great solution! Thanks for sharing your knowledge!