Average Column Depending On Values In Another Column

May 17, 2009

I have tried a few other examples in previous threads that use COUNTIF and ISERROR but I just can't get it right.

Basically I want to average the cells in column J (J2:J6,J8:J12,J14:J18, etc).
My aim here is to determine the average value for each day (Averaging 0 values in this instance is OK).

Now, the date in column A (A2:A6,A8:A12,A14:A18, etc) sometimes returns a blank value, depending on the day of the week (see attached example). This is because, in the full version of the workbook, I have Column A date linked to a perpetual calendar and therefore the dates can change cells depending on the year.

For the Date Cells that return a blank value, I do not want the AVERAGE equation to include the 0 value on these days.

For an example I have attached a month of February where there are blank date cells at either end of the column.

View 13 Replies


ADVERTISEMENT

Formula To Calculate Average Values In Column B Where Value In Column A

Mar 11, 2013

I'm currently using this formula to calculate the average values in column B where the value in column A ='s E1 eg:

=AVERAGEIF($A$2:$A$21,E1,B2:B21)

Instead of doing this however, I need the formula to calulate the average from column B where the "Date" in column A ='s the year and month I specify in other cells.

Year value specified in: F1
Month value specified in: G1

View 9 Replies View Related

How To Sum Values In One Column Depending On Top X Values In Another Column

Feb 4, 2012

I have Column A and Column B up to 200 rows of unsorted values. I would like the user to be able to choose X value and this would basically find the top X values in column B, then sum the corresponding adjacent top X values in column A. I tried using the array formula within the SUMIF formula, but it doesn't work.

View 5 Replies View Related

Copy Rows To Other Sheets Depending On Values In Column

Feb 7, 2007

I’ve done a search of the forum for a macro and found some post that seems to be about what I want to do, but unfortunately my minimal vb experience prevents me from adapting them to my requirements.

I‘ve got a workbook with three sheets; say Sheet A, Sheet B and Sheet C. I want the info in Sheet C copied to either Sheet A or B depending on the info in cells in Column A of Sheet C.

Sheet C contains customer info, there are about 9 column headings and up to 30 000 rows (Individual customers).

The cells in Column A will contain a number between 0 - 23. What I want the macro to do is, if a cell in Column A contains any of these numbers, 0, 1, 2, 6,7,8,9,10,17,19,20,21,22,23, I want that whole row copied to Sheet A and if it contains 3,4,5,11,12,13,14,15,16,18, I want it copied to Sheet B. The cells will only contain one number, never a combination.

View 3 Replies View Related

Average Of Values In A Column

May 22, 2014

I have a column with values from P2 to P3077. I need a formula that would average values from P2:P6, P7:P11, P12:P16 and so on till the end (that is 5 data points each time till the end).

View 2 Replies View Related

Average Values If Equal To A Value In Another Column

Jun 3, 2014

I have a table full of phone numbers and call durations.

In A2:A6160, I have 162 different phone numbers and in U2:U6160, I have the call durations for calls made to those numbers.

In a separate spreadsheet, I have each number listed in Column A and in Column D, I want to display the average call duration for each of the numbers.

View 3 Replies View Related

Formula: Average Value Of Cells Left Of Highest N Values In Above Column Range

Sep 24, 2006

I need a cell formula that will (a) identify the highest N values in an above specified column range, (b) color the interior of those N cells (I suspect that this is not possible), and most importantly (c) return the average value of N corresponding cells, where the corresponding cells are located on the same rows as the identified N high value cells but in a specified column to the left (not necessarily adjacent)

Does anybody know what this formula would look like?

Example:

-----------------------
...| A | B | C | D | E | F |
-------------------------
1 |....| * |.........| 7 |...
-------------------------
2 |....................| 2 |...
-------------------------
3 |....| * |.........| 6 |...
-------------------------
4 |....................| 1 |...
-------------------------
5 |....| * |.........| 5 |...
-------------------------
6 |....................| ? |...
-------------------------

? = average of B1,B3,B5 where (N = 3) and (specified column to the left = B)

View 4 Replies View Related

Copy And Paste Unique Values From Column And Use Countif Function To Calculate Average

Jan 19, 2013

I'm stuck on the final piece of my macro project. I've attached a workbook with two sheets: sheet 1 is what I currently have, and sheet 2 is what I'd like my report to look like when complete.

On Sheet 1:
- column B is called Supervisor Name
- Columns D-O are months of the year, with either a Yes or No in each cell.

I'd like to write a macro that will:
- Take all unique values in Supervisor Name column, and paste these Above the current table.
- For each Supervisor, and each month, I'd like it to calculate, as a %, the number of Yes mentions in each month divided by the total cells (Yes/(Yes+No)).

Sheet 2 contains the output, in the format I'd like to see it.

Sheet 2:
Supervisor Name
October
November
December

Supervisor 1
8.3%

[Code] .......

View 7 Replies View Related

Calculate The Average Of A Group Cells In One Column Based On The Condition Of Another Column

Oct 2, 2008

