Formula to Find the Average of Negative Time

L

Lukma

Board Regular
Joined
Feb 12, 2020
Messages
240
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi friends

I came across some articles, but am not too sure about it, not until I have my own data with a negative sign.

So I really don't know if I can get the average of the negative sign in Column E in my data, based on monthly criteria

appreciate someone giving me a solution, I tried all I could but still can't seem to get it right. In Column B2 where I need the formula to be


Thanks

OSMS-Onshore Jetty Operations vessels Tracking.xlsx
ABCDE
1May 23Average of NegativeAverage of Positive
2#DIV/0!0.31
3
4Mus NumSupply VesselArrival time FWB Permission Granted Total Time in Days Spent Return
5MayCECILIE-K01/05/23 10:0001/05/23 13:400.15
6MayZ-OCEAN01/05/23 14:3001/05/23 21:570.31
7MaySWISSCO RUBY02/05/23 06:0001/05/23 15:00-15:00
8MayLCT-SHEWELEH01/05/23 16:0001/05/23 16:570.04
9MayA-RANGER01/05/23 20:0001/05/23 20:140.01
10MayADNOC-S0201/05/23 21:1001/05/23 19:30-1:40
11MayADNOC-81001/05/23 01:5001/05/23 19:300.74
12MayADNOC-51101/05/23 22:1002/05/23 01:590.16
13MaySK MAINSTAY02/05/23 06:0502/05/23 01:32-4:33
14MayMUTAWA-40201/05/23 10:0002/05/23 05:200.81
15MayADNOC-51202/05/23 08:1002/05/23 09:000.03
16MayCECILIE-K02/05/23 09:4002/05/23 00:00-9:40
17MayZ-POWER02/05/23 04:2002/05/23 11:000.28
18MayADNOC-81102/05/23 09:5502/05/23 00:00-9:55
19MayADNOC-22202/05/23 08:2002/05/23 00:00-8:20
20MaySMIT LUMUT02/05/23 18:0002/05/23 13:00-5:00
21MayA-RADIANT-702/05/23 09:0002/05/23 14:000.21
22MayLCT MARWAH-102/05/23 06:0002/05/23 15:000.38
23MayADNOC-85102/05/23 17:0002/05/23 15:00-2:00
24MayAMS-ONYX02/05/23 10:5002/05/23 15:200.19
25MayATLAS-SAPPHIRE02/05/23 02:0002/05/23 16:550.62
26MayM-SUPPORTER02/05/23 14:0002/05/23 17:350.15
27MayZ-OCEAN02/05/23 10:3002/05/23 18:590.35
28MayLCT-SHEWELEH02/05/23 11:1002/05/23 22:140.46
Sheet2
Cell Formulas
RangeFormula
B2B2=AVERAGEIFS(E5:E28,E5:E28,"<0",A5:A28,A1)
C2C2=AVERAGEIFS(E5:E28,E5:E28,">0",A5:A28,A1)
A5:A28A5=IFERROR(EOMONTH(C5,-1)+1,"")
E5:E28E5=IFERROR(IF((D5-C5)>0,(D5-C5),TEXT(ABS(D5-C5),"-h:mm")),"")
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
@Lukma Your formula in E is creating 'negative' values as text.
Averageifs cannot deal with that.
Can you not return values as number similar to positives?
 
Upvote 0
See if the following formula works for you:
Excel Formula: 
=SUBSTITUTE(ROUND(DOLLARFR(SUMPRODUCT(D5:D28-C5:C28,--(D5:D28<C5:C28),--(A5:A28=A1))/SUMPRODUCT(--(D5:D28<C5:C28),--(A5:A28=A1))*24,60),2),".",":")
 
Upvote 0
Solution
Dear Tetra

Many thanks, right
Tetra201 said:
See if the following formula works for you:
Excel Formula: 
=SUBSTITUTE(ROUND(DOLLARFR(SUMPRODUCT(D5:D28-C5:C28,--(D5:D28<C5:C28),--(A5:A28=A1))/SUMPRODUCT(--(D5:D28<C5:C28),--(A5:A28=A1))*24,60),2),".",":")
Click to expand...
Dear Tetra

Many thanks, it work just as i wanted it.

