# Thread: Proper use of IF and OR (Excel xp)

1. ## Proper use of IF and OR (Excel xp)

I'm trying to put together a formula that will evaluate E381. If it is less than 30 i want to make E378 a 2. If it is less than 50 i want to make E378 a --. The problem is that E381 can have an * or ** at the end of it.

So if E381 is 40*, i want e378 to be a --. If E381 is a 29** i want e378 to be a 2. I put together a formula but it's not seeing 40* as a number. I've tried (value(E381)) thinking that E381 would be recognized as a number but i get an error.

What is the proper order to do this formula. Thank you

2. ## Re: Proper use of IF and OR (Excel xp)

=SUBSTITUTE(E381,"*","")

will return a string that will be evaluated as a number for further calcs. What do you want to do if the value of E381 is >=50?

3. ## Re: Proper use of IF and OR (Excel xp)

The following formula should do exactly what you asked for.

=IF(VALUE(REPLACE(\$E\$381,3,2," "))<30,"2",IF(VALUE(REPLACE(\$E\$381,3,2," "))<50,"--","Not sure"))

Chuck

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•