extracting individual numbers from "ranges"

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.


0
Manfred
2/21/2004 8:49:01 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
730 Views

Similar Articles

[PageSpeed] 36

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

--
Regards
Frank Kabel
Frankfurt, Germany

Manfred wrote:
> 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.

0
frank.kabel (11126)
2/21/2004 9:18:37 PM
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.



0
Manfred
2/21/2004 9:34:14 PM
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)))


--
Regards
Frank Kabel
Frankfurt, Germany

Manfred wrote:
> 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.

0
frank.kabel (11126)
2/21/2004 9:40:31 PM
That's amazing (the second formula works).  Great thanks for your efforts.


"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:OOWSWNM%23DHA.220@TK2MSFTNGP09.phx.gbl...
> 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)))
>
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
> Manfred wrote:
> > 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.
>


0
Manfred
2/21/2004 9:48:30 PM
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)



Manfred wrote:
> 
> 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.

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
2/22/2004 12:45:13 AM
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

--
Message posted from http://www.ExcelForum.com

0
2/23/2004 12:17:56 AM
Reply:

Similar Artilces:

extracting individual numbers from "ranges"
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 assum...

How to divide a data series into different ranges?
In a report i need to divide my chart by certain ranges...suppose if i have a datea series for reports for every week.I want to display the reports older than 3 months,older than 2 months etc in a single data series by dividing the data sereis...Please help It's much easier to use multiple series. If it's anything but an XY chart, use separate worksheet columns that line up with the X values, with values where the series appears and blanks where other series appear. It's pretty much as described in my Conditional Charts page: http://peltiertech.com/Excel/Charts/ConditionalC...

Creating a chart with varying data value ranges
I have a chart that I want to show home price data with. The chart will have data that has very significantly varying data values. For example, one line graph will be 50-100, one will be 400,000 to 500,000, one will be 90% to 110%. I would like to show these line graphs on one chart. How to I make a chart with differing ranges on the x (vertical) axis? Take a bit of seutp work, but the end result here is impressive: http://peltiertech.com/Excel/ChartsHowTo/PanelUnevenScales.html BTW, the vertical axis is the y-axis. -- Best Regards, Luke M "Anthony Blackburn&quo...

Can Publisher print divided page ranges?
With Ms-Word, you can specify several ranges of pages, using commas as delimiters, such as: 1-4,9,10-12 This doesn't seem possible with Ms-Publisher 2003, but maybe I'm missing something. It only seems possible to indicate one range, such as 3-10 Is there a way to have just certain pages print out such that several ranges are involved? I'm trying to accomplish the equivalent of Ms-Word's "MasterDocument" by having a 40 page master, from which I can specify a 12 page version, a 28 page version, etc. The twelve page version might be something like: 1-3,8-9,10,14,36-4...

Pivot Table from Multiple Ranges
When creating the Pivot Table from Multiple Ranges, I always get the same 4 field result as Row, Column, Page, despite that I have from the origins at least 10 names for the columns on those ranges. If I do it from one source only I get those 10 names to make the design. What I am doing wrong? If you select 5 different coloumns from the sheet then excel does no let you make the table. For this the best thing to do is to take th data for pivot in a separate sheet. R u trying to select data from more than one sheet -- Message posted from http://www.ExcelForum.com If you select 5 dif...

Hyperlinks and Named Ranges
I have a formula that currently checks a state abbreviation against a table. If a certain value is retreived, a hyperlink is shown. How can I have the hyperlink go to a named location within a separate file, based on the state abbreviation used in the VLOOKUP command?? =IF(VLOOKUP(E53,EnergyCalcs,2,FALSE)="See JURISDICTIONS",HYPERLINK("C:\EnergyCodes\FullJuris.xls","See Energy Codes"),VLOOKUP(E53,EnergyCalcs,2,FALSE)) Thanks!! If you want the file to open with the cursor at cell F10 in sheet "Energy", for example, you can amend your formula as ...

How can I create a formla using date ranges
How can I create a formula that will do this: = IF cell A2 ="m" and if cell D2 is between 5/19/06 and 6/20/06 than cell E2 ="June" + if A2 = "m" and D2 is between 6/21/06 and 7/18/06 than E2 = "July" and so on? This is the formula I used: =AND(A2="m")*IF(D2=38854:38888,"June")+AND(A2="m")*IF(D2=38889:38916,"July") I either get an #Value!, or some other error. Please help! If June / July is your only concern, then: =if( A2 <> "m", ___whatever happens if A2 is not m____, if( and(D2>3885...

