Looking for formula that would create an average from certain criteria with in excell

[bdouglas1011]

With in my sheet I have certain things broken down and there is a corresponding date and time with in a certain amount of days. We group our days in 2 parts (days & Nights), with in that day they are 2 activity's that happen either (drilling or Sliding) we also track the Time it takes to complete each activity called ROP. I go through and highlight what part of the days I want calculated as days & what part are nights (I do this because some times over lap). My question is how do I write a formula that could go through the sheet and group all of one certain color, then go through that color and group all the activities that correspond to drilling and then add them up and give me the average time it took to complete that activity on that day.

Then do it for the other activity for that day.


Another version a little more complicated would be to include this:

It would be nice if the formula could also scan the time of day and know whether it was days or nights so that way it would know what lines to use in the calculations with out me having to highlight them first. The only problem I see is that times do have gaps so sometimes it is not easy to tell if I want that to be included on days or nights.

Any help would be great

[daffodil11]

I found a few issues with your dataset.

"6-Jun" isn't a date, it's just a text string. You need to multiply this times 1 or use a helper column to let Excel register this as a date.
The same thing goes for your start and end times.


Assuming that is fixed, you can setup a small table to summarize.

I used SUMPRODUCT of the values, with SUMPRODUCT as divisor for count of qualified valued.

This works by defining the hours of the day. The night summary is an inverse of the times that qualified for day.

[bdouglas1011]

Ok, forget about the date and time: for all things

Highlighted Blue: I need the average ROP on Drilling and the average ROP on Sliding.

Highlighted Red: I need the average ROP on Drilling and the average ROP on Sliding.

Sorry I hope this makes it easier

[daffodil11]

Okie dokie. If you're really insistent on going the color route you'll have to use some VB code.
Chip Pearson was kind enough to lay the ground work for us. Thanks Chip!

In Excel hit Alt+F11 to enter VB land. Go to Insert -> Module. A big white window pops up.

Copy this wall of code, which features two UDFs (User Defined Functions) that examine cell properties and so we can use them as range criteria, into the big white window.

Please Login or Register  to view this content.

Now close that window.

Now we can call ColorIndexOfRange() as a range and compare it to other cells.

I went with:

=SUMPRODUCT((ColorIndexOfRange($I$2:$I$41)=ColorIndexOfRange($L2))*($C$2:$C$41=$M2)*($I$2:$I$41))/
SUMPRODUCT((ColorIndexOfRange($I$2:$I$41)=ColorIndexOfRange($L2))*($C$2:$C$41=$M2))

Where L2 was a blue cell, and M2 was the word Drilling.

See attached for further infos.

[bdouglas1011]

I could not open the attached file for some reason.

Brian

[bdouglas1011]

sorry finally got it Ill check it out in the morning hanks again

Brian

[bdouglas1011]

Okay Now I have a problem , I have multiple sheets around 25 that I need to eventually apply this solution to.... the problem I have is I had reduced certain rows & columns to reduce the file size in order to be able to attach an example. To begin I copied the VB code into the worksheet then I carried over your formula and change the corresponding columns to represent them in the new worksheet.

Certain worksheets have more rows to include so I would have to go into the formula every time to change those in order to catch the right data.

My formula comes back with #NAME? I have looked it over multiple times to no avail....I tried to attach for example but it is to big for site.

Also is there a way for it to automatically search and know what amount of rows there are to include.

My columns are usually constant just the number of rows change depending on length of time it takes to complete that section.

[daffodil11]

Yes, you could easily account for more rows than you have data for and never have to change it again.

=SUMPRODUCT((ColorIndexOfRange($I$2:$I$6000)=ColorIndexOfRange($L2))*($C$2:$C$6000=$M2)*($I$2:$I$6000))/
SUMPRODUCT((ColorIndexOfRange($I$2:$I$6000)=ColorIndexOfRange($L2))*($C$2:$C$6000=$M2))

Now the formula is looking at 6000 rows of data. It works no different if this is applied to only 500 filled rows with 5500 blanks.


Paste the actual formula you are using that's returning #NAME

[bdouglas1011]

I got it to work I must have not copied the VB code in correctly...One question is that if I have multple sheets within a workbook do have to redo the VN code in every sheet or once I do it that covers the entire workbook.

[daffodil11]

Module code applies to the entire workbook.

If instead of Inserting a new module, you had double clicked one of the sheet icons in the hierarchy on the left side of the VB Editor, you could apply code specifically to one sheet.

As it stands, the custom function will work everywhere in that workbook.

[bdouglas1011]

Thanks for all you help, I was able to apply this to quite of few workbooks and this made it extremely easier.

I have attached one of the sheets i used & would like to know if you think there is any other way to do the same thing and receive the same output.
As you see The blue color indicates anything happening between the hours of 1800 & 0600.
The Grey are things happening between 1800 & 0600.
The only reason there are colors is to distinguish who did what - I have two guys on the job (one between 0600/1800 & 1800/0600)
The average total drilling & sliding in blue and then in grey.

the output that I am getting now works just trying to see if there is anything better.

Let me know