extracting individual numbers from "ranges"

  • Thread starter Thread starter Manfred
  • Start date Start date
M

Manfred

I'm trying to get EXCEL to extract stock prices quoted in individual cells
as 52-week ranges --e.g. 11.76-19.90-- and do the following calculation:

(higher price - lower price)/lower price

Obviously, this a problem because each cell appears to handle the range as
text or something. Is there are way to get EXCEL to handle the "text" (or
however else it is interpreting what's in the cell) as individual numbers?

Great thanks for any help.
 
Hi
do your entries always look like
lower_number-higher_number
if yes you may try the following formula in the adjacent row (lets
assume this is column B). Enter in B1
=(VALUE(MID(A1,FIND("-",A1)+1,20))-VALUE(LEFT(A1,FIND("-",A1)-1)))/VALU
E(LEFT(A1,FIND("-",A1)-1))
copy this down
 
Thank you for your response, but it did not work. In answer to your
question, yes the numbers reported are always lower number - higher number
as follows:

11.76 - 19.90


in column H. Thus I copied the formula you offered as:

=(VALUE(MID(H1,FIND("-",H1)+1,20))-VALUE(LEFT(H1,FIND("-",H1)-1)))/VALU(LEFT
(H1,FIND("-",H1)-1))

in column L. But when I attempt this, the result is a "#NAME?"

Thank you again for any help. Any assistance would be appreciated.
 
Hi
there is a small typo in the formula I gave you (VALU instead of
VALUE). Try
=(VALUE(MID(H1,FIND("-",H1)+1,20))-VALUE(LEFT(H1,FIND("-",H1)-1)))/VALU
E(LEFT(H1,FIND("-",H1)-1))

or if the above does not work (as there seems to be some spaces) try
=(VALUE(TRIM(MID(H1,FIND("-",H1)+1,20)))-VALUE(TRIM(LEFT(H1,FIND("-",H1
)-1))))/VALUE(TRIM(LEFT(H1,FIND("-",H1)-1)))
 
Another way might be to select your (single column) range of cells and make sure
you have two available columns to the right.

Then do a data|text to columns. Specify delimited and that the delimiter is a
hyphen.

Then you'll have each value in its own cell.

and you can just do regular (easy!) formulas:

=abs(c2-d2)/min(c2,d2)
 
Excel can be very forgiving - and clever - about formats.

Try multiplying the "text" value by 1 and see if it becomes
manageable number.

Failing that, try converting each entry using the formula
=value("entry")

Al
 
Back
Top