Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate average based on date and other criteria
I have a worksheet (sheet 1) which include the following columns:
Audit Date.... Name.... File Score... (A3:A9949) (D3:D9949) (L3:L9949) On the next worksheet (sheet 2), I want to calculate a file score average for all entries belonging to a certain name and on or after a certain date shown on sheet 1. So for instance I want to calculate a file score average for Doe, John for all Audit Date entries on or after 7/1/2006. Any suggestions? Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate average based on date and other criteria
Hi!
Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER: How to enter an array formula: http://cpearson.com/excel/array.htm =AVERAGE(IF((A3:A9949=--"2006,7,1")*(D3:D9949="Doe, John"),L3:L9949)) Better if you use cells to hold the criteria: M1 = 7/1/2006 N1 = Doe, John =AVERAGE(IF((A3:A9949=M1)*(D3:D9949=N1),L3:L9949) ) Biff "Kycajun" wrote in message ... I have a worksheet (sheet 1) which include the following columns: Audit Date.... Name.... File Score... (A3:A9949) (D3:D9949) (L3:L9949) On the next worksheet (sheet 2), I want to calculate a file score average for all entries belonging to a certain name and on or after a certain date shown on sheet 1. So for instance I want to calculate a file score average for Doe, John for all Audit Date entries on or after 7/1/2006. Any suggestions? Thanks! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate average based on date and other criteria
Thanks! I am working on it now, so far it is not working, but I think I am
close by using your formula. This may sound like a silly question, but how do you get the two dashes after the equal sign before the date. I tried the dash key and I don't think it is right, because they don't appear exactly as your does. Any idea? "Biff" wrote: Hi! Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER: How to enter an array formula: http://cpearson.com/excel/array.htm =AVERAGE(IF((A3:A9949=--"2006,7,1")*(D3:D9949="Doe, John"),L3:L9949)) Better if you use cells to hold the criteria: M1 = 7/1/2006 N1 = Doe, John =AVERAGE(IF((A3:A9949=M1)*(D3:D9949=N1),L3:L9949) ) Biff "Kycajun" wrote in message ... I have a worksheet (sheet 1) which include the following columns: Audit Date.... Name.... File Score... (A3:A9949) (D3:D9949) (L3:L9949) On the next worksheet (sheet 2), I want to calculate a file score average for all entries belonging to a certain name and on or after a certain date shown on sheet 1. So for instance I want to calculate a file score average for Doe, John for all Audit Date entries on or after 7/1/2006. Any suggestions? Thanks! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate average based on date and other criteria
Ooops!
I goofed. =AVERAGE(IF((A3:A9949=--"2006,7,1")*(D3:D9949="Doe, John"),L3:L9949)) Should be: =AVERAGE(IF((A3:A9949=--"2006/7/1")*(D3:D9949="Doe, John"),L3:L9949)) how do you get the two dashes I tried the dash key Those are just 2 consecutive minus signs. If you use cells to hold the criteria then you don't have to worry about that. M1 = 7/1/2006 N1 = Doe, John =AVERAGE(IF((A3:A9949=M1)*(D3:D9949=N1),L3:L9949) ) Biff "Kycajun" wrote in message ... Thanks! I am working on it now, so far it is not working, but I think I am close by using your formula. This may sound like a silly question, but how do you get the two dashes after the equal sign before the date. I tried the dash key and I don't think it is right, because they don't appear exactly as your does. Any idea? "Biff" wrote: Hi! Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER: How to enter an array formula: http://cpearson.com/excel/array.htm =AVERAGE(IF((A3:A9949=--"2006,7,1")*(D3:D9949="Doe, John"),L3:L9949)) Better if you use cells to hold the criteria: M1 = 7/1/2006 N1 = Doe, John =AVERAGE(IF((A3:A9949=M1)*(D3:D9949=N1),L3:L9949) ) Biff "Kycajun" wrote in message ... I have a worksheet (sheet 1) which include the following columns: Audit Date.... Name.... File Score... (A3:A9949) (D3:D9949) (L3:L9949) On the next worksheet (sheet 2), I want to calculate a file score average for all entries belonging to a certain name and on or after a certain date shown on sheet 1. So for instance I want to calculate a file score average for Doe, John for all Audit Date entries on or after 7/1/2006. Any suggestions? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|