Excel 2007 : Mid + Search Calculation Problems

[CUstudentDoc]

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

[NBVC]

try changing the Mid formula to:

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

to coerce the extracted text string to an actual number.

[shg]

Your first formula returns a text string; change it to

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

You can simplify the other formula to

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

[CUstudentDoc]

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