Formula to Find the Average of Negative Time

L

Lukma

Active Member
Joined
Feb 12, 2020
Messages
259
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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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

S
  • Solved
Formula to split date and time and work out energy usage trend
Replies
6
Views
470
Sundance_Kid
S
L
  • Question Question
Formula Help with Count of Week Numbers for every Each Month
2
Replies
19
Views
1K
Armstrong_N14
A
L
  • Solved
Formula Of IF Time Less than 30 minutes Met Or Not Met
Replies
2
Views
2K
Lukma
L
L
  • Solved
Help with a Formula of IF greater than or Less than or with a Negative time sign
Replies
6
Views
787
Peter_SSs
Peter_SSs
welshgasman
  • Question Question
Formulae jumping references?
Replies
7
Views
101
welshgasman
welshgasman

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

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