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.
do your entries always look like
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:
BTW, the vertical axis is the y-axis.
"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:
This doesn't seem possible with Ms-Publisher 2003, but maybe I'm
missing something. It only seems possible to indicate one range, such
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
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 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:
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
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
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
Jon's example is for last 12 months but you should get the idea.
Andy Pope, Microsoft MVP - Excel
"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
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
=if(isna(vlookup(C1, worksheet1!$A$1:$B$10, 2, 0)), "", vlookup(C1,
worksheet1!$A$1:$B$10, 2, 0))
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= * +
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. :
---------------------------------------------------------------...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...
...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))
each WS is setup as A1:AA236, then split in to 4 pages,
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
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
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
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
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
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
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,
--------------------------------...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!
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.
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 email@example.com 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
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.
If you enter the birthdays as Month/Day this is somewhat
easy. If you enter the birthdays as Month/Day/Year it is