how to average datea using condional formating

[BillR]

Hi everyone. Many thanks for your help.

I have a large database (see sample) that I use to track exam lengths. I would like a formula to use in column O that shows the average length of exams for the exam title that appears in column B. With this information, the spreadsheet will show me when the estimated end of the exam will happen based on the start time I enter.

Any suggestions are greatly appreciated!.

Thanks

//bill

 

[Chihiro]

What's conditional format have to do with your calculation?
As well, column O is empty and nothing in B as well. We'll need bit more sample and also add few manually calculated rows that shows your desired result.

FYI - Typically speaking, you wouldn't want Avg Time calculated for every row of data. You'd have separate table listing Exams (unique) and calculate avg time based on data available. You can then lookup to that table to estimate end time.

 

[GraH - Guido]

On the small sample it could be this (array formula, use CTRL + SHIFT + ENTER)
= AVERAGE ( IF ( ( $B$2:$B$5 )=$B2 , $j$2:$j$5 ) )

Or averageif (), if you have Excel 2007 or later.

Following the tip of Chihiro would be beneficial for excel performance since you say "large database".

 

[BillR]

Thanks for the response. I'm attaching a better spreadsheet.

I was thinking that the conditions are the name of the client, that would be used to find the average time for exams so the info would be entered in the last column... hopefully that makes sense.

What I do now, is filter the chart by client name... then highlight the actual exam times, make note of the average that shows on the bottom of the excel page and enter that average time in column O. I was hoping to make that process a bit more automatic.

 

[Haz]

To show the average in every line, use =AVERAGEIF(B:B, B2, L:L), copy and paste

If you want O1 to show the average as you filter, =SUBTOTAL(101, L:L)