Regards
 
Upvote 0
An alternative solution which is perhaps of more general use is to change the date reference point in Options - Advanced - 'When calculating this workbook': set it to use 1904 date system. Excel then handles negative dates and times without problem and is ideal for this sort of situation. In the sheet below column F is the simple difference between the dates and the formula in B2 is the simple AVERAGEIFS. Worth thinking about for the future. I also had to make sure that the dates in column A matched the date in cell A1.

Book1
ABCDEF
1MayAverage of NegativeAverage of Positive
2-0.290.31
3
4Mus NumSupply VesselArrival time FWB Permission Granted Total Time in Days Spent Return (original)Total Time in Days Spent Return
5MayCECILIE-K02/05/2027 10:0002/05/2027 13:4003:4003:40
6MayZ-OCEAN02/05/2027 14:3002/05/2027 21:5707:2707:27
7MaySWISSCO RUBY03/05/2027 06:0002/05/2027 15:00-15:00-15:00
8MayLCT-SHEWELEH02/05/2027 16:0002/05/2027 16:5700:5700:57
9MayA-RANGER02/05/2027 20:0002/05/2027 20:1400:1400:14
10MayADNOC-S0202/05/2027 21:1002/05/2027 19:30-1:40-01:40
11MayADNOC-81002/05/2027 01:5002/05/2027 19:3017:4017:40
12MayADNOC-51102/05/2027 22:1003/05/2027 01:5903:4903:49
13MaySK MAINSTAY03/05/2027 06:0503/05/2027 01:32-4:33-04:33
14MayMUTAWA-40202/05/2027 10:0003/05/2027 05:2019:2019:20
15MayADNOC-51203/05/2027 08:1003/05/2027 09:0000:5000:50
16MayCECILIE-K03/05/2027 09:4003/05/2027 00:00-9:40-09:40
17MayZ-POWER03/05/2027 04:2003/05/2027 11:0006:4006:40
18MayADNOC-81103/05/2027 09:5503/05/2027 00:00-9:55-09:55
19MayADNOC-22203/05/2027 08:2003/05/2027 00:00-8:20-08:20
20MaySMIT LUMUT03/05/2027 18:0003/05/2027 13:00-5:00-05:00
21MayA-RADIANT-703/05/2027 09:0003/05/2027 14:0005:0005:00
22MayLCT MARWAH-103/05/2027 06:0003/05/2027 15:0009:0009:00
23MayADNOC-85103/05/2027 17:0003/05/2027 15:00-2:00-02:00
24MayAMS-ONYX03/05/2027 10:5003/05/2027 15:2004:3004:30
25MayATLAS-SAPPHIRE03/05/2027 02:0003/05/2027 16:5514:5514:55
26MayM-SUPPORTER03/05/2027 14:0003/05/2027 17:3503:3503:35
27MayZ-OCEAN03/05/2027 10:3003/05/2027 18:5908:2908:29
28MayLCT-SHEWELEH03/05/2027 11:1003/05/2027 22:1411:0411:04
Sheet2
Cell Formulas
RangeFormula
B2B2=AVERAGEIFS(F5:F28,F5:F28,"<0",A5:A28,A1)
C2C2=AVERAGEIFS(F5:F28,A5:A28,A1,F5:F28,">=0")
E5:E28E5=IFERROR(IF((D5-C5)>0,(D5-C5),TEXT(ABS(D5-C5),"-h:mm")),"")
F5:F28F5=D5-C5
A5:A28A5=IFERROR(EOMONTH(C5,-1)+1,"")


Hope that helps.
 
Upvote 0
pjmorris said:
An alternative solution which is perhaps of more general use is to change the date reference point in Options - Advanced - 'When calculating this workbook': set it to use 1904 date system. Excel then handles negative dates and times without problem and is ideal for this sort of situation. In the sheet below column F is the simple difference between the dates and the formula in B2 is the simple AVERAGEIFS. Worth thinking about for the future. I also had to make sure that the dates in column A matched the date in cell A1.

