How To Exclude Certain Data Points

Jan 22, 2007

I'm running a query of 30 and 60 days worth of data but I don't want certain dates to be included in the queries. How do I exclude certain dates and at the same time pull in day 31 to include this in my 30 day high or average etc.?

For example:

1/19/2007 4.7
1/18/2007 4.5
1/17/2007 3.8
1/16/2007 4.9
1/15/2007 3.75
1/12/2007 3.75

I want to exclude 1/15 because it is a holiday and it just repeats the data from 1/12. So I want it to pull in the data for 1/12 if I were doing a 5 day query. As I said before I need to do a MAX, MIN, AVERAGE and STDEV of 30 and 60 days but my formulas are skewed because of the holidays.



ADVERTISEMENT

Points Property Select Multiple Points

May 2, 2007

Is there a way of amending the following line of code so that it selects multiple data points in a data series in a chart (e.g points 14,15,16)?

ActiveChart.SeriesCollection(2).Points(14).Select

View 2 Replies View Related

Exclude Non Mature Data

Nov 15, 2009

I have data that I calculate by month and at the bottom I calculate a weighted average. However, I want to exclude the last two months (data points) because they are not mature.

So every month when I update the spreadsheet, it would be the two newest data points to exclude.

View 7 Replies View Related

Inputbox To Exclude Data

May 16, 2008

I need to have an inputbox for users to enter multiple store numbers. What I need to do is actually filter out the store numbers that was entered into the inputbox
my code so far is:

'Inputbox to filter out new stores
Newstore = InputBox("Enter New Store Numbers seperated by a space", "Enter New Stores", "119 120")
x = Split(Newstore, " ")
For i = 0 To UBound(x)

Range("A1").CurrentRegion.AutoFilter Field:=2, Criteria1:=Array(x) _
, Operator:=xlFilterValues
Next i

View 9 Replies View Related

Color Chart Data Points By Cell Values Of Non-Source Data

Apr 14, 2008

I have a bar chart that was made from table 1 which has the value in it. I have another table, table 2 that has the name corresponding to the value in table 1. I want to color data points in a bar chart with a different color for each name I have in table 2. I also want to give a data label in the data points with the value I have in table 2 instead of the original data label from table 1. Is it possible to do it?

View 3 Replies View Related

Count Using Sumproduct (but Need To Exclude Certain Data)

Dec 4, 2013

I am trying to exclude unusable data from my analysis. I have flagged data in the flag column on sheet 1 by a "1". A "0" is good data. On sheet 2, I have counts for the amounts of a certain code per partner. I used a sumproduct formula for the counts, but now I want to exclude all data with a flag "1".

View 4 Replies View Related

Resample Data: Total Number Of Points Reduced By Averaging The Data Not By Simply Deleting One Of Every Four Rows

May 16, 2006

I have several files of data that are from a data logger. The data is broken up by day. Each day has roughly 43000 rows of data, at its sample rate. This has made charting the data a nuisance. Is there a way to resample the data so it fits in the 32000 points excel can chart? In the future I will set a sample rate that will keep the number of points below the 32K per series. I would like to be able to have the total number of points reduced by averaging the data not by simply deleting one of every four rows.

View 5 Replies View Related

Filtering Cells To Exclude Specific Data

Jan 31, 2014

I have a column that contains combined data of numbers and letters. I want to filter that column to exclude cells that contain the letter "d" or "f". I am trying to create a pivot table from the results of the filter to only display the ID #s that do not contain the letters "f" or "d".

The data inside the fields is not the same length and is not listed in a particular order. I have over 14,000 records I need to filter.

Data set example: The items in red are an example of the cells I would like to exclude from my worksheet. Is there a way to filter without deleting the data?

445-0706634F946
5886-K949-D001
445-0716622F946
445-0706634F887
497-0480473
445-9995967F025
6569-9788-2342
9016-1300
9016-C765-875
123-S765-87645

View 1 Replies View Related

Exclude Unwanted Data After Macro Filter

Sep 9, 2006