I'm trying to figure out if there is a formula I could use that will calculate the average of a group cells in one column based on the condition of another column. It's hard to explain, so I will show an example. All the data is on a one worksheet and I'm trying to show totals and averages on another worksheet. Location, Days

17, 4
17, 3
17, 5
26, 4
26, 8
26, 10
26, 7

On a different worksheet I would want to know what the average days are for each location. So is there a formula that I could use that will look at column A for a specified location number and then average all the days in column B for that location? I'm using Excel 2003 and have tried using the Average(if) but with no success.

View 2 Replies View Related

Calculate Average For Column Based On Moving Start Column?

May 3, 2013

I have a pivot that includes customer data and I need to create an average for each row based on the first populated column for each customer. After one month of units appears for the customer, I need to calculate the average going forward, and if there are blanks after that they should be treated as 0 in the average. Below is an example of my description of the pivot.

Jan Feb Mar Apr
Customer #1 1 1 3
Customer #2 1 3
Customer #3 2 2

So, the average for customer #1 would be calculated from Column B to Column E, and factor column D as a 0 in that calculation. Customer 2's average would start in Column C and go to Column E, and factor column E as a 0 in that calculation. Customer 3's average would start in column D to column E.

View 5 Replies View Related

Lookup/Match: Compare A1 For The Values In Column B, Then Return The Corresponding Cell (column C) In Column D

Jan 31, 2008

I've been searching the forums for this problem but I can't seem to find any answers. Anyway, this is the problem. See screenshot.

I want to compare A1 for the values in column B, then return the corresponding cell (column C) in column D.
e.g. D1 = 2, D2 = 1, D3 = 4, D4 = 5 and D5 = 3.

View 9 Replies View Related

Minimum Value In Column F Depending On Value In Column B?

May 25, 2014

I need command that takes in consideration values from column F only if in column B is value 4.8. And than, I need minimum value of those taken in consideration .d.png here is attached picture regarding my problem.

View 8 Replies View Related

Average From One Column Based Upon Criteria From An Adjacent Column

Jan 13, 2008

I am trying to get an average from one column based upon criteria from an adjacent column. The number of days to close a case for race columns Black and White are listed in B5:C16 and E5:F16 and H5:I16. I need a formula to calculate the average days taken to close cases for Males and then the same for Females. Sample below: ...

View 12 Replies View Related

Tally Report Of X: Count On Column B (project $) Depending On The Criteria On Column A (project Description)

Aug 28, 2007

I need to write a couple of functions. Firstly I want to do a count on column B (project $) depending on the criteria on column A (project description). For example a total count of all values in col B if project description is "P" or "A". [I can do sumif functions but this is a countif and I cannot get this right].

View 4 Replies View Related

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.

View 14 Replies View Related

Count Values Of Column If Column Header Is Equal To Name In Column A?

Apr 25, 2014

For column "B" count all the 1's if column header is equal to name in column A.

For Column "C" needs to be checked if a value was filled in column "I" if yes then check if in column "L" has a value, count all these values.

View 5 Replies View Related

Average One Column Based On Another Column

Dec 14, 2006

I am trying to calculate averages for a ratio I have calculated. Is there a way I can use VBA or Excel functions to average the G column depending on the value of the D column? In other words, find the average value of column G for all Industry Group = 103. Then, find the average value of column G for all Industry Group = 106. Then put industry group # in column H and avg. value in column I.

View 2 Replies View Related

VBA - Generate Random Column Values From Existing Column Values?

Aug 22, 2013

[URL]

I have Column C and D filled with numeric temperature and humidity data. I need next 16 rows (8 more set) to be filled with randomly generated dummy data by the values exist in C and D. Same numbers but randomly distributed.

View 2 Replies View Related

Extract Largest Values From Column B And Corresponding Text Values In Column A?

Oct 1, 2013

There should be a formula to extract the largest values and its corresponding values. For example, if column

Products Costs
A1 Pen 200
A2 Pencil 125
A3 Radio 670
A4 Apple 1500
A5 Xbox 222
A6 TV 100

The desired outcome

Products Cost
Apple 1500
Radio 670
Xbox 222
Pen 200
Pencil 125
TV 100

The second goal is extracting the top 3 largest values using a formula.

Sample file is attached for your convenience. Desired outcome is in GREEN.

View 6 Replies View Related

Calculate Mean Of Column B Values Based On Date Values In Column A?

Dec 5, 2013

Below the two columns represent Column A (date and time) and Column B is temperature.

How can I take the mean of all temperature values for May 1, then take the mean of all temperature values for May 2, etc.?

5/1/2013 0:0012.4
5/1/2013 1:0012.4
5/1/2013 2:0012.5
5/1/2013 3:0012.5
5/1/2013 4:0012.5
5/1/2013 5:0012.4
5/1/2013 6:0012.4

View 2 Replies View Related

Summing Up Values In Column Corresponding To Unique Distinct Values In Another Column?

Dec 24, 2013

I have two columns in excel, col A and col B.What I would want to achieve is following:

Col A Col B
A 2
A 2
A 2
B 3
B 3
B 3
B 3
C 3
C 3
C 3

Now I want a total of values in column B pertaining to unique (or say 1st occurences) of values in Col A. So in this case the output should be 8 ,Achieved as follows(2 +3+3).