Book1
ABCDEF
1MayAverage of NegativeAverage of Positive
2-0.290.31
3
4Mus NumSupply VesselArrival time FWB Permission Granted Total Time in Days Spent Return (original)Total Time in Days Spent Return
5MayCECILIE-K02/05/2027 10:0002/05/2027 13:4003:4003:40
6MayZ-OCEAN02/05/2027 14:3002/05/2027 21:5707:2707:27
7MaySWISSCO RUBY03/05/2027 06:0002/05/2027 15:00-15:00-15:00
8MayLCT-SHEWELEH02/05/2027 16:0002/05/2027 16:5700:5700:57
9MayA-RANGER02/05/2027 20:0002/05/2027 20:1400:1400:14
10MayADNOC-S0202/05/2027 21:1002/05/2027 19:30-1:40-01:40
11MayADNOC-81002/05/2027 01:5002/05/2027 19:3017:4017:40
12MayADNOC-51102/05/2027 22:1003/05/2027 01:5903:4903:49
13MaySK MAINSTAY03/05/2027 06:0503/05/2027 01:32-4:33-04:33
14MayMUTAWA-40202/05/2027 10:0003/05/2027 05:2019:2019:20
15MayADNOC-51203/05/2027 08:1003/05/2027 09:0000:5000:50
16MayCECILIE-K03/05/2027 09:4003/05/2027 00:00-9:40-09:40
17MayZ-POWER03/05/2027 04:2003/05/2027 11:0006:4006:40
18MayADNOC-81103/05/2027 09:5503/05/2027 00:00-9:55-09:55
19MayADNOC-22203/05/2027 08:2003/05/2027 00:00-8:20-08:20
20MaySMIT LUMUT03/05/2027 18:0003/05/2027 13:00-5:00-05:00
21MayA-RADIANT-703/05/2027 09:0003/05/2027 14:0005:0005:00
22MayLCT MARWAH-103/05/2027 06:0003/05/2027 15:0009:0009:00
23MayADNOC-85103/05/2027 17:0003/05/2027 15:00-2:00-02:00
24MayAMS-ONYX03/05/2027 10:5003/05/2027 15:2004:3004:30
25MayATLAS-SAPPHIRE03/05/2027 02:0003/05/2027 16:5514:5514:55
26MayM-SUPPORTER03/05/2027 14:0003/05/2027 17:3503:3503:35
27MayZ-OCEAN03/05/2027 10:3003/05/2027 18:5908:2908:29
28MayLCT-SHEWELEH03/05/2027 11:1003/05/2027 22:1411:0411:04
Sheet2
Cell Formulas
RangeFormula
B2B2=AVERAGEIFS(F5:F28,F5:F28,"<0",A5:A28,A1)
C2C2=AVERAGEIFS(F5:F28,A5:A28,A1,F5:F28,">=0")
E5:E28E5=IFERROR(IF((D5-C5)>0,(D5-C5),TEXT(ABS(D5-C5),"-h:mm")),"")
F5:F28F5=D5-C5
A5:A28A5=IFERROR(EOMONTH(C5,-1)+1,"")


Hope that helps.
Click to expand...
Dear pjmorris.

Many Thanks, I appreciate the learning, and I will always consider that which is a lot easy. however, in case of data is sent to others will 1904 setting remains.

Regards

​

 
Upvote 0
Hi,

I believe the setting is relevant to the spreadsheet and should therefore be correct when opened by others. I would send it to a colleague and see what happens. Unfortunately I can't do so here as this is a home computer.
PS. It would also simplify the solution to your issue about Met/Not Met.

All the best.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
Formula Help with Count of Week Numbers for every Each Month
2
Replies
19
Views
979
Armstrong_N14
A
L
  • Solved
Formula Of IF Time Less than 30 minutes Met Or Not Met
Replies
2
Views
881
Lukma
L
L
  • Solved
Help with a Formula of IF greater than or Less than or with a Negative time sign
Replies
6
Views
553
Peter_SSs
Peter_SSs
L
  • Solved
Help With my Formula in Column I
Replies
5
Views
318
Sufiyan97
Sufiyan97
L
  • Question
Help with IF formula with Negative Time YES OR NO
Replies
1
Views
408
etaf
etaf

Forum statistics

Threads
1,215,111
Messages
6,123,152
Members
449,098
Latest member
Doanvanhieu

Share this page

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