I have a sheet for addresses (Column 1 = Name, Column 2 = Address line 1, Column 3 = City, Column 4 = Postcode) and I run a macro that Filters that data based on the the city to different Tabs. This works fine except that sometimes, once the data is filtered I will find one or two addresses on my new filtered sheets that I really wanted to exclude. As my main address Tab information changes regularly, Ideally, I would like to see the Item on the City filtersheet, Double click it, and this would have the effect of deleting it from the City filtersheet but also copying it to an "Exclusions" Sheet which I could use before I run my normal filter macro to filter out these bad addresses.

View 9 Replies View Related

Average Dynamic Data But Exclude Zeros

Feb 26, 2008

I use a SQL query to export a subset of data from our server. After manipulating the received data I need to Average one column that has been sorted into a number of blocks consisting of variable numbers of rows and post the result to the ajacent cell. I have tried the Countif worksheet function but am unable to resove the syntax problem.

Sub SumBlock()
Dim First_Row As Long
Dim Last_Row As Long
Dim iTotalRows As Long
Dim iCount As Long
iTotalRows = Range("A65536").End(xlUp).Row
First_Row = 2
Do While Last_Row < iTotalRows - 1
Last_Row = ActiveSheet.Range("j" & First_Row).End(xlDown).Row
iCount = Application.WorksheetFunction.CountIf(Range("J" & First_Row), ("J " & Last_Row), ">0")
ActiveSheet.Range("K" & Last_Row + 1).Formula = "=Sum(J" & First_Row & ":J" & Last_Row & ")/iCount"
First_Row = Last_Row + 2
Loop
End Sub

View 5 Replies View Related

Exclude Items In Dropdown Based On User Data

Jul 10, 2013

I'm trying to set up a diet/training spreadsheet so that when a client enters foods and exercises they don't want/can't do, those foods and exercises are made unavailable in dropdown menus (on my programming sheet) so that I can't inadvertently put them into their plans. I have a webform they fill out, which automatically puts the data into a spreadsheet and I know how to pull data from there into the clients' workbooks...

View 2 Replies View Related

Plotting Data With 35 Points?

Jan 25, 2014

I have a data for sedimentation along a pipe. I measured sediment concentrations at different lengths of the pipe, after different intervals of time. So I have 35 concentration values, take at 5 different points along the pipe, at 7 different times. Is it possible to plot a chart with these 35 points, with depth on y-axis, time on x-axis? These 35 points are percent removals (0-100). .

View 4 Replies View Related

Trend Data Points

Dec 14, 2008

What I have is a column (column A) that only has two data entries at any one time.
I need column B to fill in the trend of those two data points.

My problem is I don't know how to deal with the cells that say #N/A. Which is every cell other than the two cells with data.

If I replace the #N/As with say 1, then it messes up the trend data points.

It seems like I need to be using the trend command, but any solution would be welcome.

View 14 Replies View Related

Directional Data Points

Oct 11, 2007

I have a line chart that has 4 points. Is it possible to make the data points point to the next point? If so how can I do that?

View 9 Replies View Related

Hiding Data Points

Oct 12, 2009

I have created a series of charts in excel and the data sources for them are located on a hidden sheet. I need to keep all the charts active as I have set up macros that enable a viewer to automatically change the chart with a click of a button. However the data behind these charts is confidential and I cannot let the viewer have access to the individual data points.

My issue is that when you 'hover' your mouse over the lines on the charts, the various data points can be seen

Is there a way to make these data points hidden when you hover your mouse over the chart but at the same time keep the source data active behind the chart?

View 9 Replies View Related

Graph Min And Max Data Points

Sep 25, 2007

I need to map the names of the companies on the x axis (ANF, AEO, ARO, PSUN) and 5% to 30% on the y axis. Plotted for each company should be the min and max. So two points for each company. How can I plot this data. Underlying data as follows:

MINMAX
ANF18.6%19.8%
AEO7.8%21.0%
ARO6.2%9.0%
PSUN6.1%14.2%

View 3 Replies View Related

Data Validation To Exclude Letters But Allow Numbers Beginning With Zeros

Nov 8, 2008

I am using Excel 07 with Vista, and creating a spreadsheet in which users will be inputting a record into every row.

There is a row that requires a number to be entered, where some of the numbers will begin with 0 or 00 or even 0000. I want to make sure no-one incorrectly enters a letter, however with data validation set to whole numbers, it formats the cell to change an entry of "005667" into just "5667". There is no set length for the field.

