Thread: Mid + Search Calculation Problems

So I wasn't really sure about what to title this thread, but here is my problem:


Upon importing data from a website, I get a cell (C10) that looks like this:

+4 (-107)


I then use this function to isolate the number in parentheses

=MID(C10,(SEARCH(" ",C10)+2),4)


This is what I get; so far so good:

-107


The next cell uses the following function to yield what should be a number between 0 and 1. The IF function uses a different equation depending on whether the value calculated above (D3) is positive or negative and results in .5 if D3=0:

=IF(D3>0,1/((D3/100)+1),IF(D3<0,ABS(D3/100)/(ABS(D3/100)+1),0.5))


If I just plug -107 into the cell manually I get 0.516908213


HOWEVER-- With the Mid and Search function instead I get this value: -14.28571429, however as explained above this is not the correct value.


Can anyone please explain why this error is occurring and how to fix it; OR a better way to accomplish what I am after?



Thanks in advance,

Kevin

Awesome, all of these tips worked.

I didn't think to turn the text into a number; Also good call on the simplification. It's been a long while since taking a math course.

Thanks