Average With Multiple Conditions, Including Dates

Aug 20, 2008

My spreadsheet is too large to attach so I'll describe it as best I can. I have names in column H, which are repeated irregularly. I have corresponding grade numbers (1-5) in column I, and I have the dates they were entered in column J. It looks something like this:

Column H Column I Column J
Name 1 4 8/10/08
Name 2 3.5 8/11/08
Name 1 5 8/11/08
Name 5 4 8/12/08
Etc....

I want to average the grades (column I) for a certain name (column H) within the last 30 days, or however many days I enter. I'm using Excel 2003 with the analysis add-in. I can use SUMIF to include any two columns, but how can I write an average grade formula using both the name and date conditions? Dan Auto Merged Post Until 24 Hrs Passes;Come to think of it, all I need to do is sum the data. I have a denominator in a different box that I can use to average it. So in effect, I'm trying to creat a SUMIFS function.

View 3 Replies


ADVERTISEMENT

Average Elapsed Time With Conditions Using Dates & Numbers

Mar 5, 2009

Dataset is 220K rows. A small subset follows.

Using an array calc with criterions Day = 1 and Hour = 1 summed on Elapsed Time returns 81 with an average of 11.57 for 7 events of which there are 5 distinct dates.

The function =SUM(IF(D2:D14<>"",1/COUNTIF(D2:D14,D2:D14))) returns the number of distinct dates in the dataset ie 7.

Is it possible to combine these two methods to create an average of elapsed time by day and hour using a denominator of distinct dates for only that combination of dimensions?

I have attempted to calculate an item in a pivot table using a different denominator without success.

day Hour Date Elapsed Time
1 0 5/03/2009 25
1 0 5/03/2009 24
1 1 5/03/2009 23
1 1 7/03/2009 11
1 0 7/03/2009 5
1 1 8/03/2009 2
1 1 8/03/2009 8
1 1 9/03/2009 4
1 1 9/03/2009 17
1 0 9/03/2009 22
1 1 10/03/2009 16
1 2 13/03/2009 12
2 1 14/03/2009 15

View 4 Replies View Related

Sumproduct - Average With Multiple Conditions?

May 7, 2012

I need to average with multiple conditions. Is there a way for sumproduct to do such a thing? How to average with conditions?

View 3 Replies View Related

Average Based On Multiple Conditions

Dec 1, 2009

Im currently trying to report on cycle times for a certain task that is carried out on a daily basis. I have used NETWORKDAYS to calc each rows cycle time in Cell R14:R512 the code is:-

=IF((NETWORKDAYS(O14,N14)-1)*($AH$11-$AH$10)+IF(NETWORKDAYS(N14,N14),MEDIAN(MOD(N14,1),$AH$11,$AH$10),$AH$11)-MEDIAN(NETWORKDAYS(O14,O14)*MOD(O14,1),$AH$11,$AH$10)=0,"",(NETWORKDAYS(O14,N14)-1)*($AH$11-$AH$10)+IF(NETWORKDAYS(N14,N14),MEDIAN(MOD(N14,1),$AH$11,$AH$10),$AH$11)-MEDIAN(NETWORKDAYS(O14,O14)*MOD(O14,1),$AH$11,$AH$10))

This is returning the cycle time providing both the start date/time and end date/time are filled in, or it returns blank if nothing has been filled in. I have then tried to calc the average overall cycle time in Cell R12 by using the below formula = SUM(R14:R513) / (COUNT(R14:R513) - COUNTIF(R14:R513,0))

This seems to be working proved i have two rows of data filled in. My problem lies when all the cells are blank, it then returns the DIV error. I have tried using an if statement to ignore if the cell is blank but it still returns the DIV error. Im really at a loss on this and now im not even sure if i have gone about this in the right way or if there is a better way to approach the whole task? I have attached a sample workbook to try and better explain.

View 4 Replies View Related

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

View 9 Replies View Related

Multiple Conditions For If Statement For Past And Future Dates

Feb 14, 2014

I need creating a formula that combines the following if statements in cell C107:

