# Average Formula In Excel With Even Columns Only And Columns With No Value?

Apr 26, 2014
i am trying to complete a spreadsheet which most of it is done but one column i have i have to average everyother square in each column which i did but when i total those columns at the end i get the dreaded #div/01 which means any value that i dont have in a particular column it will not add is there anyway to fix this. i am attaching a sample of the spreadsheet so you can see exactly what i am trying to do .

View 6 Replies
ADVERTISEMENT
Feb 1, 2010

Looking for an average formula.

All found formulas average within a string (ex. A1:F1).

My spreadsheet contains a series of three columns.

Example:

Column A reflects a number stat for a month (January).

Column B reflects another number stat for the same month (January)

Column C reflects the difference between Columns A & B.

Column D reflects a number stat for the next month (February)

Column E reflects another number stat for the same month (February)

Column F reflects the difference between Columns D & E.

and so on.

I would like to average the rows stats reflected in Column A, D, G, J, and so on.

Also, I would like to average the rows stats reflected in Column B, E, H, K, and so on.

Also, I would like to average the rows stats reflected in Column C, F, I, L, and so on.

View 9 Replies
View Related
May 4, 2013

I would like to take the data from worksheet1 and put into worksheet2 but limit the length of a list (the real spread sheet has over 100 rows and i would like them in 4 sets of 25 versus the example I provided). Is there an array or macro that would make this work (keeping the formatting)..

Excel 2007

B

C

3

Name

Company

[Code].....

View 9 Replies
View Related
Apr 9, 2014

how to set one entire columns text to two different colors based on another columns values. So for example I have column A and B. Column A has two values called Internal and External. Column B is a title table so the entire column is just titles. We'll say it goes for 20 rows if you need a row count. What I am looking to do change the text in Column B to Red for External and Blue for Internal. I tried the conditional formatting and I just can't seem to find the right option.

I'm using Win 8.1, Office 2013.

View 4 Replies
View Related
Oct 13, 2008

I'm trying to compare values in 2 separate columns to see how many times the same value appears in both columns. Ideally I would be able to insert a range function to compare the values in the column "ID 1" against the values in column "ID 2" and return the count of times that a value appears in both columns. For example 2122, 1112 and 1718 appear in both columns and I would like the formula to return a count of 3.

ID 1ID 2

12342122

45671112

89101718

11122678

13144544

15162324

17189987

19201215

21221928

1976

2576

2345

4678

In my actual project I'm comparing 2 columns in the same worksheet. The column are column B with data in cells B2:B10266 against column C with data in cells C2:C18560.

View 4 Replies
View Related
Nov 15, 2009

How do you make the Autofill function increment in blocks, e.g. instead of increment 1,2,3 increment 1,8,15 inthe following example: I have a 2 worksheets, one with days of the year across columns and values down rows, and another that summarises the sum of the corresponding days in weeks, so I have 365 columns in sheet 1 and 52 columns in sheet2. Everytime I update the sum range i cannot autofill it in the summary worksheet, as A1:G1 autofills to B1:H1, but I want it to increment to H1:N1.

View 3 Replies
View Related
Apr 10, 2014

I am having data of codes in a and c column and the amount for the same code in b and d column. I want the sum of amount of codes in the two columns b and d, if the same code matches in the a and c column .

I am enclosing the excel file. Why a formula contains those things.

sumif.xlsx

View 14 Replies
View Related
Jan 14, 2014

I have multiple columns in excel that contains values like this

A B C D E F G H

TrxVolTrxVolTrxVol Trx Count Vol Count

122001400013500

125031290012499

130001300012700

Now at the last columns G & H i want to get the result that how many column of title "Trx" are having values greater than or equal to 1 and how many columns of title "Vol" are having values greater than 2500 respectively.....

View 1 Replies
View Related
Sep 25, 2012

I am looking to place a formula which will look like this:

=min("A2:?2"), where ? depends on "number of columns". If number of columns is 4, then it should be D, E or Z. Number of columns is specified as an integer in range("B2").

