# Calculating The Average Gain & Loss For Numbers In One Column?

Mar 27, 2009

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 ??

## Pivot: Gain/loss Over Time

Feb 12, 2009

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:

## Adding Icon Of Gain Or Loss

Dec 4, 2012

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.

## Find The Loss/gain Difference Between An Initial Value

Aug 27, 2007

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.

## To Calculate That Loss Or Gain As A Percentage Of Their Original Weight

May 1, 2009

I 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?

## Calculating Average Of Numbers Put In One Cell

Feb 13, 2013

I 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.

## Formula For Calculating Win/loss Streaks....

Jul 6, 2004

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?

## Average Column Of Cells But Ignore Errors And Return Average Of Numbers That Are There

Jun 14, 2013

E11 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.

## Calculating Column Average Excluding Blanks?

Apr 26, 2012

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%)

## Calculating The Average Of A Column Based On Multiple Conditions

Feb 21, 2008

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))

## Place Average Of The Five Numbers In Column

Dec 22, 2008

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:.....

## How To Average A Column Of Numbers That Are Greater Than 0

Jul 20, 2006

I am trying to average a column of numbers. I want to only average the cells
that contain a number greater than 0.

## Calculate Average Of 4 Lowest Numbers Out Of Last 7 Entries In Column

Jun 4, 2014

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

## Totalling A Win Loss Column

Sep 27, 2009

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?

## Seeking To Average Numbers In Single Column Broken Into Increments Of 5

Sep 12, 2008

I 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.

## Excel 2007 :: Find Average Numbers On Sheet 1 Column D?

Feb 13, 2012

I 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,"

## Calculating A Rolling Average

Oct 8, 2009

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

## Calculating Average Of A Range

Oct 9, 2009

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.

## Calculating Average Of 2 Averages

Jun 7, 2013

I 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.

## Calculating Average From Averages

Oct 1, 2013

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.

## Calculating An Average From Letters

Feb 11, 2008

I 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.

## Calculating Average Values

Jul 28, 2006

I 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.

## Calculating Average Excluding Cells Containing <?

Jan 26, 2014

I 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 "<".

## Calculating AVERAGE DEVIATION From A SPECIFIED POINT?

Jul 29, 2009

I 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%).

## Calculating Weighted Average With Criteria

Aug 25, 2009

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).

## Formula For Calculating Average Time?

Sep 24, 2010

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?

## Calculating Average Based On Other Criteria?

Feb 27, 2012

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)

## Excluding Zeros When Calculating An Average

May 10, 2007

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

## Calculating Average Using Vlookup Table

Dec 18, 2008

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

## Ignoring Cells While Calculating Average

Oct 18, 2009

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.