Poor Workbook Performance due to Named Ranges
Hello, I defined some named ranges in one of my workbooks. I created another worsheet that uses references to these named ranges to generate counts using the SUMPRODUCT function. The result has been unacceptably slow performance in this workbook when using the autofilter to filter by a particular value in a particular column. I have the same problem when I manually enter a value or paste a value into a cell and move off the cell. The application says, "calculating" for about 4-5 minutes. Is there any way to improve this performance to an acceptable levesl? Thank...

VBA and Charting Named Ranges?
Our developers write out a .csv file, with row 1 being the strings for the column titles. I can read these .csv files into a worksheet, so that column 1, for example is titled "LogTime", and Column 10 is TimeOnDisk. I can create a named range for each that covers just the cells that are populated e.g.($A$2:$A$2280). I can use the Names drop-down to verify the named ranges are correct. I have to do this, because in the next release, the developers may add or delete columns of data, but have promised to keep the column names. And the number of rows varies by the number of days worth of...

dynamic setting of data ranges
I have a set of data, 1 x range and four y ranges, the data is end of week totals, i only want to see the last 13 weeks of data on a graph, how can i dynamically set the data input ranges for each graph so that they only plot the last 13 data points????? the macro function doesnt seem to like relative references for the chart data sections thanks pat Hi, Jon's example is for last 12 months but you should get the idea. http://peltiertech.com/Excel/Charts/DynamicLast12.html Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Patrick" <Patric...

compare two columns with different ranges in two worksheets
I need to compare two columns of data in two different worksheets and display a third one. Here it is an example: -(worksheet1!A1:A10), (worksheet1!B1:B10) and (whorksheet2!C1:C25) -this is my query, if C5 is already in (A1:A10) I want to display B5 in worksheet2!D5 I think it is tricky because you need to identity which row in the A1:A10 is equal to C5 to display B5 and the range are different. you could save my day chris90 In worksheet2!D1: =if(isna(vlookup(C1, worksheet1!$A$1:$B$10, 2, 0)), "", vlookup(C1, worksheet1!$A$1:$B$10, 2, 0)) HTH Kostis Vezerides brilliant, ma...

Printing data ranges from multiple columns to a single column
I'm trying to create a column (d) in excel which will combine th outputs from 3 other cells (a) (b) and (c). I need column (d) to look up the unspecified range of information fro column (a), followed immediatedly by the unspecified range o information in column (b) again, followed by column (c). Column (d now will contain the list abc: a= 1 2 3 b= w x y z c= * + therefore: d= 1 2 3 w x y z * + I'm sure it's not that hard to do, I'm just very new to excel. Any hel would be really appreciated. : -- BushMonste ---------------------------------------------------------------...

Printing only selected ranges in "notes"
Can a group or selection of notes e.g., one month at a time, be printed? As I have many, multiple entries in Outlook "notes" on a daily basis, it seems I can only print my entire "notes" folder. Please advise... -- Rick ...

Named ranges & protect Print_area? & apply changes across WS's?
I have 13 WS with same layout, is there anyway I can use grouping to select all 13 WS, then select a cell range and apply a named range to these cells across every WS? Basically while im setting up this WB any changes I make to one of these 13 WS needs to be applied to the others, the only differences on each WS are contained on the first few rows of each page (4 pages per WS) (13 WS = (2 WS per day Mon-Sat) + (1 for Sunday)) example: each WS is setup as A1:AA236, then split in to 4 pages, Page1=A1:AA59 Page2=A60:AA118 Page3=A119:AA177 Page4=A178:AA236 these pages are the named ranges I wi...

Comparing date ranges
I'm trying take a date value that entered through the calendar contro and use it to determine which pay period that date belongs to. But even though the calendar1.value is crossing properly, the Select Cas method is picking the first date range every time. Below is a sampl of the code I'm using, I'm not sure what other details I need t include...this is my first time to post. But, any help is VER appreciated. Private Sub CmdSubmit_Click() Dim CalValue as Date CalValue = Calendar1.Value 'Determine the Pay Period CalValue = Format(Date, "mm/dd/yyyy") Select Case Cal...