I do not want to do it in a macro, want to do it in the formula.

I have done this before, but can't quite remember. I have tried:

=min(range(cells(1,2),cells(1,2+B2))), but does not work.

View 2 Replies
View Related
Jan 24, 2014

I have data from Row A1 to A3 with following names, I need formula (Dont want to use text to column or VBA code)

Atif Malik Khan

Khalid feroz sahb

Ali ahmed khawaja

I want that 1st, 2nd and 3rd name should be shown in Column B, C, and D.

View 1 Replies
View Related
Aug 27, 2012

I need to copy a specific column range K18:M180 to only columns that have an "X" on a specific row. Below is my small example.

In this example it would be in columns OPQ and WXY

Excel 2007IJKLMNOPQRSTUVWXY

121314xxxxxx1516$Units$/Units$Units$/Units$Units$/Units$Units$/Units

1718aaa180.0 0.00 19bbb0.0 0.00 20ccc1.6 0.11 14.74 21

ddd9.0 0.53 16.93 22eee7.3 0.45 16.22 232425Sheet1

View 1 Replies
View Related
Jan 31, 2013

I have a large spreadsheet converted from pdf whose data still appears in A4 reading format.

I need to move part columns of data from 6 columns to form 1 large column in column A.

For example, move range B8 to B76 beneath range A8 to A76 and range C8 to C76 beneath that etc, page by page working through all 270 pages !

Also need to delete unnecessary 'page headers' throughout as in rows 2-6

View 2 Replies
View Related
Nov 10, 2009

I have twelve columns of data nine of which are filled. I'm trying to come up with a formula that averages a column only if it has data in it, I have attached a sample file. As I add data the average should change without changing the formula.

View 2 Replies
View Related
Jul 30, 2009

I have data that I need averaged. The problem is that my data is taken into chunks of time, and I want to average each individual column after each break of time. My data looks like this: ...

View 6 Replies
View Related
Jul 25, 2007

I am calculating the average for a range of cells in a row. =AVERAGE(D7:P7). I need to hide some of the columns and calculate the average based only on the visible columns. How can I do that?

View 5 Replies
View Related
Mar 11, 2008

Is there a way to loop through a range of numbers, pick the first 24 columns, do some kind of caclulation for each column and row, the start again on column 25 for another 24 columns, perform the same calculation, go to the next set of 24 columns, etc?