=IF(AND(B107

View 1 Replies View Related

How To Get Average Of Dates Across Multiple Rows

Mar 4, 2013

70 rows, Col A is a start date, Col B is an end date

What I want in Col B, row 71 is the average elapsed time between start and end dates...

View 2 Replies View Related

How Do I Get An Average Without Including The Zeros

Jan 27, 2006

I am using a formula to include all values greater than zero in my average which has worked fine until i tried to extend the parameters, then it gives me the Value? sign. Does anyone know another way to accomplish what i want?

here is what i am using now.
=average(if(BZ28:CM28<>0, BZ28:CM28, " "))

i was trying to make the CM extend to CS, but for some reason it is not working.

Anyway, i figure a different equation might be better than what i
have and solve my problem.

View 10 Replies View Related

Sum & Average Including Zeros But Not Blanks

Aug 14, 2007

Attached is an xls with my formulas and problem. We need a way to factor in zeros in grading student workers. However, we also need a way to omit blank or null cells if the workers did not do a particular project. The formulas currently in the sheet compute zeros for both scenarios, lowering the overall 'grade' for workers who didn't do a project compared with workers who did the project but got a '0'

View 8 Replies View Related

Average Including Zero After Paste Link Transpose?

Feb 15, 2012

Average formula that will give an accurate answer. My issue is that I need an average of what has been entered rather than for the entire selection. I have paste link transposed the data as it needs to run on Excel 2003 which can only hold 30 items in the Average(number1, number 2 ... etc) and I need 52. My problem occurs when the data is paste linked all blank cells appear as a Zero therefore increasing the count of the average and providing a wrong answer.

Eg when (1, 2, 0, 3) Average = 1.5 is entered the formula works out (1, 2, 0, 3, 0, 0, 0, 0, 0, 0, etc) Average = 0.12 because it divides by 52 instead of 4. As you can see I can't ignore zeros as they are a possible input. What would be perfect is for the paste link to paste the blank cells instead of the providing a zero then the plan =Average(B3:B55) would be fine.

(Note: the new entered data is on the same row but different columns hence the reason for the paste link transpose to get all the data in one column)

View 2 Replies View Related

Get Average Of Last 6 Data Entries Not Including Blanks Or Zeroes

Jul 22, 2014

I am trying to get the average of the last 6 data entries not including blanks or zeroes.

Currently I have:

{=AVERAGE(INDEX(C2:CP2,LARGE(IF(C2:CP2<>"",COLUMN(C2:CP2)-COLUMN(C2)+1),6)):CP2)}

Which works fine when I've got 6 or more values in the row.

But if there are less that 6 values in the row it returns with #NUM!. Is there a way to add an IF ERROR THEN make = to AVERAGE of C2:CP2?

View 3 Replies View Related

IF Formula - Get Average Of Only Values In That Range Not Including Blanks

Nov 21, 2011

I am looking for a formula for the following:

If there is nothing is U6:W6 return blank

Otherwise sum the contents of Bf6:BH6 and divide by the number of cells that are not empty in the range U6:W6 (to get an average of only the values in that range not including blanks).

View 3 Replies View Related

Average Response Time Including Core Hours Only

Sep 19, 2012

I need to calculate average response time, the problem is that it should be based only on "core hours" and that's between 7am - 6pm.

How to create function that will exclude "out ot hours" time (6pm-7am) from the calculation.

View 3 Replies View Related

Calculate Working Hours Between 2 Dates Including Saturday?

Jun 17, 2008

I need to calculate the total WORK-hours (09:00-17:30) between two date/time-stamps;

Including Saturdays but excluding Sundays & Holidays.

I can get this working excluding Saturdays (formula below) using NETWORKDAYS however the call centre work six days a week Mon-Sat.

A1=dd/mm/yyyy/hh:mm
A2=dd/mm/yyyy/hh:mm [code]....

View 9 Replies View Related

Calculate Working Hours Between 2 Dates Including Saturday

Jun 17, 2008

I need to calculate the total WORK-hours (09:00-17:30) between two
date/time-stamps;
Including Saturdays but excluding Sundays & Holidays.

I can get this working excluding Saturdays (formula below) using NETWORKDAYS however
the call centre work six days a week Mon-Sat.

A1=dd/mm/yyyy/hh:mm
A2=dd/mm/yyyy/hh:mm

DayEnd= 17:30:00
DayStart= 09:00:00
HolidayList= "Currently Blank"

=(NETWORKDAYS(A1,A2,HolidayList)-1)*(DayEnd-DayStart)+MOD(A2,1)-
MOD(A1,1)

View 6 Replies View Related

Number Of Working Hours Between 2 Dates Including Start And Finish Times

Mar 22, 2007

I have 2 dates+ times.

1 the contract start date and time.

2 the contract end date and time.

Can I calculate the number of working hours between these two using a formula?
So if I have a 10 hour working day (08:00-18:00), Mon-Fri and the two Dates/Times can I calculate the number of working hours?

Example
Start 06-Mar-07 10:00
End 14-Mar-07 14:00

This is 2 part days and over a weekend.

View 9 Replies View Related

Average For 3 Conditions

May 12, 2008

I need your expert advise on how i can get the results i need. What i'm trying to do is get the average number of days given that 3 conditions are met.

1. Month Received
2. Status
3. Account

So here are the things i did

1. Named Ranges
ACCT for the Account
MR for the Month
STATUS for the status
TATQ for the no of days

2. I created a validation list in specific cells for the condition

a5 to a12 are the list of accounts
b2 for the month
d2 for the status

3. used the formula...........

View 7 Replies View Related

Average Based On Conditions?

Jul 1, 2014

I have attached a file where there is information in the sheet Weekly with the first row showing the same month for several weeks. add a formula in the sheet Monthly which would calculate average for each month based on the first row in Weekly sheet. Plus the grouping in the Weekly sheet is done by shops and in the Monthly sheet by food, which would I imagine make the formula more complex.

View 5 Replies View Related

Finding Average With Few Conditions

Nov 20, 2009

I am using EXCEL 2003. I am to find Average using few conditions. I have explained the requirement in the sample workbook.

View 7 Replies View Related

Return An Average Value If Two Conditions Are Met

Mar 24, 2009

I'm trying to create a formula that that returns an average value if two conditions are met. No luck here.

I need to be able to get to - Brand = Blue; Value = 2; X Count = average - from the following information. The average is 17.5

BrandValueX CountY CountZ CountGreen1213152Green1405595Blue3107686Red1164965Green21098108Green33077107Blue21796113Blue13970109Red3144963Blue1107787Red31096106Red24663109Blue2165773Red21888106Green31910

View 9 Replies View Related

Average Range By Conditions

Dec 6, 2007

I have written a function below which is supposed to give average of selected range and will ignore #N/A, if any. Naturally, i use array formula:

Function m_avg(mRange As Range)

m_avg.FormulaArray = "=AVERAGE(IF(ISNUMBER(mRange),mRange))"

End Function

the above doesnt work and gives me #VALUE!.

View 3 Replies View Related

Average Of Last X Cells With Conditions/Criteria

May 24, 2008

I have an excel sheet with different data columns (Column A = date, Column B = Production line, column C = total production of the line for the month, column D = complaints per milion). Within the same sheet Columns E through L have formulas and fucntions that uses Columns A thru D for calculation. Also every month a new row is added to the sheet, populating of course Columns, A, B, C and D and the other columns E thru L are populated automaticaly with the functions/formulas i have in place.

My question is within the same sheet I have 4 fixed cells I2, J2, K2 and J1. J2 and K2 depend on I2 and K2 values. Since my date changes every month (the inserion of new rows). I would like if someone can help me in how those cells I2 and K2 can be updated when i enter a value in Column C/ cell?? (??=next blank cell). I2 and K2 calucalates the averages of the last new 17 cells of columns H and L respectively.

So all i need is that cell I2 and K2 be updated automatically as soon as i add the new value in column C Example

This month
C22 = 12345 I2=Average(H5:H22) and K2=Average(L5:L22)
Next Month
C23=78901 I2=Average(H6:H23) and K2=Average(L6:L23)

So I type the input in column C everything is updated automatically. Again i dont know if i can do that with the if/offset, etc or if i need a macro.

View 5 Replies View Related

SumIF With Dates: SUM All Expenses After The Posted Date Including That Date

Feb 13, 2009

In the expense log, Column C is a list of Dates and Column I is a list of expenses. I want to Sum the expenses in the 'Expense Log 09' to a new sheet based on a Date entered in H24 on the new sheet. I have tried the formula as shown below and Get the result #NAME?

=SUM(IF(Expense Log 'Expenses Log 09'!C8:C100,H24,'Expenses Log 09'!I8:I100)). I would Like to SUM all expenses After the posted date including that date.

View 5 Replies View Related

Formula To Work Out Cost Including Multiple Discounts

Mar 31, 2014

complicated formula for working out prices including applying discounts.

I'm looking for the correct formula for G6,G8,G10 and G15,G17 and G19

I think I have them correct(?) to include standard days and extended days but I can't make it work to include Half days. The formula gets too complicated for me to follow.

View 2 Replies View Related

SUMIFS Multiple Criteria Including Blank Cell

Mar 13, 2013

I am trying to create a SUMIFS function to sum estimated hours for specific systems that do not have a Completed date. I do not want to sum remaining hours if it has been completed. i.e.

=SUMIFS(Hours, System#,"110-000-00, Complete (Date), ISBLANK)

System #
HOURS
Complete (Date)

110-000-00
0.5

210-000-00
0.5

[Code] .........

View 7 Replies View Related

Index With Multiple Criteria Including Date Range

Dec 2, 2013

I know need to modify the formula to include validating if the indexed reference occurs within a certain month.

{=INDEX('Duration Calculations'!B$2:B$5000,SMALL(IF(MONTH('Duration Calculations'!$J$2:$J$5000=10),IF(ISNUMBER(SEARCH("br",'Duration Calculations'!$A$2:$A$5000)),ROW('Duration Calculations'!$J$2:$J$5000)-ROW('Duration Calculations'!$J$2)+1)),ROWS(P$100:P101)))}

Essentially, what I am trying to do is index the value from B column if it meets the following criteria

1) Date in column J is equal to any date that occurs in October
2) the Column A has a text value equal to "br"

If both true then I need to index the value in column B.

The formula as states works finding any value equal to "br" but seems to be ignoring the date range.

View 1 Replies View Related

SUM (count) - Multiple Criteria Including Date Ranges

Oct 23, 2009

My setup is - excel 2003 sp3 / windows xp

On one sheet (Data) I have a list of action items, each with owner; target date; classification and in some cases revised target date. I'm trying to report on these fields and provide a status, by owner and classification, of how many are overdue; due this month; due next month; due beyond 2mths.

Using a SUM array formula on another sheet I can count the number that are overdue based on date; owner; classification; and target date, but can't find a solution if there is a revised target date. Any guidance you can give would be greatly received.

=SUM((Data!$L$3:$L$27=D$18)*(Data!$P$3:$P$27=$C23)*(Data!$N$3:$N$27

View 9 Replies View Related

Formula To Return Multiple Criteria Including Stretch Target

Jul 8, 2014

I'm struggling to come up with a formula that ill give me the following:

I have will have a row of data (cells A4:H4 on the attached example), in the respective cells below each value in row 4 I want formula that will give me a value of 10 if it sees 90% in the cell above reducing to a value of 0 if it sees 80% in the cell above. I think that there should be a relatively straightforward formulae for that, my problem is that I want to add a further criteria that says; if the value in row 4 increases above 90% and reaches 95% then I must return a value that is 10 for 90% but "stretches" to 12 for 95%. Anything below 80% should return a value of 0, and anything over 95% should return a value of 12.

View 8 Replies View Related

Convert Multiple Columns To Rows Including Blank Cells

Apr 11, 2014

I currently have a spreadsheet that I had to convert from multiple rows to columns:

[URL]

Now I need a script to change the data so that each column is now in row format, (see attached spreadsheet).

View 5 Replies View Related

Excel 2003 :: Averaging Cells In Multiple Sheets And Not Including Null Or Zero Value?

Jun 24, 2012

I need to calculate the average spend on a day of the week over the month, so all Monday's or all Tuesday's, etc. One sheet is one week so I need to average b16 on 6 sheets as an example.

I used =AVERAGE('WEEK1:WEEK6'!B18) to calculate average over the six Monday's. The issue is, as in other posts, how do I ignore the cells that have a zero or null value.

I've tried adjusting this which was in 1 post
=AVERAGE(IF($C$2:$CA$2=C62,IF($C$25:$CA$25"",$C$25:$CA$25
with this
=AVERAGE(IF(1+1=2,IF('WEEK1:WEEK6 '!B180,'WEEK1:WEEK6 '!B18))) which returns #REF!

this from another post
=SUM('WEEK1:WEEK7 '!B18)/COUNTIF('WEEK1:WEEK7 '!B18,"0") which returns #VALUE!

and this
=AVERAGE(IF('WEEK1:WEEK7 '!B180,'WEEK1:WEEK7 '!B18)) which returns #NAME?

The cells on each sheet are sum formulas for other cells on the sheet not just numbers on their own.

Using windows 7, excel 2003

View 3 Replies View Related







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