The only way to have "005667" is the change the column to text, but then obviously that defeats the whole purpose!

View 10 Replies View Related

SUMIF For Duplicate Data Points?

Oct 25, 2013

I was creating a sales report: See below

Sales for Shook, Emily(131520) Total Sales $160.18
Sales for Clayton, Casandra(131008) Total Sales NO SALES FOR THE WEEK
Sales for Jofery, Rebecca(126310) Total SalesNO SALES FOR THE WEEK
Sales for Brea, Olga(124257) Total Sales$140.21
Sales for Mastro, Rachel(131521) Total SalesNO SALES FOR THE WEEK
Sales for Rodriguez, Blanca(111550) Total Sales $154.33
Sales for Katz, Mary Isabel(126840) Total Sales$269.87
Sales for Kitson, Mackenzie(130466) Total SalesNO SALES FOR THE WEEK
Sales for Blagniceanu, Adriana(126518) Total Sales$161.25
Sales for Best, Jessica(128350) Total SalesNO SALES FOR THE WEEK
Sales for Sanchez, Vanessa(126437) Total SalesNO SALES FOR THE WEEK

The Sales For (Last Name, First Name) was a concatenate I created to give everyone a unique identifier. Than I used a vlookup based on the the sales report to get there total sales

=IFERROR(VLOOKUP(D7,sales2,5,FALSE),"NO SALES FOR THE WEEK")

My problem is say for example: Sales for Sanchez, Vanessa shows up twice on the report stating she has total sales for $40 and $60 how can I get excel to calculate that within my VLOOKUP Function. If there a formula I can use to combine both values. I was think SUMIF is the most likely answer but I'm having problems.

View 2 Replies View Related

Colour Chart Data Points

May 11, 2007

How do i conditionally colour chart data points. This is an example of my data from which the chart is created from.

Salesman A 10
Salesman B 10
US Total 20
Salesman C 20
Salesman D 10
Philipines Total 30

When the chart is created, i would like the country total data points to be in orange while the rest of the data points should remain in white. I tried this code and it returned me an error.

ActiveChart.SeriesCollection("Average of Margin").Points("ID Total").Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With

I also tried the online F1 help it told me to use Points(Index). However i do not want to refer to the ponint with an index as the number of salesman or country could change. Therefore changing the index points.

View 4 Replies View Related

Charting 2 Data Series With Different Numbers Of Points

Jun 12, 2014

Have Series 1 (to be a line chart) with 20 data points X values are 0 to 68. Have Series 2 (Bar chart) with 68 data points, again X values 0 to 68

Cant get series 1 to display beyond X value of 20

View 3 Replies View Related

Stretching Chart To Show More Data Points

Nov 26, 2013

Attached is an excel file of my issue. I want to stretch the graph so it shows at least 3-4 months. at the moment is only shows 1 month.

Timeline format - Copy.xlsx‎

View 1 Replies View Related

Graphs With Conditioning Formatting On Data Points?

Dec 17, 2013

Is there any way I can use conditioning formatting on datapoints in a graph, so that if certain points meet the criteria they will appear with another symbol?

Suppose I would like to change symbol on the datapoints exceeding the value 200 in this graph: Graph.jpg

Can I do that?

View 3 Replies View Related

Linear Regression Over The First 5 Points In The Set Of Data On The Same Graph

Mar 2, 2009

I have a set of data. I know how to do linear regression over the whole set of data. How do I have another linear regression over the first 5 points in the set of data on the same graph ?? I am using Excel 2007

View 14 Replies View Related

Sampling Select Data Points At Certain Percentages

Feb 10, 2013

I have a set of data that represents points in a cycle. I would like a way to take a subsample of the data at discrete percentages (i.e., 0%, 5%, 10%......100%).

View 3 Replies View Related

How To Display Custom Tooltips On Data Points

Jun 6, 2014