I have tried using, for example, AVERAGE(OFFSET(range,0,0,24,24) and that correctly returns the average for the first 24 columns (range being the range of data I am using like D366:HK389). However, unless I manually change the starting column for the next set, it doesn't return the correct result. I have attached a sample data spreadsheet. There are 9 Locations in the spreadsheet where each Location contains a 24x24 matrix.

View 4 Replies
View Related
Jul 1, 2014

I have a pivot table showing actual and forecasted sales per month for diffrent regions/sales channels/segment.

I would like to calculate the average monthly sale, for forecasted and actual data, within the pivot instead of doing it outside in columns to the right of the pivot table.

View 1 Replies
View Related
Jan 18, 2014

I am trying to get the conditional average of 2 data columns.

data_1 category data_2 category

2.5 Yes 1.2 No

4.2 No 2.6 No

3.5 Yes 3.9 Yes

4.1 No 2.5 Yes

What I need is the average across the 2 data columns (data_1 and data_2) for category Yes and category No. I know I can use averageif to get the conditional average of 1 column, but that doesn't work with 2.

View 5 Replies
View Related
Jul 29, 2014

I would like to average a column based upon another column value. Namely, if one column is a duplicate, I would like to take the column to its right value and average all the duplicates. For example

comp 1409 99.7

comp 1409 99.5

comp 1409 100

comp 1308 65.7

comp 1509 99.5

I would like that to become

comp 1409 99.73

comp 1308 65.7

comp 1509 99.5

View 6 Replies
View Related
Oct 7, 2008

how to calculate a six month rolling average for hours in AD column and AE column, this average would be entered in to AF1. the date column is AC the date is not consecutive it does miss days out.

View 14 Replies
View Related
May 4, 2009

I am trying to determine the average reduction amount of appraised property values. I have two columns in my spreadsheet. Column H has appraised value of property. Column I has the accepted value of the property. Sometimes the accepted value is the same as the appraised value, sometimes it is reduced, and sometimes it is rejected.

I want to be able to find the average reduction amount when the accepted value is less than the appraised value and is not rejected.

Column H always shows a numerical value (i.e. $250,000), but Column I may have a numerical value or show "rejected".

View 9 Replies
View Related
Dec 30, 2008

I am trying to include numbers in an average if two separate columns prove true. For example, I want to include all numbers in column "C" in the average if the cell in column "A" matches a name and the cell in column "B" is equal to the number 1.

View 2 Replies
View Related
Jul 26, 2006

How do I get the average sale if column N=sale amount if ...

View 9 Replies
View Related
Oct 4, 2008

I have 10 sheets in my workbook that represent 10 employees. Column G, rows 5 through 739, represent the amount of time each employee spends doing a certain task for each day of the year. All these cells have zeros in them until a we come to that day and a time is entered for that day. The result in each cell is a certain amount of minutes spent on that task.

I can average each employees time individually on it's own sheet with this formula: {=AVERAGE(IF(G5:G739=0,"",G5:G739))}, but what I'm having a problem doing is averaging all 10 employees together on a separate sheet. I'm trying to figure out company wide how much time is spent on this particular task each day.

My depth of understanding here isn't too great, I'm learning as I go. I think I have to incorporate that formula while also selecting each sheet. I'm starting to get the BSOD in my head with this one.

View 3 Replies
View Related
Aug 3, 2013

I am trying to find the average of a cell with a name in that may appear several times in various columns. I then need to find the average of the value against that name in all the columns that it occurs in. Example:

a

b

c

d

e

f

g

h

i

j

k

Aldridge Sean

2

3

3

Andrew Rob

4

3

3

Aldridge Sean

3

2

2

[Code] .....

In the above example I want to find the average score for aldridge sean in column b, f and j.

View 9 Replies
View Related
Oct 19, 2006

I have two columns:

A B

1 10

2 9

3 8

4 7

5 6

6 5

7 4

8 3

9 2

10 1

I need a formula that takes the difference of the two columns (1-10, 2-9, etc.) and then finds the average for all of the differences. I don't want to have a third column with the differences and then take the average of those.

View 9 Replies
View Related
Jul 19, 2007

I have three columns in my worksheet.

Column 1 reports the name of three people, John , George and Jim, column 2 the year for which they are paid (e.g. 2002, 2003 etc) and column 3 the amount of money they received each year (e.g. John received 18$ in 2005, $15 in 2004 and 11$ in 2003). I need a function to give me the average salary earned by each of these people in the period 2003-2004 e.g. John earned $15 in 2004 and $11 in 2003, so the average value is 13. Is there a function to do it automatically (so taking the conditional average---average for john given that year is 2004 or 2005)?

I want this average to be in column 5 (column 4 will contain the names john, george and jim)

View 9 Replies
View Related
Feb 18, 2008

I have data in 3 different columns (A, C, and E) to name a few. I want to average each of these columns, but if any of them include zero values, I want to exclude that from my calculation.

Example:

Column A = 10

Column B = 0

Column C = 3

Right now, my "average" formula, is showing the average as 4.33. (average a5, b5, c5)

The real average I'm looking for is 6.5. What is the best way to setup my formula?

View 9 Replies
View Related
Feb 12, 2007

code for an assignment, I have a table of values, the 5 colums are arrays, one with 20 student names and the other 4 are grades for 4 subjects, each colum being one subject.

I need code to find the average of each student and get it to display in a separate colum and averages for each subject also getting it to display in another row.

View 9 Replies
View Related
Apr 20, 2007

I have a file that I want Column D to average by column A and C. I also want to ignore any rows that dont have data in column B in the average. See sample file.

View 5 Replies
View Related