Numerical wildcard in formula

[Scott]

Does anyone know a way to put a wildcard into a series of
numbers? What I'm trying to do is use an IF statement to
check the value of a cell:

"if B2<=?.5, rounddown(B2),rounddown(B2))"

Excel doesn't like any of the wildcards (?, *, or ~) when
used in this formula.

Hopefully someone has a solution?!?

Thanks in advance,

Scott

 

[Dave R.]

you can use MOD(B2,1) to get the decimal place only, if thats what you're
asking. You also ask it to round down both times.

 

[Robert Nguyen]

use =ROUND(B2, -0.5)

 

[Guest]

Sorry, misstated the equation. Should've read: "=if
((b2<=?.5,rounddown(b2,roundup(b2)".

What I'm trying to do is set the breakpoint for rounding
so that at .5 or below it rounds down and at .51 or higher
it rounds up.

 

[Dave R.]

Did you read any of the responses? As you've re-written it in words, you
will have a gap between 5.000000000001 and .509999999999999

I'd stick with the ROUND function, no need to evaluate the number on your
own, especially since you're using round calls later in the formula (but
those are not any Im familiar with).

 

[Harlan Grove]

use =ROUND(B2, -0.5)

=ROUND(5.5,-0.5) gives 6, as does =ROUND(5.5,-0.9999) . If you pass ROUND
a negative 2nd argument, ROUND truncates it. So =ROUND(x,y) always gives
the same result as =ROUND(x,TRUNC(y)) .

 

[Harlan Grove]

Sorry, misstated the equation. Should've read: "=if
((b2<=?.5,rounddown(b2,roundup(b2)".

What I'm trying to do is set the breakpoint for rounding
so that at .5 or below it rounds down and at .51 or higher
it rounds up.

Alternatives. If your values never seem to include more than two decimal
places, consider

=ROUND(B2-0.005,0)