I have a line chart (multiple series) in which I am trying to display custom tooltips on the data points. The source for these custom tooltips are the Notes columns in the first worksheet. The code (which I've copied from an internet site) is working to some degree but I'm not sure how to modify it to pick up the notes column as the content for the tooltips. Also, the example I took this from only had one line whereas I will have 5 when the chart is complete.

View 6 Replies View Related

Line Chart - How To Ignore Zero Value Data Points

Jan 13, 2009

I have the following chart that displays average speed for my bike rides (Green line). The problem is that I don't want the line going to points with no value or zero value. So, the first month would just be the point then, when I enter in an avg speed in Feb then the line would connect to Feb. Is this possible?

View 9 Replies View Related

Adding Various Time Data Points In One Cell

Jan 10, 2013

I have a number of time data elements of different recordings that i would like to be able to use one formula in one cell and have it give me to total time. For example:

00:03
00:55
00:47
00:05

All in one cell (i.e., not having each data point in a cell), I would like to have the formula tell me that these four recording times equal 00:01:48 (i.e., 1min 48 sec).

View 1 Replies View Related

Excel - Draw Rectangle From Two Data Points?

Sep 5, 2013

I want to put a length in one cell and a width in another. Then have excel draw a square/ rectangle to this size. It could just outline cells, each cell being one inch or any other way.

View 9 Replies View Related

Limit Chart To Certain Number Of Data Points

Jun 3, 2014

I have a macro that runs every 15 minutes and stores data into a table. This table is attached to a chart and auto updates with the new data every 15minutes. The table will house historical data over time, however I only want the chart to reflect the last 96 entries (15min data points over 24hours). So, once i have enough data my table will be A2:A97. When A98 is populated, i want the data in A2 to be removed from the chart only.

Here is a breakdown:

A2:A97 is currently displayed on the chart.
I enter new data into A98.
The chart will now display A3:A98.
I enter new data into A99.
The chart will now display A4:A99.
I enter new data into A100.
The chart will now display A5:A100.
and so on.

How I can accomplish this? Does this need to be done in VBA?

View 3 Replies View Related

Line Chart: How To Ignore Zero Value Data Points

Jan 13, 2009

I have the following chart that displays average speed for my bike rides (Green line). The problem is that I don't want the line going to points with no value or zero value. So, the first month would just be the point then, when I enter in an avg speed in Feb then the line would connect to Feb. Is this possible?

View 9 Replies View Related

Formating Specific Data Points In Chart

May 20, 2007

I have a time series chart ( dates on x axis , with the data values on y axis ). It is a line chart. So column A has the dates, and column B has the values. Both have length 100. Now, column C can be considered to be the "flag" column. If a cell in Column C has the flag set to true , then the corresponding date/data values in the chart needs to be highlighted or formatted differently

I tried playing around with conditional formatting of x-axis but could not make any progress. Do I need to create a new data series with the C column?

Also given that there are 100 date values, I dont want all x-axis points to be listed on the chart. I guess excel by default shows the date points as the user increases/decreases size. But I dont want the specifically formatted date points to be skipped .

View 5 Replies View Related

Align X Y Data Points On Combination Chart

Sep 11, 2007

Following my bosses recent charting attempts involving multicoloured backgrounds, graduated bars, textured boxes, mis-matched fonts etc, etc, which frankly showed no information whatever, I was asked to simplify them.

I did so, as in the two attachments, but the response is now along the lines of "well, yes, but they aren't very exciting, are they?"

View 4 Replies View Related

Show Values Of Charts Data Points

Apr 23, 2008

I've been working on this chart for a while and can't seem to get my x and y axis as well as my series corrected. I have time and distance but since my distances are small I cant seem to make sense on how best to depict my data. Right now it is shown as data points but would love to show value.

View 2 Replies View Related

Data Labels Points In Scatter Plot

Jun 19, 2008

I am attaching the Excel File along with this mail. In chart there is data points have been mentioned in scatter plot like (38,15 etc) Instead of these points I want the names that I have mentioned in column A2:A9. I used to make lots of charts related to this and it is very diffilcult for me to manually punch all these names and most importantly the thing is that I cant download the software available on http://www.appspro.com due to some issues can you give me the VBA code for this which will automatically paste these names only I have to change is the data range and its very urgent................ASAP

View 7 Replies View Related

Formula To Look At All 9 Data Points And Give The Place Number

Jan 19, 2009

Column P is the percentage of body weight lost...

P
-1.98%
-2.92%
-3.87%
-3.48%
0.00%
-0.10%
0.00%
-3.56%
-3.57%

What I need is a formula to look at all 9 data points and give the place number. Remember that the lowest number is actually in first place.

View 2 Replies View Related

How To Find Accurate Average Based On Data Points

Nov 8, 2011

I'm looking to find a accurate average based on some data points. The issue is that some service times are 0 and so are considerably larger than the norm. So...

17
18
12
13
14
12
10
5
5
0
0

Assuming the following data 12,13,14,12,10 are probably the most accurate and all other are not.

View 7 Replies View Related

Excel Chart - Move Data Points To Front?

Oct 12, 2013

I have an Excel chart graphing two data records. As the data points cross, one is in "back" and one is in "front". I would like to reverse the current layout. Is this possible?

View 1 Replies View Related

Duplicate Entries For Single Data Points Sets

Mar 6, 2007

I am using old macro code in excel written several years ago. I am trying to educate myself to update and improve the macros I am using but not making much progress. This macro is used to record data from a DDE link to a Data Acquisition System. The D-AQ system sends the data points to r5c3:r5c9 typically. The macro then records the data points in those cells to the next row in sequence on a specific time interval. However, the macro is recording duplicate entries for each set of data points. Sometimes just two, but has been as many as four duplicates. I have a remove duplicates macro to run, after the fact, but preventing them from occurring in the first place is the goal. I included the application.wait method at 10 second intervals after the application .OnTime method, but it isn't preventing the duplicates.

Dim delta_time As Date
Dim Count As Integer
Dim n_rows As Integer
Dim start_row As Integer
Dim col_mov As Integer

Sub initialize_button_set()
start_row = 7
Count = 0
n_rows = Worksheets("sheet1").Cells(1, 2).Value
delta_time = Worksheets("sheet1").Cells(2, 2).Value
Application.OnTime Now + delta_time, "get_data"
Rem Worksheets("sheet1").Buttons.Add 275, 0, 72, 36
Worksheets("sheet1").Buttons.Caption = "STOP"
Worksheets("sheet1").Buttons.OnAction = "Halt"
End Sub...........................

View 2 Replies View Related

Conditional Format Chart Data Series Points

May 16, 2008

Background: I have a Chart sheet with a combination bar/line chart on it. Data is coming from a "datasheet". There is a combo box on the chart sheet that allows you to choose a specific office whose data you want to view. Pick an office from the dropdown and the chart changes. The data is a count of closed cases by month. The data sheet shows 22 Offices with their monthly data, along with entries for each month of the Upper Control Point, Lower Control Point and Mean based on the nationwide data for that month.

The chart itself uses bars to show the office's case count per month, and I've incorporated 3 additional data series to plot the UCP, LCP and Mean as lines for each month.

I'd like to change the color of the bars to be Blue, Green or Red depending on where they fall in the 3 lines. I sort of have it working, but I'm stuck.

The code below grabs the value of each series point and compares it to the 3 control points and does change the colors. I've figured out how to use the If, next to change from one series point to the next, but cannot figure out how to loop through each of the control points. As it stands now, it looks only at the first month's control point and uses that for each successive month.

I hope this is clear and I've tried to keep it as short as possible while still providing enough information.

Sub chartbarformat()
'
' chartbarformat Macro
' Macro recorded 5/15/2008 by vacoyoungd
'
Dim x As Integer
Dim val As Variant
Dim AveragePending As Range
Dim LowerPending As Range
Dim UpperPending As Range

View 8 Replies View Related

Color Code Data Points On A Scatter Chart (2007)

Jun 12, 2009

Does anyone know if there's a way to color code points on a scatter chart based on their values in a "3rd dimension" (i.e. other than the 2 dimensions represented by the chart axes).

You might imagine a scatter chart that plots transactions by price (y) and units purchased (x).

In that scenario, it might also be nice to color code each point by the region that made the sale.

The only way I know to do this is to manually create separate columns of data for each region, so that each region can be plotted on the scatter chart as a separate data series.

But since I'd like to experiment with several color coding alternatives, I'm wondering if there isn't a simpler way...

For instance, Rob Bovey's "XY Chart Labeler" can add data point labels to a scatter chart...

Is there, perhaps, another add-in that might enable color coding of data points based on a 3rd set of values?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved