Average Chart
trying to figure out a simple baseball batting average chart.
and i can't seem to get the season average completed.
for instance
A-------------B------------C-------------D
at bats -------hits-------daily avg------season AVG
2--------------1-----------.500---------.500 (d2)
3--------------2-----------.666---------.600 (d3)
4--------------1-----------.250---------.444 (d4)
3--------------1-----------.333---------.417 (d5)
so, after each game, i entering the AB, and the hits, and i would like to have a running total of the season average.
i currently have this. but, i figure there must be a way to copy the formula down and keep it easier.
d3=SUM(b2:b2)/SUM(a2:a2)
d4=SUM(b2:b3)/SUM(a2:a3)
d5=SUM(b2:b4)/SUM(a2:a4)
d5=SUM(b2:b5)/SUM(a2:a5)
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Display Average Line On Chart
I have a dynamic Named Ranges called ChartData containing 4 values from 4 cells on the row selected. I have a chart using this Named Ranges so that the value displayed are always referring to the current row. This works fine. I created a second Named Ranges ChartAvg which is =AVERAGE(ChartData) that I project to use in the Chart. However, I need to have 4 time the same value (average) if I want to display a line in the chart. My problem is: I don't want (and can't) enter the Named Ranges ChartAvg in 4 cells to use in the chart. I wish to create a 3rd Named Ranges called ChartAvgData that would be used in the Chart. So now, to my question : How to create a Named Ranges that is a list of (4) values, somethine like: {ChartAvg,ChartAvg,ChartAvg,ChartAvg} This is not working, whereas {1,2,3,4} (for example) works fine.
View Replies!
View Related
If,average,sum Formula, For Rating Chart
I'm trying to get a formula working that is basically for a rating chart. The chart is somewhat simple, but the formula is a bit complex (for me anyway) So the details are the rating is on three axis with three levels, and here is what I need to do. At the moment the formula below works, but it isn't 100% accurate because I actually need to take an average of F5+G5 then take that value and add it to H5 then divide by two to get the proper average of the two charts. (if that makes sense. =IF(AVERAGE(F5:H5)>2.33,"High",IF(AVERAGE(F5:H5)>1.67,"Medium",IF(AVERAGE(F5:H5)<1.67,"Low"))) Here is the formula that I thought would have worked, but Excel begs to differ =IF(SUM((AVERAGE(F5:G5))+H5)/2>2.33,"High",IF(SUM((AVERAGE(F5:G5))+H5)/2>1.67,"Medium",IF(SUM((AVERAGE(F5:G5))+H5)/2<1.67,"Low"))) I played with the brackets quite a bit thinking I had something in the wrong order, but nothing seems to work. I always get FALSE in the field and an error as well when I enter the formula.
View Replies!
View Related
Formula =AVERAGE(B16:L16) To Give The Average
I'm using the formula =AVERAGE(B16:L16) to give me the average. However I have a couple of problems with this. Firstly I would like to exclude the value zero from the average. Secondly to also ignore the lowest and highest values. Example, if the values in the cells are 0,1,2,3,4,5,6,7,8,9,10 then the current result shows 5, by ignoring the 0 and lowest value 1 and highest value 10 the average should be 4.5.
View Replies!
View Related
Grade Point Average Using AVERAGE IF AND
I am attempting to calculate Grade point averages for my students for all classes. There are 5 columns of grades to be considered. I frist assign values of 0-5 to the grades then average the points. My problem is that I want the AVERAGE to ignore the zero but it calculates it as well. (I enter a 0 if I have no grade for that class.) I have tried the{ } to make it an array formula but this also did not work. Here is what I have, can anyone help? =SUM(AVERAGE(IF(AND(L2>89,L2<100),5,IF(AND(L2<90,L2>79),4,IF(AND(L2>69,L2<80),3,IF(AND(L2<70,L2>59), 2,IF(AND(L2>0,L2<60),1,0))))),IF(AND(T2>89,T2<100),5,IF(AND(T2<90,T2>79),4,IF(AND(T2>69,T2<80),3,IF( AND(T2<70,T2>59),2,IF(AND(T2>0,T2<60),1,0))))),IF(AND(AB2>89,AB2<100),5,IF(AND(AB2<90,AB2>79),4,IF(A ND(AB2>69,AB2<80),3,IF(AND(AB2<70,AB2>59),2,IF(AND(AB2>0,AB2<60),1,0))))),IF(AND(AJ2>89,AJ2<100),5,I F(AND(AJ2<90,AJ2>79),4,IF(AND(AJ2>69,AJ2<80),3,IF(AND(AJ2<70,AJ2>59),2,IF(AND(AJ2>0,AJ2<60),1,0))))) ,IF(AND(AR2>89,AR2<100),5,IF(AND(AR2<90,AR2>79),4,IF(AND(AR2>69,AR2<80),3,IF(AND(AR2<70,AR2>59),2,IF (AND(AR2>0,AR2<60),1,0)))))),-1) I know it is huge. The syntax is correct. It calculates the average, but always for all 5 columns. It will not ignore a 0 in a column.
View Replies!
View Related
Avoiding Average Of An Average: Times
Ok, I for some reason just cant wrap my head around this. I need to to get the average time per call of two rows, but they are based on how many calls taken. so in one column i have 50168 calls taken at 4:21 seconds per call. and in the next row i have 597 calls taken at 5:20 per call. I need to see what the new average will be with them combined, and I need it to display in m:ss form.
View Replies!
View Related
Odd Chart: Combine Data From Multiple Worksheets And Make A Chart
I am trying to combine data from multiple worksheets and make a chart. I have about 200 keywords in every worksheet (about 50), and some of them repeat themselves through worksheets and some don't. For every keyword, I have an associated value in the next column that I want to portray over time (each worksheet is for a different period). So what I need to figure out is how to be able to pick any 10 keywords from the worksheets and put them in a line chart where I can see the associated value for each period for every worksheet so I can compare my keywords' efficiency. The tricky part is that some worksheets do not contain the keyword and other worksheets contain the keyword in a different cell than the previous wsheet.
View Replies!
View Related
Combine (Overlay) Bubble Chart & Point Scatter Chart
I would like combine (overlay) a bubble chart with a connected point scatter chart. I understand that, without VBA, this is not possible. However, I understand that, by using VBA, the markers of a scatter chart can be configured as circles with their size proportional to values in a specified column. This pseudo-bubble chart can then easily be combined with a connected point scatter chart. My question is: does anyone have any VBA code to share that shows how to configure a scatter chart as the type of pseudo-bubble chart described above.
View Replies!
View Related
Line Chart - 12 Month Chart Moving With Dates
Is there a way to display only the current month and the past 11 months in a Line Chart in Excel? So if I was to print Jan 2008 Excel graph, it would only display Feb 2007 - Jan 2008 data charting. The spreadsheet of the data contains data for Jan 2005 - Jan 2008 so far, but only the current month with the past 11 months should display in the line chart depending on what is the current month.
View Replies!
View Related
Pareto Chart, Set Line Chart Origin At Zero
I have a pareto chart(Bar & Cumulative Line Chart) that I would like to have the line orginate at x = 0 & y = 0 without changing the position of the other points on the line chart. Basically this is for better visual appearance. When I add a 0 to the table, it skews the alignment of the line chart with the corresponding bar chart.
View Replies!
View Related
Chart Making- Chart With 3 Data Series
I have a chart with 3 data series. The series are located in columns A, B and C respectively. Series I is a general number anywhere from 0 to 100,000. Series 2 is also a number, but is is devided by series 1. So, if series 1 was 100,000 then series two would be 25,000/100,000 which is 0.25. Series three will always be numerator of series 2 or 25,000 in this example. The numerator, or the 25,000 will never change, so, in my example, series three will be a straight line across the chart because it is always 25,000. In my example, the .25 is plotted on the left value axes, and series one is plotted on the right value acccess (secondary axis). Series three is just a line in the middle. My problem is that sometimes the line, series 3, doesn't match the values in both the right and left values axes. So, if series one is 48,000, then series two would be 48,000/48,000 = 1, and series three would be 48000 - the straight line. The third series should be a line touching the 48,000 on the right and the number 1 on the left. It does touch the 48000 on the left, but is below the number 1 on the left.
View Replies!
View Related
Convert Scatter Chart To Bubble Chart
I would like to combine / overlay a bubble chart and scatter chart with straight connectors. I understand that, without VBA, it is not possible to combine a bubble chart with a scatter chart. Unfortunately, I am a VBA newbie, and so I cannot write my own code (though I can usually adapt code to my specific environment). My question is: does anyone have VBA code to share that will convert a scatter chart series (x,y data in 2 columns) to a bubble-style chart (bubble radius in 3rd column)?
View Replies!
View Related
Line Chart To XY Chart And Vice-Versa
Attached is the sample data worksheet. Chart 1 is XY type chart using Seconds (2nd column of sample sheet as x-axis from 42510 to 42530). How do I change it to Line chart using Time (1st column of sample sheet as the X-axis) retaining same data from 42510 to 42530 on both primary and secondary axis?. And how do I again change it back to XY chart?
View Replies!
View Related
Bar Chart And Overlaying Scatter Chart
I'll attempt to explain what I'm hoping to do. I have a column of data that I want to plot out into a chart. For example the data points might run from -1.50 to 2.5. Let's say there are 20 data points in the column and they are spaced thruout the range shown above. They tend to cluster amoung themselves into 2 or 3 distict groups. I want to first produce one horizontal bar that runs from the -1.50 to 2.5 overall range and secondly, I want to overlay a scatter chart horizontally over the bar showing where the data points lie within the range and also so I can visually see the clusters of points. With the data residing in a column, when I generate the scatter chart, the points are laying verticle within the chart and spreadout horizontally. I can manually get what I want by narrowing the chart down to the point that the points lie on a straight vertical line, but I haven't been able to figure out how to "rotate" the chart 90 deg to overlay the scatter points within the bar chart. Naturally, the optimum solution would be such that I could generate the charts originally and then allow the spreadsheet to generate the new charts as we modify the data values, etc.
View Replies!
View Related
Change Chart Series Pasted From Another Chart
I want to use a macro to change attributes of a series in a chart. Unbelievably, if the series has been pasted into the chart from another chart, and although the macro can address the series and even return values (eg name) correctly, the selection simply cycles to one of the original series on the chart.
View Replies!
View Related
Making Average Buy Price And Average Sell Price
to formulate Excel formulas to obtain the average buy price and average sell price for me to do this futures trading. Thanks a lot. I downloaded the Htmlmaker to post the spreadsheet here to show the manual way to calcualte the average buy price and average sell price but when it is on html form, i clicked on the 'Please click this button to send the source into clipboard' button & then i paste into this thread. Is the way to make my spreadsheet appear here correct cause it cannot work.
View Replies!
View Related
A Chart Will Columns And A Line Chart Below??
I can create a column chart in excel comparing sales in 06 v's 07 for each month in 07, what I'm aiming to show on the chart is how sales have grown or not over a comparative period. A simple enough graph, I end up with about 24 columns (2 for each month and then a gap between each month). I now want a line chart to show the performance of an index over the same time period, lets say the FTSE100. I don't know how to get the line chart under the columns on the same graph, using the same x-axis with a small y-axis to the left. I could propably figure out the y-axis bit if I could only get the line chart below the columns.
View Replies!
View Related
Convert Chart Into Pivot Chart
Please look at the sample workbook below. The chart you see below is fine BUT I would like in a pivot table chart, so I could select any days on the pivot chart. I have tried it to do it myself but for some reason the time is not displaying on the pivot chart correctly. Any help please ? P.S. If not possible then can we add a scroll bar or something?
View Replies!
View Related
Pivot Chart Changes Chart Upon Refresh
I have created a pivot table and chart. The pivot chart consists of three sets of data from the pivot table. Two of the data sets are lines and the third data set is displayed as columns. I made the third data set into columns by right clicking on the data series line and selecting chart type. I change the chart type for that data set to a column and it worked great. The only problem is that when data is refreshed in the associated pivot table, the entire chart turns into columns with completely different formatting. Does anyone know how I can maintain the graph with the two lines and one column data set when I refresh the data?
View Replies!
View Related
Graph The Following Data In An Area Chart With Line Charts Superimposed On The Area Chart
I am trying to graph the following data in an area chart with line charts superimposed on the area chart. I have a lot of data (and a lot going on) so I'm trying to figure out the best way to show this in excel from a functional standpoing (i can't get this to work in excel!!) to also an asthetic standpoint (dont want it to look terrible or illegible). This is what I'm trying to chart: 1) Weather data (temperature) by region: So one region, would be: Northwest I would like the "area" (so a shaded region) to be the min/max of the temperature data for each month. 2) I would like to show the temperature for each year as a line graph on the chart - so you can see if a year falls in or out of the shaded region. 3) I would like to show a company's sales increases across the same months per year as separate line charts. I may choose to just show the biggest outlier year in the end... or to show 2006 (the latest data). What I am trying to convery with the chart is that the company's sales is or is not tied to weather deviations. I have attached an excel file with the data. I haven't been able to use the area chart or get a two axis chart to work or get it to look even remotely professional.
View Replies!
View Related
Pivot Table With Dynamic, Updatable Chart, But Not A Pivot Chart!
My boss wants me to design a dynamic, updatable chart in Excel 2003. I initially made a Pivot Chart based on a Pivot Table which worked perfectly, but it doesn't look professional enough when printed (or viewed) and she wants me to approach it a different way. So, I created a graph based on the data in a Pivot Table, and used dynamic ranges as the source for the graph series so that the chart updates when the criteria fields are changed for the Pivot Table. I then added two combo boxes (ie data validation lists) to the Chart sheet, and wrote VBA code so that whenever the combo box values are changed, the Criteria fields for the Pivot Table on the 2nd sheet are updated accordingly, and this in turn causes the graph to be updated as well. This solution also worked perfectly, but now I've been told to create the graph without macros. Does anyone have any suggestions? The requirements/details are as follows: 1. The Pivot Table is on sheet "PIVOT", and the graph is on sheet "GRAPH" 2. The Pivot Table has two criteria - School Name and Year Level 3. On sheet "GRAPH" there are two data-validated fields, School and Year, which only allow the selection of valid Schools and Year Levels Is there any way to make the Pivot Table update when values are changed in the fields on the CHART sheet so that the chart also updates, but without using code nor a Pivot Chart?
View Replies!
View Related
Pivot Chart Object: Find Any Suitable Object To Choose From To Make A Pivot Chart In Powerpoint
1) i have office 2003 on a laptop. within powerpoint, i can create a 'microsoft excel chart 11' object. to create a link to the excel data source, do i have to go through the odbc sql setup? it works, but i don't want my powerpoint to be dependent on some excel file somewhere. what are the other options to insert/make a functional pivot chart in powerpoint with the data also within powerpoint? the data as sheet option does not result in the chart being a pivot, it's just a plain chart. it has to be a proper object, not an image paste or a chart that updates links with the excel file open. 2) i have office 2007 on my other laptop. i can not find any suitable object to choose from to make a pivot chart in powerpoint. what's the best way to go about in 2007 version? 3) am i going about this the wrong way with the objects? should i be after vba code?
View Replies!
View Related
Average Of Last 10
What I am trying to do is track a 10 week rolling average, where scores are entered weekly in row A6 thru W6. The formula needs to count back starting with W6 until it counts 10 scores then average them. Some cells may be blank.
View Replies!
View Related
Conditional Average Using IF Possible?
I have a spreadsheet with days of week and places saved as text values, and I need to get averages for specific items, for example, Day of week in A1, place in A2, number in A3 I want to get in A4 say, the average of the numbers for all sundays at cambridge. Any ideas how I could do this?
View Replies!
View Related
Rolling Average
Could you please supply a formula that will calculate a four week rolling average of column B in relation to the dates in column A? I tried using the average and offset functions but can't quite get it to calculate the correct averages.
View Replies!
View Related
Average Between Times
This sheet has several years of data so is full at 65,000 rows. One issue is that there may be skip or gaps in the date sequence, so I can't rely on the relative position of cells to be accurate. Thanks for any ideas! ******** ******************** ************************************************************************>Microsoft Excel - solar1.xls___Running: xl2000 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCD6492810/1/200313:00:00308 6492910/1/200313:15:00302 6493010/1/200313:30:00280 6493110/1/200313:45:00323 6493210/1/200314:00:00366 6493310/1/200314:15:00317 6493410/1/200314:30:00247 6493510/1/200314:45:00219 6493610/1/200315:00:00259 6493710/1/200315:15:00259 6493810/1/200315:30:00233 solar1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View Replies!
View Related
Weighted Average
I have a large dataset that includes information on the investment payout ratios of US companies over the period 2004-2006. I need to calculate the weighted average investment ratio for each company---see sample below Microsoft Excel - example.xls .... [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR. The corresponding weight for each year must be as follows: 0.5 for 2006, 0.3 for 2005 and 0.2 for 2004. Then, for company A, the weighted average will be: (0.5*B2)+(0.3*C2)+(0.1*D2) = 7.8 (see cell E2) The problem is that for some companies there are missing data for some years. For example, for company B, there are no data available for year 2004. In this case , the weights have to change to 0.6 for 2006 and 0.4 for 2005. Then, the weighted average investment for company B will be (0.6*B3)+(0.4*C3) = 16 (see cell E3). Similarly, in cases when there are data for only 1 year , the weight has to change to 1. Then, for company C the weighted average is 35 (see cell E4) Is there any formula to give me the weighted average as it appears in column E without having to adjust it for each single case?
View Replies!
View Related
Nested Average IF AND
I have used this board quite a bit, however I never have registered an account with mrexcel. Ive tried searching the internet and your forums for help with this issue and have not had a success getting the results I am looking. What I am trying to do is get an AVERAGE from cellAD only IF cellW is with in a certain month, AND if Cell U has a certain name (lets call it trainer for now) and if its possible (would really help) to also include : AND IF cellV is Course name X
View Replies!
View Related
Define And Average
I have a different set numbers each day I need to average if they fit a certain criteria. These are actually times (in Military time) and in the 3 column spread sheet I have in column D the difference between column B and C. What I need to do now is find all the data from column A that is in between 1100 and 1500 and average all their column C results together. I need to do this for all 6 4 hour time frames (from the small table). I am using this array: =AVERAGE(IF(B2:B14>1100,IFB2:B14
View Replies!
View Related
Average By Month ...
I am trying to input monthly budgets based on a yearly budget for which I already have a few month's budgets. For example: Jan - ? Feb - ? Mar - ? Apr - ? May - ? June - $1105 July - $1325 Aug - $1470 Sept - $835 Oct - ? Nov - ? Dec - ? TOTAL YEARLY BUDGET - $10,000 I need to find out what the other 8 months are, on average. I'm looking for something along the lines of each month's average IF SUM(A3:L3)=M3
View Replies!
View Related
Average If In 2003
I'm trying to do an average if statement, but I only have excel 2003 and don't think the averageif statement is available. So... Here is what I have. In column A I have have a list of H's and @'s. In column b I have a list of numbers that I want to average out. Basically I want to find the Average for all the #'s in column B that have an H in column A.
View Replies!
View Related
Formula For Average
Cell: F6 is to represent "average weekly offering". D2:D53: represents the Total Given Year To Date. I need a Weekly Average for Offering, so is it possible to have a formula that can show the average weekly offering in cell F6?
View Replies!
View Related
DateDif Average
I am calculating the length of time someone has worked for the company: Column; Row A1, Hire Date MM/DD/YYYY Column; Row B1, =DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months, " & DATEDIF(A1,NOW(),"md") & " days" Which gives me a result like: "4 years, 5 months, 10 days" I want to be able to average the results from column B, thereby producing the average "years, months, and days" worked. Just not sure how to get there.
View Replies!
View Related
Average If Statements
consider that I have two columns of data with say five rows each. Column A contains company names (say Company A,B or C). Column B contains revenue that I'd like to average. However, I want to exclude revenue from one of the companies (say Company C). Keep in mind that my actual number of rows is in the thousands, so manually picking the rows to average is not a viable solution.
View Replies!
View Related
Average Every 10 Readings
1. I have a very long list of values which exceed 1000 readings. I would like to calculate the averages in blocks of 10. 2. The following is a very challenging query: I have the following readings From To Value 0 4 55 4 12 64 12 21 53 21 31 34 etc.. I would like to get the following outputs From to Weighted Value 0 10 ((4x55)+(6x64))/10 10 20 ((2x64)+(8x53))/10 etc
View Replies!
View Related
Advanced Average
with a complicated average function. I’ve made a tender comparison sheet which compares different suppliers and their prices. The suppliers are assigned a region (with data validation when they are added and I want to get an average on the specific article (on every row) on all suppliers in the same region. All supplier names and regions are on row4. See example below: Art no: Supplier1 region1 Supplier2 region1 Price Price A1 2 3 B1 1,5 2 C1 How do I get the average from only one region?
View Replies!
View Related
Weigted Average
I need to get the weighted average of abandon time, talk time and after call work time in two different locations. The way the data is set up is per day per site. I know how to get the weighted average if all the data is in one table but how do I do it if I have data in two different tables? I’ve tried a lot of different things but since I’m still a bit of a novice, I’m having real difficulty figuring this out. Let me know if you need more data. I could even upload a sample sheet. This is what I have tried so far and I know its no where near correct: (SUMPRODUCT(M4,O4)/M4)(SUMPRODUCT(V4,X4)/V4) In addition, the data that needs to be calculated is coming from a lookup formula and one site doesn’t have as many calls and doesn’t work the weekends so I get a #N/A result and because of this, I’ve even had more problems getting an answer.
View Replies!
View Related
Percentage Average
I have two rows of data representing marks out of different amounts. E.g. A1=23, A2=14, A3=50 etc. B1=12, B2=10, B3=32 etc. So, the first mark is 12 out of 23, the second is 10 out of 14 and so on. I have a large number of these for at least 250 people. Is there a simple formula that will immediately work out the average percentage for all of the marks above? If so, will it still work if some marks are missing?
View Replies!
View Related
How To Weight An Average
I have some aged data, as follows 325days - $225 20days - $2,725 16days - $27,250 11days - $8,175 Now, if I just take the average number of days ((365 + 20 + 16 + 11) /4) I get 93.0days However, I want to high value days to carry more weight, than the lower value days. I gave each set of data a weighted score by dividing the value by the total value, i.e. $225 / $38,375 = 0.006 $2725 / $38,375 = 0.071 $27,250 / $38375 = 0.710 $8175 / $38375 = 0.213
View Replies!
View Related
Average From Several Averages
Column 1 contains the number of times an ad has shown Column 2 contains the average position of the ad from column 1 Like so, Ad Impressions.........................Avg Position Google: 14,220,023..........................3.2 Yahoo: 346,656...............................1.2 MSN: 123,234..................................8.9 Total: 14,689,913............................??? Doing the following, = Average(B2,B3,B4) This is not the real average as this is directly influenced by the number of ad impressions... How can i make a real calculation to give me the true total average position of my ads?
View Replies!
View Related
Average Percentages ...
I have a row of formulas that calculates information into a percentage. As you input info, a percentage is created in the cell. Those cells that do not yet have information entered still show " DIV/0" or something like that. I need a formula that will average all the percentages, but the problem is, it's trying to also average the formulas with the percentages, so I currently get an error.
View Replies!
View Related
AVERAGE More Than 30 Arguments
I'm getting the error that tells me I have more than 30 arguments when trying to get an average of cells across a row. The biggest issue is that I don't want to average all the cells in the row, just some of them. I tried writing an IF statement for it and failed. What I want is every fourth cell to be totaled into the average. Another way to look at it is that I want every cell under the heading "south" to be totaled into the average.
View Replies!
View Related
|
TRACKER
