Numerical wildcard in formula

  • Thread starter Thread starter Scott
  • Start date Start date
S

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
 
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.
 
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.
 
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).
 
Robert Nguyen said:
use =ROUND(B2, -0.5)
Click to expand...

=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)) .
 
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.
Click to expand...

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

=ROUND(B2-0.005,0)
 
Back
Top