[URL]

View 5 Replies View Related

Counting Values Of One Column Based On The Uniqueness Of Values In Another Column

Jun 11, 2014

Here is a table

Col1 Col2 Col3 Col4
8821015m14:44.0E__uMail
9266321m34:13.0E__uMail
92677165m23:43.0E__uMail
9309415m07:24.0E__uMail

[Code] .....

Here is some context

Each one of these rows represents an entry into a ticket in our incident management system. Col4 represents the method by which the incident was reported to us (Email, Voicemail, Walk In, Ask IT). A ticket can be open and closed with a single entry if the issue is easily solved or it can have many entries if the issue is complex and requires more troubleshooting or escalation to a higher tier of support. I need to determine the number of unique tickets submitted by each reporting avenue (Email, Voicemail, Walk In, Ask IT).

In this table the number of tickets that were:

submitted via Email is 14
submitted via Voicemail is 1
submitted via Walk In is 2
submitted via Ask IT is 1

Col1 Col2 Col3 Col4
8821015m14:44.0E__uMail = 1
9266321m34:13.0E__uMail = 2
92677165m23:43.0E__uMail = 3
9309415m07:24.0E__uMail = 4

[Code] ....

What I need is a formula that will produce this result.

View 5 Replies View Related

Set Range Depending On Value In Column D

Oct 29, 2007

I have a list of data in colum A and I want to group them together.

In column D I number the group with the same number.

A1 = Tony D1 = 1
A2 = Tom D2 = 1
A3 = Bill D3 = 1

A4 = Helen D4 = 2
A5 = Marg D5 = 2
A6 = Kathy D6 = 2
A7 = Catherine D7 = 2

And so on..... The list up to 100 names

As you can see group 1 = 3 names, group 2 = 4 names, group 3 = 6 or maybe 7 and so on.

I'm trying to set ranges for them Eg:
set rng1 = A1:A3
set rng2 = A4:A7

but it not fixed to set ranges like that. It's variable and changes every week. Next week maybe rng1 = A1:A5, rng2 = A6:A9.

can I have the VB code to determine these variable ranges.

View 9 Replies View Related

Sum Of A Column Depending On Data In Another Cell

Jan 7, 2009

I want to add up items in column H, but only if Column J says "yes" in the same row.

I tried a few methods as I always try to do, but I came up short with this one.

View 2 Replies View Related

Summe Column Depending Of Other Columns

May 15, 2007

I want to create a macro that make the somme of 2 other columns when the nummer of the first column ist double(more than one ) and put the somme in a new Rows. like this :

A B
23 5
23 12
23 2
45 4
45 1
46 3 in the news the resultat in the same columns it´s like this
A B
23 19
45 5
46 3

View 10 Replies View Related

Select Range Depending On A Column

Mar 4, 2009

If i have row 10-20 in column A filled, what vba code would i need to select a range in column C that selects rows 10-20

If that 10-20 rows in column A chnages to say 10-50 the vba code will select 10-50 in column C

View 10 Replies View Related

How To Sum Amounts In Column Depending On Currency

Dec 19, 2013

I am creating jet report, however am having to use Excel formula to create totals.

I am trying to sum the amounts in column I depending on the currency, although as it's a jet report it needs to update when the report is run.

My formula: =SUMPRODUCT(SUBTOTAL(9,OFFSET(I4,ROW(I4:I6)-ROW(I4),0)),(F4:F6=Options!B4)+0)

View 2 Replies View Related

Data Validation Depending On Column

Feb 22, 2007

Let's say I have data validation on column A, which only allows 2 values, "Number" and "Letter". Easy enough. If the value "Number" is selected in A1, when the user moves to B1, I want a validation list of 1,2 and 3, when they move to C1, I want a list of 4, 5 and 6 and when they move to D1, a list of 7, 8 and 9. However, if "Letter" is selected in A1, when the user moves to B1, I want a list of R, S and T, when they move to C1, U, V and W, and when they move to D1, a list of X, Y and Z. I tried using dependant validation, but can only get all three columns (B, C and D) to either be 1, 2 and 3, or R, S and T. The validation lists would be existing named ranges on a separate sheet.

View 2 Replies View Related

Add Or Subtract Depending On Text Value In Another Column

May 3, 2007

I have a workbook to record the attendance of students. Sheet attendance9 for September's attendance. Enter 1 for present, 0 for absent, L for late, E for early leave and blank cell for no schoolday. Sheet "remark9" is used to record detail of attendance for September. In sheet "attendance9", I want to display in cell AT2 the number of times a student absent for 7 or 14 consecutive days. How to count from E2:AH2?

View 6 Replies View Related

Multicolum Listbox - Column 3 Changes Depending On ComboBox?

Aug 19, 2012

I have a multicolumn (3 columns) listbox that is filled from a sheet (ProductList). Col1=product code (column A); Col2=description (column B); Col3=prices (columnC). I fill it when the form loads like this:

VB:
Dim rng As Range
With Worksheets("ProductList")
Set rng = .Range(.Range("A2"), _

[Code]....

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved