| 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting with dates and other criteria
2 questions:
1. Somehow my numbers do not add up using the formula you recommended. When I manually do the avg 276/6=46. blank 57 107 22 0 55 blank 12 Blank 14 When I do the formula =AVERAGE(IF('Domestic #''s '!C3:C152="Valarie B.", ('Domestic #''s '!P3:P152 <0),'Domestic #''s '!P3:P152)) I get 59 when hitting enter and 26 when I do cntrl,shift,enter What am I not seeing? Thank you for educating me.... 2. when you have 2 dates and you want to know the days in-between I use =b1-a1. But when b1 is blank a negative number appears. is there a better formula? or can I put something that if a2 is blank to put "0" otherwise put the number between the 2 cells. Thanks "T. Valko" wrote: 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting with dates and other criteria
=AVERAGE(IF('Domestic #''s '!C3:C152="Valarie B.", ('Domestic #''s
'!P3:P152 <0),'Domestic #''s '!P3:P152)) Your formula syntax doesn't look anything like the one I suggested, now does is it? Try it like this (array entered): =AVERAGE(IF(('Domestic #''s '!C3:C152="Valarie B.")*('Domestic #''s '!P3:P152 <0),'Domestic #''s '!P3:P152)) For the date subtraction question, one way is to test and make sure there are 2 entries: =IF(COUNT(A1:B1)<2,0,B1-A1) -- Biff Microsoft Excel MVP "Roman" wrote in message ... 2 questions: 1. Somehow my numbers do not add up using the formula you recommended. When I manually do the avg 276/6=46. blank 57 107 22 0 55 blank 12 Blank 14 When I do the formula =AVERAGE(IF('Domestic #''s '!C3:C152="Valarie B.", ('Domestic #''s '!P3:P152 <0),'Domestic #''s '!P3:P152)) I get 59 when hitting enter and 26 when I do cntrl,shift,enter What am I not seeing? Thank you for educating me.... 2. when you have 2 dates and you want to know the days in-between I use =b1-a1. But when b1 is blank a negative number appears. is there a better formula? or can I put something that if a2 is blank to put "0" otherwise put the number between the 2 cells. Thanks "T. Valko" wrote: 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting with dates and other criteria
No it does not..... Everything now works. Thank you for your help and
patience... "T. Valko" wrote: =AVERAGE(IF('Domestic #''s '!C3:C152="Valarie B.", ('Domestic #''s '!P3:P152 <0),'Domestic #''s '!P3:P152)) Your formula syntax doesn't look anything like the one I suggested, now does is it? Try it like this (array entered): =AVERAGE(IF(('Domestic #''s '!C3:C152="Valarie B.")*('Domestic #''s '!P3:P152 <0),'Domestic #''s '!P3:P152)) For the date subtraction question, one way is to test and make sure there are 2 entries: =IF(COUNT(A1:B1)<2,0,B1-A1) -- Biff Microsoft Excel MVP "Roman" wrote in message ... 2 questions: 1. Somehow my numbers do not add up using the formula you recommended. When I manually do the avg 276/6=46. blank 57 107 22 0 55 blank 12 Blank 14 When I do the formula =AVERAGE(IF('Domestic #''s '!C3:C152="Valarie B.", ('Domestic #''s '!P3:P152 <0),'Domestic #''s '!P3:P152)) I get 59 when hitting enter and 26 when I do cntrl,shift,enter What am I not seeing? Thank you for educating me.... 2. when you have 2 dates and you want to know the days in-between I use =b1-a1. But when b1 is blank a negative number appears. is there a better formula? or can I put something that if a2 is blank to put "0" otherwise put the number between the 2 cells. Thanks "T. Valko" wrote: 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 |
|
#10
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 ... No it does not..... Everything now works. Thank you for your help and patience... "T. Valko" wrote: =AVERAGE(IF('Domestic #''s '!C3:C152="Valarie B.", ('Domestic #''s '!P3:P152 <0),'Domestic #''s '!P3:P152)) Your formula syntax doesn't look anything like the one I suggested, now does is it? Try it like this (array entered): =AVERAGE(IF(('Domestic #''s '!C3:C152="Valarie B.")*('Domestic #''s '!P3:P152 <0),'Domestic #''s '!P3:P152)) For the date subtraction question, one way is to test and make sure there are 2 entries: =IF(COUNT(A1:B1)<2,0,B1-A1) -- Biff Microsoft Excel MVP "Roman" wrote in message ... 2 questions: 1. Somehow my numbers do not add up using the formula you recommended. When I manually do the avg 276/6=46. blank 57 107 22 0 55 blank 12 Blank 14 When I do the formula =AVERAGE(IF('Domestic #''s '!C3:C152="Valarie B.", ('Domestic #''s '!P3:P152 <0),'Domestic #''s '!P3:P152)) I get 59 when hitting enter and 26 when I do cntrl,shift,enter What am I not seeing? Thank you for educating me.... 2. when you have 2 dates and you want to know the days in-between I use =b1-a1. But when b1 is blank a negative number appears. is there a better formula? or can I put something that if a2 is blank to put "0" otherwise put the number between the 2 cells. Thanks "T. Valko" wrote: 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 | |||
Linear Mode