Sum If
I have the following data: Col D Col E Col I Part Numbers Part Unit Cost Extended Value Question 1: How do I sum the number of unique parts in the following ranges (<=$25, $26-50, $51-100, $101-$500, $501-1,000, >$1,001)? I was able to do it by part, but not unique part. I am trying to avoid duplicates. Question 2: How do I sum the dollar value associated with those ranges? So I would be able to say I have 10 unique parts in the <$25 category, with a total extended value of $XXX (this should include the duplicate parts si...

Prevent excel from changing numeric data ranges into dates?
I want to prevent excel from changing my numeric data into dates (ie. 10-12, into 12-Oct). I want to import specific data ranges, not a date values. Whenever I paste or type a possible date, like 10-12, excel assumes I am writing Oct. 12, 2004. Then, Excel formats the cell into a Custom d-mmm format (see scrn shot @ www.vd4.org/excel_issue.gif). If I change the Custom format to General, excel converts the date into the DATE'S value (ie. 12-Oct, into 38272; which I think is the # of days since 1900?). How can I *prevent* excel from changing my data ranges into dates (ie keep 10-12 ...

Better control of axis ranges
I would like to make x-y charts in Excel 2000 with dynamic source data. That is, the user can select what data set he wants to display. To do this, I need better control of how the axis limits are defined. This can be achieved in one of two ways: 1. Use numbers from cells to define xmin, xmax, ymin, ymax. 2. Define in some way how auto-scaling decides what the min and max values are (I often find that the data gets "scrunched" into a small portion of the plot area). Method 1 is preferred, but 2 may also work. I would prefer to do this without writing a VBA macro, if possib...

Right-Click menu and sum of ranges
Hi everyone, I know that If I select a range in a spreasheet (continuous, or b Ctrl+Clicking a few cells) I can see the sum of the values in the rang on the Status bar, but would it be possible to have this info somewher else? This is basically the only reason I have my Status bar visible, and a I like my Excel as unclutered as possible, I would like to get rid o it :) Ideally, I would love to be able to view this on the Right-Click menu but I dont think it is possible without somebody writing an add-in Does one like this exist? Thanks for your help, Mathia --------------------------------...

Grouping number ranges
I have a database of products that we sell in a shop. Each product has a unique four digit code known as a PLU. The PLU is assigned according to what sort of product it is. For example Poultry Feed is assigned a code between 5000 and 5079, Game Feed between 5080 and 5099 and so on. What I want to do is pull a report off to use as a price list to hand out to all our salesmen. So, for example all 5000 codes are for Merchant Feed - so I want to pull one price list off for this category. However within the category I would like to have a list of products listed under their group headi...

How can I stop Excel from turning the number ranges I enter (eg 1.
I've been trying to put together a spreadsheet of addresses, so I need to be able to include addresses that include number ranges - eg. 11-12 High Street or 14-16 Main Road. The problem is that in the column in which I enter the number part of the address, these kind of addresses keep getting turned into dates - so 11-14 becomes Nov 14. How can I get these to display as I've originally typed them?? Help would be much appreciated! Hi! You can either preformat the cells as TEXT or precede the entry with an apostrophe, like this: '11-14. The apostrophe will not be displayed in...

Looking up across date ranges in Access
I am mapping financial data from one system to another and need to look up in a query a dept based on a date range. tblDeptMap reads Dept FromDate To Date NewDept 1121 01/01/2009 31/03/2009 1125 1121 01/04/2009 31/05/2009 2332 Financial Data Table Looks Like this: Dept Month Year Amount 1121 01 2009 32 1121 02 2009 41 1121 03 2009 50 1121 04 2009 52 1121 05 2009 25 I need a query that will read: Dept Month Year Amount FinDate NewDept 1121 01 ...

help with ranges
I need a to know a formula for a range that verify's another field has certain varaible or # the goes to another field in the same record an copies the next 4 cells down and then pastes the the copie information in the next column but same record. transposes the dat for the 4 cells, so all data on the same record -- Message posted from http://www.ExcelForum.com It's a bit hard to figure out. Could you send a sample sheet of "Was" and "Now" to excelmarksway@yahoo.com.au and I will write a macro or userform utility for you. You can verify my ability at my use...

Charting Ranges
I have some ranges I want to chart - There are only six values - They consist low ranges high ranges. The raw data is as follows: Room Temperature 1: Low Range: 32 High Range: 256 Room Temperature 2: Low Range: 2 High Range: 64 Room Temperature 3: Low Range: 0 High Range: 2 How can I chart this on one chart - I want the scale to be from 0 - 256 and just have 3 bars to show the different ranges. Every time I try to chart it using different charts, the lower values don't show much of a bar because the Room temperature 1 range is so high. How would I arrange this data? What cha...

Filltering data between two date ranges
Hi, wonder if anyone can help me - I have a list containing peoples birthdays. I wish to be able to enter a date range i.e. today and 2 weeks into the future. I then wish for the list to be filtered showing only those birthdays within the date range. The date range could span across 2 different months. I'm not particularly skilled in using features such as VBA etc, so if there is a simple solution it would be much appreciated. Many thanks for any help. Hi Dave! If you enter the birthdays as Month/Day this is somewhat easy. If you enter the birthdays as Month/Day/Year it is much m...