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.
I have two columns where TREND does not work well. I want to calculate the TREND value of Column 2 (PERCENTILE RANK) based on a new RAW number. If I TREND between just the two rows, the value is fine (i.e. If I TREND between 105 and 110 RAW, I get the right PERCENTILE RANK if the new X value is 106 (which is 15.3% PERCENTILE RANK). How can I make a formula that will only look at the two rows and TREND just those two rows to give me the right PERCENTILE RANK.
RAW PERCENTILE RANK
10514
11020.4
11121.8
11729.5
11833
12342.5
12444.5
12954.4...........
I am trying to find the exponential equation from a set of data in excel. The problem is that the graph does not follow the scatter plotted points on the graph. How would you remedy this?
View 1 Replies View RelatedIs 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
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 RelatedI 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 RelatedI 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 RelatedI'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.
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?
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?
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%
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.
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.
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
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
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?
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
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 RelatedI 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 RelatedI 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 RelatedI 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).
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 RelatedI 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?
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 RelatedI 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 .
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?"
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 RelatedI 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 RelatedColumn 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.
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.