Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging
If I have a column of data that I got using this formula:
DATEDIF(B2,C2,"m") & " months " & DATEDIF(B2,C2,"md") & " days " & TEXT(C2-B2,"hh") & " hours " & LEFT(TEXT(C2-B2,"mm:ss"),2) & " minutes" is there any way to then get an average from that column. When I used the regular average function that didn't work. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging
That's because you're trying to average text, not numbers. Do the Average on Column B and then apply your formula to that. "Hookette" wrote: If I have a column of data that I got using this formula: DATEDIF(B2,C2,"m") & " months " & DATEDIF(B2,C2,"md") & " days " & TEXT(C2-B2,"hh") & " hours " & LEFT(TEXT(C2-B2,"mm:ss"),2) & " minutes" is there any way to then get an average from that column. When I used the regular average function that didn't work. Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging
Not directly, because the output of this formula is text, and you've got the
values mixed in with text. If you're wanting to do averages, I would recommend splitting this formula up into multiple columns. Also, of note, this: LEFT(TEXT(C2-B2,"mm:ss"),2) can be simplified to simply: TEXT(C2-B2,"mm") -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Hookette" wrote: If I have a column of data that I got using this formula: DATEDIF(B2,C2,"m") & " months " & DATEDIF(B2,C2,"md") & " days " & TEXT(C2-B2,"hh") & " hours " & LEFT(TEXT(C2-B2,"mm:ss"),2) & " minutes" is there any way to then get an average from that column. When I used the regular average function that didn't work. Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging
Or, as Sam suggested, do an average to columns B & C, and then apply your
formula. It works out the same mathematically. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Luke M" wrote: Not directly, because the output of this formula is text, and you've got the values mixed in with text. If you're wanting to do averages, I would recommend splitting this formula up into multiple columns. Also, of note, this: LEFT(TEXT(C2-B2,"mm:ss"),2) can be simplified to simply: TEXT(C2-B2,"mm") -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Hookette" wrote: If I have a column of data that I got using this formula: DATEDIF(B2,C2,"m") & " months " & DATEDIF(B2,C2,"md") & " days " & TEXT(C2-B2,"hh") & " hours " & LEFT(TEXT(C2-B2,"mm:ss"),2) & " minutes" is there any way to then get an average from that column. When I used the regular average function that didn't work. Thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging
Averaging days is fine but do do it in months cause problems - If you have a
process that always takes 1 month, 29 days then the average is going to show as 2 months and 1 day because of February. "Luke M" wrote: Or, as Sam suggested, do an average to columns B & C, and then apply your formula. It works out the same mathematically. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Luke M" wrote: Not directly, because the output of this formula is text, and you've got the values mixed in with text. If you're wanting to do averages, I would recommend splitting this formula up into multiple columns. Also, of note, this: LEFT(TEXT(C2-B2,"mm:ss"),2) can be simplified to simply: TEXT(C2-B2,"mm") -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Hookette" wrote: If I have a column of data that I got using this formula: DATEDIF(B2,C2,"m") & " months " & DATEDIF(B2,C2,"md") & " days " & TEXT(C2-B2,"hh") & " hours " & LEFT(TEXT(C2-B2,"mm:ss"),2) & " minutes" is there any way to then get an average from that column. When I used the regular average function that didn't work. Thanks. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging
"Sam Wilson" wrote:
That's because you're trying to average text, not numbers. Do the Average on Column B and then apply your formula to that. I assume you mean: take the average of column C minus column B, then apply the formula. I had thought about that when the OP posted the query as a follow-up in his original thread, "Formula for elapsed time". I don't think it works. Moreover, I do not believe there is a "right" answer, given that the OP wants to report elapsed time in months and days, and the OP was told to use DATEDIF("md"). The problem is the way in which DATEDIF reports elapsed months and "month days". Consider the following start and end dates in A1:A3 and B1:B3 for which DATEDIF reports 1 month 29 days using "md", shown to the right: 2/1/2008 3/30/2008 1 29 3/1/2008 4/30/2008 1 29 4/1/2008 5/30/2008 1 29 The true average can be computed using the following array formula (commit with ctrl+shift+Enter instead of Enter): A4: =AVERAGE(B1:B3 - A1:A3) To format as months and "month days" using DATEDIF, we might try: DATEDIF(DATE(1900,1,1), A4+1, "m") & " months" DATEDIF(DATE(1900,1,1), A4+1, "md") & " days" The result is 1 month 28 days (!). The reason for the apparent disparity is two-fold. The primary problem is the way in which DATEDIF("md") works. Although DATEDIF would have us believe that all three time periods are equally long, in fact they are different, namely: 58, 60 and 59 days. The second problem is the fact that we are trying to use elapsed time as a date relative to 1/1/1900 so that we can use DATEDIF. By the way, these problems are even more interesting when we add the following start and end dates to the mix: 1/1/2008 and 3/1/2008. Note that with an end date of 2/29/2008, DATEDIF would show 1 month 28 days. But with 3/1/2008, one day later, DATEDIF shows 2 months 0 days. There is "no period" of 1 month 29 days (!), based on DATEDIF. Even more interesting: according to DATEDIF, the period of 1/1/2009 to 2/28/2009 is 1 month 27 days, but the period of 1/1/2009 to 3/1/2009, one day longer, is 2 months 0 days. There are "no periods" of 1 month 28 or 29 days (!) in between. I think the OP was ill-advised to use DATEDIF("md") in the first place, although admittedly that can be the right answer in some contexts. Also, the OP needs to recognize that trying to report elapsed time in months and days is ambiguous. It means different things in different contexts. I would report elapsed time in only days. (And hours and minutes, per the OP's original request.) That does not require the use of DATEDIF. But if the OP insists on reporting elapsed time in months and days, I think he has two choices: (a) always use a divisor of 30, a not-so-uncommon choice in both law and finance; or (b) use DATEDIF for the individual elapsed times, but use a divisor of 30 for the average and accept the disparity as a computational consequence, much as we accept the fact that a column of rounded percentages does not always add up to 100%. To use a divisor of 30 (or 365/12 or 1461/48; pick your poison): =INT(A4/30) & " months " & MOD(INT(A4),30) " days " & TEXT(MOD(A4,1),"h") & " hours " & --RIGHT(TEXT(MOD(A4,1),"hh:mm"),2) & " minutes" A4 can be the average of the difference of the columns as shown above, or it can be replaced with B1-A1 for individual elapsed times. ----- original message ----- "Sam Wilson" wrote in message ... That's because you're trying to average text, not numbers. Do the Average on Column B and then apply your formula to that. "Hookette" wrote: If I have a column of data that I got using this formula: DATEDIF(B2,C2,"m") & " months " & DATEDIF(B2,C2,"md") & " days " & TEXT(C2-B2,"hh") & " hours " & LEFT(TEXT(C2-B2,"mm:ss"),2) & " minutes" is there any way to then get an average from that column. When I used the regular average function that didn't work. Thanks. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging
"Luke M" wrote:
Also, of note, this: LEFT(TEXT(C2-B2,"mm:ss"),2) can be simplified to simply: TEXT(C2-B2,"mm") I don't think so. Test with C2-B2 equal to 32.5. LEFT(TEXT(32.5,"mm:ss"),2) results in "00", whereas TEXT(32.5,"mm") results in "02". To understand why, format 32.5 with the custom format "m/d/yyyy h:mm" without quotes; note that it appears as 2/1/1900 12:00. TEXT(...,"mm") returns the month number in digits, not the minutes. ----- original message ----- "Luke M" wrote in message ... Not directly, because the output of this formula is text, and you've got the values mixed in with text. If you're wanting to do averages, I would recommend splitting this formula up into multiple columns. Also, of note, this: LEFT(TEXT(C2-B2,"mm:ss"),2) can be simplified to simply: TEXT(C2-B2,"mm") -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Hookette" wrote: If I have a column of data that I got using this formula: DATEDIF(B2,C2,"m") & " months " & DATEDIF(B2,C2,"md") & " days " & TEXT(C2-B2,"hh") & " hours " & LEFT(TEXT(C2-B2,"mm:ss"),2) & " minutes" is there any way to then get an average from that column. When I used the regular average function that didn't work. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Averaging | Excel Worksheet Functions | |||
averaging | Excel Discussion (Misc queries) | |||
Help with averaging... | Excel Discussion (Misc queries) | |||
Averaging | Excel Discussion (Misc queries) | |||
Averaging | Excel Discussion (Misc queries) |