Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of difference between dates greater than zero
I'm having an issue trying to get this formula to work. I have a due date
which is when documents are supposed to be turned in and then their actual dates - when the docs were actually turned in. I need to know the difference between these two dates and if the difference is greater than zero (meaning they turned it in prior to the due date or it still hasn't been turned in - the cell is blank), the average of those differences. The current formula I'm using is: =AVERAGE(IF((O12-O9)0,(O12-O9)),IF((O14-O9)0,(O14-O9)),IF((O16-O9)0,(O16-O9)),IF((O18-O9)0,(O18-O9)),IF((O20-O9)0,(O20-O9))) It returns the average of all difference in dates, including the zeros. I tried making it an array formula like the following one, so the zeros could actually be null and then they wouldn't be averaged but that doesn't work either: {=AVERAGE(IF((N12-N9)0,(N12-N9),""),IF((N14-N9)0,(N14-N9),""),IF((N16-N9)0,(N16-N9),""),IF((N18-N9)0,(N18-N9),""),IF((N20-N9)0,(N20-N9),""))} Could someone please help me out with this, I'd greatly appreciate it =) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of difference between dates greater than zero
Because your breaking the numbers up, the Average function is treating the
FALSE values as 0. Can you simply combine the functions into: {=AVERAGE(IF(O12:O20-O90,O12:O20-O9,FALSE))} If you have text values or something in between that you were trying to ignore, you might be able to just eliminate them within the IF function like so: {=AVERAGE(IF(AND(ISNUMBER(O12:O20),O12:O20-O90),O12:O20-O9,FALSE))} This would ignore all text values on rows 13, 15, 17, 19. Also, I presume you know these are array functions, but for future readers, these formulas should be entered without the curly brackets, and confirmed using Ctrl+Shift+Enter. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "juneturtle" wrote: I'm having an issue trying to get this formula to work. I have a due date which is when documents are supposed to be turned in and then their actual dates - when the docs were actually turned in. I need to know the difference between these two dates and if the difference is greater than zero (meaning they turned it in prior to the due date or it still hasn't been turned in - the cell is blank), the average of those differences. The current formula I'm using is: =AVERAGE(IF((O12-O9)0,(O12-O9)),IF((O14-O9)0,(O14-O9)),IF((O16-O9)0,(O16-O9)),IF((O18-O9)0,(O18-O9)),IF((O20-O9)0,(O20-O9))) It returns the average of all difference in dates, including the zeros. I tried making it an array formula like the following one, so the zeros could actually be null and then they wouldn't be averaged but that doesn't work either: {=AVERAGE(IF((N12-N9)0,(N12-N9),""),IF((N14-N9)0,(N14-N9),""),IF((N16-N9)0,(N16-N9),""),IF((N18-N9)0,(N18-N9),""),IF((N20-N9)0,(N20-N9),""))} Could someone please help me out with this, I'd greatly appreciate it =) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of difference between dates greater than zero
Try this:
=AVERAGE(IF({TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE ,FALSE,TRUE}*(O12:O20-O90),O12:O20-O9,FALSE)) We'll just have to manually FORCE the formula to ignore those rows!! -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "juneturtle" wrote: Luke, thanks for your swift response. It seems like your formulas could work but I wasn't using a range before because there are different dates in the odd numbered cells. Do you have any other suggestions? "Luke M" wrote: My apologies, change second formula to: {=AVERAGE(IF(ISNUMBER(O12:O20)*(O12:O20-O90),O12:O20-O9,FALSE))} Because its an array, need to multiple criteria, not evaluate independently. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Luke M" wrote: Because your breaking the numbers up, the Average function is treating the FALSE values as 0. Can you simply combine the functions into: {=AVERAGE(IF(O12:O20-O90,O12:O20-O9,FALSE))} If you have text values or something in between that you were trying to ignore, you might be able to just eliminate them within the IF function like so: {=AVERAGE(IF(AND(ISNUMBER(O12:O20),O12:O20-O90),O12:O20-O9,FALSE))} This would ignore all text values on rows 13, 15, 17, 19. Also, I presume you know these are array functions, but for future readers, these formulas should be entered without the curly brackets, and confirmed using Ctrl+Shift+Enter. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "juneturtle" wrote: I'm having an issue trying to get this formula to work. I have a due date which is when documents are supposed to be turned in and then their actual dates - when the docs were actually turned in. I need to know the difference between these two dates and if the difference is greater than zero (meaning they turned it in prior to the due date or it still hasn't been turned in - the cell is blank), the average of those differences. The current formula I'm using is: =AVERAGE(IF((O12-O9)0,(O12-O9)),IF((O14-O9)0,(O14-O9)),IF((O16-O9)0,(O16-O9)),IF((O18-O9)0,(O18-O9)),IF((O20-O9)0,(O20-O9))) It returns the average of all difference in dates, including the zeros. I tried making it an array formula like the following one, so the zeros could actually be null and then they wouldn't be averaged but that doesn't work either: {=AVERAGE(IF((N12-N9)0,(N12-N9),""),IF((N14-N9)0,(N14-N9),""),IF((N16-N9)0,(N16-N9),""),IF((N18-N9)0,(N18-N9),""),IF((N20-N9)0,(N20-N9),""))} Could someone please help me out with this, I'd greatly appreciate it =) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of difference between dates greater than zero
Wow, it worked! Thank you so much...you were a great help. I'm sure I'll be
on here in the future for more mind-boggling excel questions! "Luke M" wrote: Try this: =AVERAGE(IF({TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE ,FALSE,TRUE}*(O12:O20-O90),O12:O20-O9,FALSE)) We'll just have to manually FORCE the formula to ignore those rows!! -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "juneturtle" wrote: Luke, thanks for your swift response. It seems like your formulas could work but I wasn't using a range before because there are different dates in the odd numbered cells. Do you have any other suggestions? "Luke M" wrote: My apologies, change second formula to: {=AVERAGE(IF(ISNUMBER(O12:O20)*(O12:O20-O90),O12:O20-O9,FALSE))} Because its an array, need to multiple criteria, not evaluate independently. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Luke M" wrote: Because your breaking the numbers up, the Average function is treating the FALSE values as 0. Can you simply combine the functions into: {=AVERAGE(IF(O12:O20-O90,O12:O20-O9,FALSE))} If you have text values or something in between that you were trying to ignore, you might be able to just eliminate them within the IF function like so: {=AVERAGE(IF(AND(ISNUMBER(O12:O20),O12:O20-O90),O12:O20-O9,FALSE))} This would ignore all text values on rows 13, 15, 17, 19. Also, I presume you know these are array functions, but for future readers, these formulas should be entered without the curly brackets, and confirmed using Ctrl+Shift+Enter. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "juneturtle" wrote: I'm having an issue trying to get this formula to work. I have a due date which is when documents are supposed to be turned in and then their actual dates - when the docs were actually turned in. I need to know the difference between these two dates and if the difference is greater than zero (meaning they turned it in prior to the due date or it still hasn't been turned in - the cell is blank), the average of those differences. The current formula I'm using is: =AVERAGE(IF((O12-O9)0,(O12-O9)),IF((O14-O9)0,(O14-O9)),IF((O16-O9)0,(O16-O9)),IF((O18-O9)0,(O18-O9)),IF((O20-O9)0,(O20-O9))) It returns the average of all difference in dates, including the zeros. I tried making it an array formula like the following one, so the zeros could actually be null and then they wouldn't be averaged but that doesn't work either: {=AVERAGE(IF((N12-N9)0,(N12-N9),""),IF((N14-N9)0,(N14-N9),""),IF((N16-N9)0,(N16-N9),""),IF((N18-N9)0,(N18-N9),""),IF((N20-N9)0,(N20-N9),""))} Could someone please help me out with this, I'd greatly appreciate it =) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of difference between dates greater than zero
=AVERAGE(IF({TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRU E,FALSE,TRUE}*(O12:O20-O90),O12:O20-O9,FALSE))
Are you sure that's doing what you think it's doing? The array of Booleans should be a *vertical* array. As you have it, it's a horizontal array and is not calculating the way you think. A vertical array is delimited by semi-colons: {TRUE;FALSE;TRUE} Try it like this: =AVERAGE(IF((MOD(ROW(O12:O20),2)=0)*(O12:O20-O90),O12:O20-O9)) If you want it to be more robust against row insertions: =AVERAGE(IF((MOD(ROW(O12:O20)-ROW(O12),2)=0)*(O12:O20-O90),O12:O20-O9)) All formulas are array formulas. -- Biff Microsoft Excel MVP "Luke M" wrote in message ... Try this: =AVERAGE(IF({TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE ,FALSE,TRUE}*(O12:O20-O90),O12:O20-O9,FALSE)) We'll just have to manually FORCE the formula to ignore those rows!! -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "juneturtle" wrote: Luke, thanks for your swift response. It seems like your formulas could work but I wasn't using a range before because there are different dates in the odd numbered cells. Do you have any other suggestions? "Luke M" wrote: My apologies, change second formula to: {=AVERAGE(IF(ISNUMBER(O12:O20)*(O12:O20-O90),O12:O20-O9,FALSE))} Because its an array, need to multiple criteria, not evaluate independently. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Luke M" wrote: Because your breaking the numbers up, the Average function is treating the FALSE values as 0. Can you simply combine the functions into: {=AVERAGE(IF(O12:O20-O90,O12:O20-O9,FALSE))} If you have text values or something in between that you were trying to ignore, you might be able to just eliminate them within the IF function like so: {=AVERAGE(IF(AND(ISNUMBER(O12:O20),O12:O20-O90),O12:O20-O9,FALSE))} This would ignore all text values on rows 13, 15, 17, 19. Also, I presume you know these are array functions, but for future readers, these formulas should be entered without the curly brackets, and confirmed using Ctrl+Shift+Enter. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "juneturtle" wrote: I'm having an issue trying to get this formula to work. I have a due date which is when documents are supposed to be turned in and then their actual dates - when the docs were actually turned in. I need to know the difference between these two dates and if the difference is greater than zero (meaning they turned it in prior to the due date or it still hasn't been turned in - the cell is blank), the average of those differences. The current formula I'm using is: =AVERAGE(IF((O12-O9)0,(O12-O9)),IF((O14-O9)0,(O14-O9)),IF((O16-O9)0,(O16-O9)),IF((O18-O9)0,(O18-O9)),IF((O20-O9)0,(O20-O9))) It returns the average of all difference in dates, including the zeros. I tried making it an array formula like the following one, so the zeros could actually be null and then they wouldn't be averaged but that doesn't work either: {=AVERAGE(IF((N12-N9)0,(N12-N9),""),IF((N14-N9)0,(N14-N9),""),IF((N16-N9)0,(N16-N9),""),IF((N18-N9)0,(N18-N9),""),IF((N20-N9)0,(N20-N9),""))} Could someone please help me out with this, I'd greatly appreciate it =) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of difference between dates greater than zero
Hello Biff,
... If you want it to be more robust against row insertions: =AVERAGE(IF((MOD(ROW(O12:O20)-ROW(O12),2)=0)*(O12:O20-O90),O12:O20-O9)) ... Insert a row after row 12 or before 20 :-) SCNR, Bernd |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of difference between dates greater than zero
Insert a row after row 12 or before 20 :-)
Yeah, I said "more robust" not bulletproof robust! <g -- Biff Microsoft Excel MVP "Bernd P" wrote in message ... Hello Biff, ... If you want it to be more robust against row insertions: =AVERAGE(IF((MOD(ROW(O12:O20)-ROW(O12),2)=0)*(O12:O20-O90),O12:O20-O9)) ... Insert a row after row 12 or before 20 :-) SCNR, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average the Difference between three dates in a pivot table | Excel Discussion (Misc queries) | |||
Average if is not greater than today | Excel Worksheet Functions | |||
Average using Greater Than - Less Than | Excel Discussion (Misc queries) | |||
Average greater than 0 | Excel Discussion (Misc queries) | |||
Countif cell greater than average | Excel Worksheet Functions |