Any Excel experts out there?

The Junk Drawer is for those Off Topical discussions where we can ask questions of the community that we feel might have the ability to help out.

Moderators: Harold_V, websterz

Post Reply
User avatar
Greg_Lewis
Posts: 1992
Joined: Wed Jan 15, 2003 2:44 pm
Location: Fresno, CA

Any Excel experts out there?

Post by Greg_Lewis » Wed Apr 15, 2020 12:05 am

Hey friends, I need some help with an Excel formula. I've been all over the net and can't find an answer that works. Here's what I've got. Column B contains times in the Excel time format. Column C contains values in the Excel number format. I want to find the average value for the AM times and then in a separate calculation, the average value for the PM times. Then I would also like to find the maximum value for AM and then PM, then the minimum value for AM and then PM. Below is a screen shot of the Excel worksheet. (There are many more entries than this and data continues to be entered so I don't want to do it by hand....)

Do any of you know how to do this?
Screen Shot 2020-04-14 at 10.02.32 PM.png
Greg Lewis, Prop.
Eyeball Engineering — Home of the dull toolbit.
Our motto: "That looks about right."
Celebrating 30 years of turning perfectly good metal into bits of useless scrap.

User avatar
NP317
Posts: 2581
Joined: Tue Jun 24, 2014 2:57 pm
Location: Northern Oregon

Re: Any Excel experts out there?

Post by NP317 » Wed Apr 15, 2020 12:29 am

I would change the "B" column times to 24 hour format.
Then mouse-click on the "B" column label and Excel should sort the chart based on the values in Column B. Click again to reverse the sort direction, if needed.
You can then insert a horizontal row to separate the 0:00 to 12:00 times, from the 12:01 to 24:00 times.
Then set up your calculations on the two separate upper and lower chart halves.
Make sense?
Let me know how that works, or if you find a better way. There must be...
RussN

hanermo
Posts: 50
Joined: Tue Oct 01, 2013 5:47 am

Re: Any Excel experts out there?

Post by hanermo » Wed Apr 15, 2020 1:04 am

Endless ways to do this.

I recommend the easy simple brute force approach.

Add columns to the right, referring to the time column.
You can hide them later, if you want, but they will still work.

1. For column c, use a format like result = (time >= 0 AND <= 12:00) x time.
aka = a1 x ( a1 >= 0 and a1 >= 12)
2. could use if .. then, but it´s slow.

For column d, use the same as c, but adjust for pm times, >12:00 AM.

Now you only will have times in column c of am, and times in d of pm.
Sum and avg them at top, like SUM(range).
AKA = SUM(C3:C999) in the cell C1 perhaps.

Extra credit.
Use named ranges to make it more elegant, and use flex ranges to make it adjust to future increases.

It a good idea to always work with numbers only, not am, pm, etc.
Then use format functions to *display things* the way you want, but always based on underlying numbers only.
Endless reasons, daylight savings, time zones, etc.

Cntrl 1 or format cells, custom format, copy from samples, easy to do.

hanermo
Posts: 50
Joined: Tue Oct 01, 2013 5:47 am

Re: Any Excel experts out there?

Post by hanermo » Wed Apr 15, 2020 1:07 am

The second use has the < sign wrong as a typo for GT or greater than 12 when it should be LT or Less Than.

User avatar
Greg_Lewis
Posts: 1992
Joined: Wed Jan 15, 2003 2:44 pm
Location: Fresno, CA

Re: Any Excel experts out there?

Post by Greg_Lewis » Wed Apr 15, 2020 10:53 am

Thanks for the tips, guys. The time data is in the Excel time format so Excel sees it as a numerical value regardless of how it displays. (For example, Excel sees 8:00 AM as .333333333 and 9:00 AM as .375. But 9:00 AM is the same numerical value as 9:00 PM.) I'll try out your suggestions later. I won't have time to pursue this until this evening. I'll let you know what happens.
Greg Lewis, Prop.
Eyeball Engineering — Home of the dull toolbit.
Our motto: "That looks about right."
Celebrating 30 years of turning perfectly good metal into bits of useless scrap.

User avatar
SteveM
Posts: 7353
Joined: Mon Jun 27, 2005 6:18 pm
Location: Connecticut

Re: Any Excel experts out there?

Post by SteveM » Wed Apr 15, 2020 1:47 pm

Sent you a solution.

Steve

User avatar
Greg_Lewis
Posts: 1992
Joined: Wed Jan 15, 2003 2:44 pm
Location: Fresno, CA

Re: Any Excel experts out there?

Post by Greg_Lewis » Wed Apr 15, 2020 9:19 pm

Thanks everyone, and a special one to Steve for making up a sample for me. Russ, your sort idea looks good but I would prefer to keep the data in date order. See the new screen shot below.

I'm a little confused though by some of the other suggestions, so how about I re-state the problem in different words. Excel will convert the time to 24-hour time which I have done, so it might be easier to find the time value without dealing with the AM/PM. The screen shot below is only a part of the sheet but I've included the rest of the columns, which I should have done in the first post. It's also important to know that this is a continuing record and more data will be added as time goes by. which makes the sorting solution a little awkward. At this point I have 62 records but I could have 100 or more.

As I understand your suggestions, they will give me an average of times. But what I am looking for are evaluations of the values in columns C, D and E. For example, in an ideal world, there would be a formula I could put into a cell that would do this: average the values in column C (or D or E) that were recorded between 00:00 hours and 11:59 hours. And then use the same formula for the values recored between 12:00 hours and 23:59 hours.

I also forgot to mention in my o.p. that I also need to pull out values within ranges. For example, I would like to know how many values in column C that were recorded between 00:00 hours and 11:59 hours, were between 90 and 120. Then repeat for values between 121 and 130, and so on. I have a formula that will give me specified ranges of values for the whole data set but I can't figure out how to break it out by time.

Does this make any more sense?
Screen Shot 2020-04-15 at 9.44.11 AM.png
Greg Lewis, Prop.
Eyeball Engineering — Home of the dull toolbit.
Our motto: "That looks about right."
Celebrating 30 years of turning perfectly good metal into bits of useless scrap.

User avatar
Greg_Lewis
Posts: 1992
Joined: Wed Jan 15, 2003 2:44 pm
Location: Fresno, CA

Re: Any Excel experts out there?

Post by Greg_Lewis » Wed Apr 15, 2020 11:51 pm

Got it! The time has to be referenced as a decimal. Here's the formula for pulling the values that I referenced above:

=COUNTIFS(C2:C100,">=130", C2:C100,"<=139",B2:B100,"<0.5")

So the time in column B needs to be either <.05 or >.05. And the answer to my first problem is to use AVERAGEIFS

Whew. How to the people who make these things up maintain their sanity?
Greg Lewis, Prop.
Eyeball Engineering — Home of the dull toolbit.
Our motto: "That looks about right."
Celebrating 30 years of turning perfectly good metal into bits of useless scrap.

User avatar
NP317
Posts: 2581
Joined: Tue Jun 24, 2014 2:57 pm
Location: Northern Oregon

Re: Any Excel experts out there?

Post by NP317 » Thu Apr 16, 2020 12:19 am

Excel is an extraordinary software package, capable of remarkable and complex evaluations.
And they call it a "spread sheet." Way under rated.

Good on you for solving your query. It was more complex than originally stated.
RussN

Post Reply