Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional average of time
Hi everyone,
I have a big problem and need descreptly your help on that. I have an excel 2000 sheet in which i need to calculate average of time taken to solve the problem. Sheet details are given below time is in [h]:mm format and conditions are there i.e. if data in column A meets creteria then only it will add the respective time viz.. A B 123 36:56 123 12:13 235 00:15 528 28:16 now on based on this example i want to take average of time value from column B where data in column A is 123. which should come (36:56+12:13)/2. Please help -- "Rome was not built in one day" |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional average of time
=SUMPRODUCT(--(A1:A4=123),B1:B4)/COUNTIF(A1:A4,123)
Format as [h]:mm "Lovik" wrote: Hi everyone, I have a big problem and need descreptly your help on that. I have an excel 2000 sheet in which i need to calculate average of time taken to solve the problem. Sheet details are given below time is in [h]:mm format and conditions are there i.e. if data in column A meets creteria then only it will add the respective time viz.. A B 123 36:56 123 12:13 235 00:15 528 28:16 now on based on this example i want to take average of time value from column B where data in column A is 123. which should come (36:56+12:13)/2. Please help -- "Rome was not built in one day" |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional average of time
=AVERAGE(IF(A1:A100=123,B1:B100))
which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Lovik" wrote in message ... Hi everyone, I have a big problem and need descreptly your help on that. I have an excel 2000 sheet in which i need to calculate average of time taken to solve the problem. Sheet details are given below time is in [h]:mm format and conditions are there i.e. if data in column A meets creteria then only it will add the respective time viz.. A B 123 36:56 123 12:13 235 00:15 528 28:16 now on based on this example i want to take average of time value from column B where data in column A is 123. which should come (36:56+12:13)/2. Please help -- "Rome was not built in one day" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding the average time | Excel Worksheet Functions | |||
Conditional Average Array with Dates, <blanks, 0 | Excel Worksheet Functions | |||
Conditional Average across worksheets | Excel Worksheet Functions | |||
How do I add a list of times to get an average time? | Excel Discussion (Misc queries) | |||
Conditional average function | Excel Worksheet Functions |