Finding Averages in a set of cells based on values in part of a set of different cells

**xBostonx** · 03-23-2017, 08:10 PM

NOTE: Sheet is attached

I have an excel sheet that contains one column ("Email Send Date") with cells that contain both a date and time. For example, "8/18/2016 6:00:00 AM". There are a bunch of other columns with different percentages for a variety of KPIs.

My question is how can I find averages for these different rates (for example, the unique open rate) based on the time in the "Email Send Date" cell (ex: 6:00:00 AM, 7:00:00 AM, etc.)? Or do I have to separate the date and time?

Thanks in advance.

**FlameRetired** · 03-23-2017, 08:32 PM

I am not sure what you mean about the averaging, but see if this is helpful.

You can "extract" the times from the combined date/times

=MOD(Email Send Date,1)

That will return the decimal part of the combined date/time. If you are not aware of it that decimal represents a fraction of a day which is the time ... 0.5 = 12:00 PM, 0.33333 = 8:00 AM ...

=INT(Email Send Date) will return the date portion only.

Does this help?

**xBostonx** · 03-23-2017, 09:23 PM

Originally Posted by FlameRetired

I am not sure what you mean about the averaging, but see if this is helpful.

You can "extract" the times from the combined date/times

=MOD(Email Send Date,1)

That will return the decimal part of the combined date/time. If you are not aware of it that decimal represents a fraction of a day which is the time ... 0.5 = 12:00 PM, 0.33333 = 8:00 AM ...

=INT(Email Send Date) will return the date portion only.

Does this help?

What I mean by averaging is (for example) finding the average open rate for emails sent at 6 AM, 7 AM, etc, so I can compare them against each other. I tried what you recommended but it doesn't seem to be working.

**FlameRetired** · 03-23-2017, 10:31 PM

I had to account for some rounding errors.

In column T is a helper column. It extracts the times. The formula is

Formula:

Please Login or Register  to view this content.

I generated lookup times in row 3 with this formula

Formula:

Please Login or Register  to view this content.

Then to get the conditional averages this formula in row 4

Formula:

Please Login or Register  to view this content.

I formatted it h:mm.

Finding Averages in a set of cells based on values in part of a set of different cells

LinkBack

Thread Tools

Rate This Thread

Display

Finding Averages in a set of cells based on values in part of a set of different cells

Re: Finding Averages in a set of cells based on values in part of a set of different cells

Re: Finding Averages in a set of cells based on values in part of a set of different cells

Re: Finding Averages in a set of cells based on values in part of a set of different cells

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Average Formula that averages certain cells based on how i filter.

[SOLVED] Finding Averages in Cells with Text and Number

[SOLVED] returning averages in cells based on autofilter

HELP Subtracting averages when some cells have no values

[SOLVED] CF based on part of cells vaule

formula for calculating averages of other cells based on multiple criteria

Sum Cells Based on Current Qtr (Part 2)

Bookmarks

Bookmarks

Posting Permissions