| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting with dates and other criteria
Hoping you can help me.
I have Column A with a start date and Column B with an end date. Column C has "New". Column D has the name "Jane". Column E has the number of days between A and B. I want to: 1. See the average number of days (e1:e50) for Jane only 2. See the average number of days (e1:e50) for only Jane that are only New |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting with dates and other criteria
Try these array formulas** :
=AVERAGE(IF(D1:D50="Jane",E1:E50)) =AVERAGE(IF((C1:C50="new")*(D1:D50="Jane"),E1:E50) ) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Roman" wrote in message ... Hoping you can help me. I have Column A with a start date and Column B with an end date. Column C has "New". Column D has the name "Jane". Column E has the number of days between A and B. I want to: 1. See the average number of days (e1:e50) for Jane only 2. See the average number of days (e1:e50) for only Jane that are only New |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting with dates and other criteria
Thanks...That worked.
Cheers "T. Valko" wrote: Try these array formulas** : =AVERAGE(IF(D1:D50="Jane",E1:E50)) =AVERAGE(IF((C1:C50="new")*(D1:D50="Jane"),E1:E50) ) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Roman" wrote in message ... Hoping you can help me. I have Column A with a start date and Column B with an end date. Column C has "New". Column D has the name "Jane". Column E has the number of days between A and B. I want to: 1. See the average number of days (e1:e50) for Jane only 2. See the average number of days (e1:e50) for only Jane that are only New |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting with dates and other criteria
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Roman" wrote in message ... Thanks...That worked. Cheers "T. Valko" wrote: Try these array formulas** : =AVERAGE(IF(D1:D50="Jane",E1:E50)) =AVERAGE(IF((C1:C50="new")*(D1:D50="Jane"),E1:E50) ) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Roman" wrote in message ... Hoping you can help me. I have Column A with a start date and Column B with an end date. Column C has "New". Column D has the name "Jane". Column E has the number of days between A and B. I want to: 1. See the average number of days (e1:e50) for Jane only 2. See the average number of days (e1:e50) for only Jane that are only New |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting with dates and other criteria
What happens in this formula if there is no number in e1:e:50. Does the
average skip it or does it take a 0. If so, how would this formula look different if you ask it to skip cells that are blank or have "0". Thanks "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Roman" wrote in message ... Thanks...That worked. Cheers "T. Valko" wrote: Try these array formulas** : =AVERAGE(IF(D1:D50="Jane",E1:E50)) =AVERAGE(IF((C1:C50="new")*(D1:D50="Jane"),E1:E50) ) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Roman" wrote in message ... Hoping you can help me. I have Column A with a start date and Column B with an end date. Column C has "New". Column D has the name "Jane". Column E has the number of days between A and B. I want to: 1. See the average number of days (e1:e50) for Jane only 2. See the average number of days (e1:e50) for only Jane that are only New |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting with dates and other criteria
To exclude 0 and empty cells:
Array entered =AVERAGE(IF((C1:C50="new")*(D1:D50="Jane")*(E1:E50 <0),E1:E50)) -- Biff Microsoft Excel MVP "Roman" wrote in message ... What happens in this formula if there is no number in e1:e:50. Does the average skip it or does it take a 0. If so, how would this formula look different if you ask it to skip cells that are blank or have "0". Thanks "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Roman" wrote in message ... Thanks...That worked. Cheers "T. Valko" wrote: Try these array formulas** : =AVERAGE(IF(D1:D50="Jane",E1:E50)) =AVERAGE(IF((C1:C50="new")*(D1:D50="Jane"),E1:E50) ) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Roman" wrote in message ... Hoping you can help me. I have Column A with a start date and Column B with an end date. Column C has "New". Column D has the name "Jane". Column E has the number of days between A and B. I want to: 1. See the average number of days (e1:e50) for Jane only 2. See the average number of days (e1:e50) for only Jane that are only New |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting dates based on certain criteria | Excel Worksheet Functions | |||
| Counting unique dates based on selected criteria in a list | Excel Worksheet Functions | |||
| Counting multiple criteria involving dates | Excel Worksheet Functions | |||
| Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
| Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
Hybrid Mode
