I wanted to know how I can calculate the average gain and average loss for data that is inputted into one column? For example;
Column A
$12,000
-$5,000
$6,500
-$1,000
$8,000
-$4,000
Average Gain ??
Average Loss ??
I have a excel 2000 spreadsheet that has beginning and ending dates for certain services. I'm trying to tease out what our customer base has been doing over time in aggregate- i.e. how many customers subscribing to X in a given month/year.
The only way I've been able to do this in the past has been generating pivot charts of start dates (with 'start mo' and 'start year' busted out) as a positive count (+1), stop dates (with 'stop mo' and 'stop year' busted out) with a negative count (-1), then copying these tables into yet another page where I mash them together to get the 'flow' of our customer base over time and generating charts from there. Needless to say, it seems a whole lot more clunky than it should be, and I'm having a hard time searching the innarwebs for similar examples (even though I'm sure it's been done before). Example:
I have the data in
A1=30 and B1=40
A2=60 and B2=20
And I also 2 small Pictures ie picture1 and picture 2 which i insert in same excel sheet. I wanted to lookup C1 cell , if A1 is greater than B1 then Picture1 or vice versa.
I am looking for a simple loss/gain formula for my students. They are trying to find the loss/gain difference between an initial value, and the current value. They are using a monetary value, not date/time.
View 5 Replies View RelatedI run a slimming club and would like to use Excel to keep records of members weights, losses, gains etc. What I would like to do is enter their starting weight into a cell then each week when they are weighed enter a loss or gain in an adjacent cell. I would then like Excel to calculate that loss or gain as a percentage of their original weight. Is this possible?
View 11 Replies View RelatedI am trying to find a way to average all the numbers placed in one cell with the answer in another.
So in cell A1
1, 2, 3, 2 or 1-2-3-2
In cell A2 would be 2. But the amount of numbers in A1 will vary from time to time.
I have data from a tems current win/loss record. I want to calculate what the longest win streak is and the longest losing streak as well. What formula do I use?
View 9 Replies View RelatedE11 through E24 contains numbers and a few errors (#N/A) that need to persist (the errors need to show).
E10 needs to show the average of the numbers that are in E11 through E24, and just ignore the errors.
I have many columns like that - where the errors need to show and I need to show an average of the number/values that do appear, ignoring the errors.
Project: x
Activity: xActifity TypeReference FormDec-11Jan-12Mar-12Apr-12May-12Feb-12Mar-12W/EW/EW/E2-Mar9-Mar16-Mar23-Mar30-Mar6-Apr13-Apr20-Apr27-Apr4-May11-May18-May25-May77%91%91%R/S95%100%100%R/S100%100%100%100%MMMM100%71%71%R/S100%100%R/SMMMM97%81%100%R/S100%MM100%R/S75%85%R/SR/SMM100%100%96%Average
Basically what I want to achieve is the average % of Dec 11 (D11:D22 on the sheet im working on) scores but excluding any blank fields (not excluding potential scores of 0%)
How do I calculate the average based on multiple conditions? Is my syntax incorrect?
Here's what i'm looking for:
=average((IF(X1:X1000=A1)*(Y1:Y1000=B1),AA1:AA1000))
Column B values are the average of the most recent five values in column A.
The formula in Cell C1 is “=B7” so that the value in C1 is 1.17. My question is: When I place a value in cell A8, which will result in the average of the five numbers in column A4-A8 being placed in cell B8 how do I make the value in C1 change to be the value which is in cell B8 rather than the value which is in cell B7?
Note: I just want to be able to pick up the most recent average value – the
method whereby it is done is of no consequence. However I cannot think
of an alternative. Example:.....
I am trying to average a column of numbers. I want to only average the cells
that contain a number greater than 0.
Daily Result
14
10
16
10
5
15
8
12
15
9
I have the formula working for the average of the 4 lowest of all, using "AVERAGE" and "SMALL"; need for average of 4 lowest in most recent 7
I have two columns for a Home and an Away win-loss records for a team over a few seasons. The win-loss stats in each column are in the form 24-13 (2-two digit numbers with a hyphen in between). How can I get a total at the bottom of the column for all the win-losses?
View 9 Replies View RelatedI have a column of over 500 replies to a survey. I want to create a pie chart showing the average ages of the respondee to that that question, broken down into increments of 5 years (this was the question -- how old are you) and reported as a percentage.
View 9 Replies View RelatedI am using excell 2007 and 2010
I need to find the average numbers on sheet 1 column D. Column D contains numbers as well as Blank cells. The info will be presented on sheet 2 in a monthly view. In other words i need to ssearch awithin a date range and determin the average of those numbers. This works on some months but return a DIV/0 erro most of the time.
=AVERAGEIFS('2N Stats'!$I:$I,'2N Stats'!$E:$E,">=2/1/2011",'2N Stats'!$E:$E,"
I have several worksheets with thousands rows (independent variables) and hundreds columns (all dependent variables). Each line basically gives me hundreds values for each independent variable - see below:
...
C9 39.65 653.95 5.28 163.56 99.56 14.49 ... ...
E9 7535.92 21500.56 2835.88 3122.98 7225.34 5371.25 ... ...
G9 111568 298021 12940 31645 181797 36996 ... ....
...
I need to know how the values in each row are distributed, and I ideally plot a 2D column graph of the distribution. Is there a way to do that and create/program a macro (with relative button on the workboook) that does it automatically once clicked?
Very often there are outlying values (bigger or smaller by a factor of 1000 or even more), mistakes, which I would like to identify and fix possibly
What could be the syntax (in a code) for averaging a range in col C. the range values are given in E3 and E5. (E3=508 & E5=1200) These values changes each time I open the workbook.
View 2 Replies View RelatedI have a spreadsheet that we are using as a Skills Matrix for the team. The area that the team member deals with is listed in Row 3. The scores for each person are recorded in Columns D to M. The process steps being scored against are in Rows 8 to 38, and are seperated by the area of responsibility.
I am trying to work out the average scores of each person depending on their area of responsibility. I can work out the totals easily enough using:
=IF(ISNUMBER(FIND("CM",D3)),SUM(D11,D12,D13,D14,D18,D19,D20,D23,D24,D25,D26,D27,D28,D29,D30),0)
+IF(ISNUMBER(FIND("V",D3)),SUM(D33,D34,D35,D38),0)
But if I use the same for the average scores is works fine until a person covers two duties:
=IF(ISNUMBER(FIND("CM",D3)),AVERAGE(D8:D10,D12:D17,D19:D22,D24:D30),0)
+IF(ISNUMBER(FIND("V",D3)),AVERAGE(D31:D32,D34:D37),0)
This is becuase it adds the 2 averages instead of working out the total average score. Is there an easy way around this?
i.e. For team members who have CM in Row 3 I need an average of certain cells, for team members who have V in Row 3 I need a different average and for those team members with both CM and V I need an overall average.
I have a database with monthly tabs and a summary sheet, on the summary sheet I calculate an average length of stay for each month (tab) but I need to find the yearly average and I don't know if there is a formula that would calculate the true average by using the monthly averages.
View 2 Replies View RelatedI have a table with 9 columns filled by a letter (A, A-, B+, etc). This letters correspond to grades (4, 3.67, 3.33, etc). The tenth column is supposed to contain the numerical average of all 9 letters in the line.
View 9 Replies View RelatedI created a pivot table in which sales amounts are represented and a derived table in which market share percentages are showed. Now i am looking for a formula that is able to calculate average market share values. Depending on which country i selected in the pivot table and which category, the number of active companies are changed. How can i formulate this formula to calculate average market share percentages in a certain period. check out my attached file to clarify the situation.
View 2 Replies View RelatedI am trying to calculate an average of 800 cells, where I would like to exclude a cell when a certain character is in it, namely "<".
View 4 Replies View RelatedI have a set of % score values (e.g. 88%, 94%, 82%, 67%)
I would like to know if there is a relatively simple formula that can be used to calculate the AVERAGE DEVIATION from 100%, as opposed to the AVERAGE DEVIATION from the mean, which is what the excel function =AVEDEV will return.
E.g. The Average Deviation from the mean for the the above set of values is 8.25%, whereas the Average Deviation from 100% is 17.25%
I can create a complex manual formula which sums absolute values of the variation from 100% of each score, and then divides by the count total of the number of scores, however this is flawed for several reasons, including the fact it is very time consuming when used across a large set of scores, and it is corrupted when a non score exists in a cell (some of the 'scores' may be '-', in which case they should be ignored and not included in the calculation of the average deviation from 100% by treating as a 0% score (and a deviation of 100% from 100%).
I have a data set that looks something like the following:
TypeAmountCost
A100$50
A200$40
A300$35
B250$40
B275$55
B500$60
A700$20
B350$25
B450$35
C400$80
C400$60
I am having trouble creating a formula that would calculate the weighted average cost depending on the type (i.e. weighted average cost of $28.84 for A, weighted avg cost of $70 for C).
I am trying to calculate the average Talk Time into Minutes and Seconds. Every time I have tried, I get the wrong average.
For example, if I have a total of 53 calls and the total talk time is 4:19:05, what would the average talk per call be? I tried doing everything in seconds (this would give me a total of 15545 seconds) but the total I was getting is 4:89.
What would the formula be to get the correct average time?
I have a series of numbers in the column AB56 going downwards and in AC56 going downwards their respective counts in a data-set.
What I would like to do is find the average of the min and max, the min and max for the average calculation are both determined by looking at the count column, isolating the max, isolating the min, provided they both have a count greater than zero then averaging both results.
eg.
0.1562818960
0.1558039690
0.1553260431
0.1548481172
0.15437019 2
0.1538922643
0.1534143384
0.1529364124
[code].....
In this calculation the MAX is 0.155326043 because it has a count greater than zero, the MIN would be 0.138120697 as it has a count greater than zero, the average of both would simply be 0.1467. i.e. Average(0.155326043,0.138120697)
I have a one column of numbers...let's say consisting of six rows
the following numbers 12,0,14,25,0,9
if I average this it comes out to 10
how can I average the same rows but exclude the zeros in the outcome(is there a formula to do that?)
I would like the answer to be 15.....I still need the zero's in the rows however
i have data on excel which has the following fields:
Invoice No,Inv.Date,Month,Customer,Part Number,Description,Qty.I want my final pivot table to have the following results for every part no. and description
Total monthly sales
total year sales
and for each model sale calculated as sale quantity / week
I have tried the following way but it did not work:
just next to the pivot table under the heading"AVG" i used the following formula to get the sale/week on cell o6=grand total sale /(applicable no. of weeks).and when i am copying the formula to the cells lying below the formula is being applied as that of the first cell so getting the same result for all the rows.is there a way to calculate sale/ wk within the same table
I want to average the cells in column B but ignore values in the corresponding rows with a 1 or 7 in column C.
This will be used in a years data where 1 - 7 are days of the week and I want to separate weekdays from weekends when calculating an average.