Need help with formula, please.

E

Emilio

Hi,

I am a newbie to excel formulas.
I have this: =IF(D12<10,10,D12)
This works ok except that if D12=0 it shows 10

Also I need that if D12= 12.3 the result shows as 12.5
(rounding to .5)
and if D12= 12.6 the result will show as 13

I hope is not confusing.
TIA,
Emilio
 
P

Pete

I think your arrow is pointing the wrong way--zero is
less than ten so it shows ten--need to point it to the
right since you are testing for greater than.

Just as an aside, sometimes I've run into tests like this
failing because the character of the number was in the
tested cell rather than the number--in other words it
looks like a number but when you go to the cell, there is
a little quote mark to the left of the number. In this
case reformatting is needed.
 
G

Guest

Thanks Frank for your response

I still get 10 if D12=0
and if D12= 15.67 I get 15.5 (but it should be 16)
Basically anything below ".5" should be ".5"
(example= 15.23 should be 15.5)
and anything above ".5" should go to next number
(example= 15.67 should be 16)

Thanks again
Emilio
 
E

Emilio

Forgot to put my name

Thanks Frank for your response

I still get 10 if D12=0
and if D12= 15.67 I get 15.5 (but it should be 16)
Basically anything below ".5" should be ".5"
(example= 15.23 should be 15.5)
and anything above ".5" should go to next number
(example= 15.67 should be 16)

Thanks again
Emilio
 
F

Frank Kabel

Hi
what do you want for values <10? Do you mean:
=IF(D12<10,D12,CEILING(D12,0.5))
 
E

Emilio

Thanks a lot, I am getting closer.
Everything works great except when D12 is less than 10,
say is 3.34 the answer should be 10
(anything less than 10 should be 10)

THANKS A LOT!

Emilio
 
F

Frank Kabel

Hi
now you're confusing me. In the previous post you said that you have an
issue that if D12=0 the formula returns 10. Now you're asking for a 10
if D12 is smaller than 10?

So then try:
=MAX(10,CEILING(D12,0.5))
 
E

Emilio

Hi Frank,

Sorry to get you confused.
"(anything less than 10 should be 10)"
Except "0"
In that case the answer should be "0"

I hope this clarifies it.

Thanks again,
Emilio
 
E

Emilio

Thanks again Frank,but I manage to figure it out myself

=IF(D12=0,0,IF(D12<10,10,IF(D12>10,D12,CEILING(D12,0.5))))

also I needed another formula which thru all these years
I have been entering manually because I didn't think it
could be done, too complex

=IF(D12<4,4,IF(D12<12,CEILING(D12,1),IF(D12>12,CEILING
(D12,0.5),IF(D12=12,(D12),))))

if D12 less than 4=4
and if D12 more than 12 (12.5, 13, 13.5,......)

ALL THANKS TO YOU!

Cheers,
Emilio
 
R

Rob arelhATnetvigator.com

i gather you want answer to be 0 if d12 =0 try this
=IF(D12<10,IF(D12=0,0,10),ceiling(D12,0.5))
where ceiling forces D12 to the nearest 0.5
regds Rob
 
R

rob arelhATnetvigator.com

sorry for the xtra reply ceiling forces D12 to the next
0.5 not nearest
regds rob
 
A

Aladin Akyurek

[...]
also I needed another formula which thru all these years
I have been entering manually because I didn't think it
could be done, too complex

=IF(D12<4,4,IF(D12<12,CEILING(D12,1),IF(D12>12,CEILING
(D12,0.5),IF(D12=12,(D12),))))

if D12 less than 4=4
and if D12 more than 12 (12.5, 13, 13.5,......)
Click to expand...
[...]

Looks like this also can be shortened to:

=CEILING(MAX(D12,4),1-(D12>12)*0.5)
 
Top