How to average numbers generated by formulas?

Peter h

Active Member
Joined
Dec 8, 2015
Messages
417
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]Formula in N
[/TD]
[TD]Result of formula
[/TD]
[/TR]
[TR]
[TD]05/28/2019 05:15
[/TD]
[TD]05/28/2019 05:00
[/TD]
[TD]=IF(A3="","",TEXT(TIMEVALUE(TEXT(A3,"HH:mm"))-TIMEVALUE(TEXT(B3,"HH:mm")),"hh:mm"))
[/TD]
[TD]00:15
[/TD]
[/TR]
</tbody>[/TABLE]


This has to be simple, and don't know why I'm unable to think of how to do this, but I need some help.

I've got dates and times in columns A and B, and a formula in N that calculates the difference of those 2 times. What I want is a formula that shows me the average difference in the last 20 rows. So, here's what my data looks like.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
there must something you're not telling us . . because why is al this converting to text necessary?
N3: =A3-B3, copy down
N24: =AVERAGE(N3:N23)
 
Upvote 0
there must something you're not telling us . . because why is al this converting to text necessary?
N3: =A3-B3, copy down
N24: =AVERAGE(N3:N23)

The convertions to text is only because I'm an idiot that over complicates things... lol. I tried using A3-B3 and it returned 01/00/1900 00:24:02, and instead of realizing I could just change the format of the cell to hh:mm, I thought it was not reading the cells correctly. So I went the long way around extracting the hours and minutes as strings to get the desired results. Only when you answered my question did it dawn on me to just change the format of the cells. Thanks for enlightening me.
 
Upvote 0

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top