Calculating average - time

[tara_922]

Wait Time....Dif. Wait vs. ETA....ETA....Average Dif between Wait & ETA
4:00...........-1:00...................3:00....-1:00
3:00...........0:00.....................3:00...-0:30

This is what is should look like above. But what formula do I use to calculate the Average dif between Wait and ETA for a negative number also using if cell is blank return ""

Thanks for any help.

Tara

 

[DonkeyOte]

Which XL version are you using ? (eg XL2007+, XL2003)

Which Date System are you using ? (1900 or 1904)

 

[tara_922]

Hi there,

I'm using excel 2007 and using the 1900 date system.

Thanks,
Tara

P.S. can you explain the difference between the 1900 and 1904 system? Which is better and what are the advantages?

 

[DonkeyOte]

Generally speaking it is better to use your default system and be wary of switching...

PCs: 1900: Day 0 = 00-Jan-1900
Macs: 1904: Day 0 = 01-Jan-1904

The latter system allows you to display negative time values in Time format but changing date system mid-model is rarely a good idea (nor is it wise if you copy date values between files using different date systems)

To clarify further.... dates in XL are integers (date serials) and time is decimal (an hour being equivalent of 1/24 so 12 hours = 0.5)

If we assume you intend to persist with 1900 date system...

In your case your time values are negative and as such you can't display them in a time format - you could display them as numbers rather than Time - ie the values are valid, formatting is the issue.

Some people prefer to circumvent the formatting issue by displaying "hours" in decimal form such that -12:30 = -12.5 ... this we can do by multiplying the time value by 24 such that hours become equivalent of days (ie integer).

If you're adamant you want to keep the time format things become a little more convoluted.

Let us know your preference.

 

[tara_922]

Hiya!

I would prefer to use the easiest method possible. I would like it to display as if it were a time, but not necessarily have to be in time format.

Thanks for your help and the explanation!

Tara

 

[tara_922]

=IFERROR(IF(L6>G6,"-"&TEXT((L6-G6),"H:MM"),G6-L6),"")

This is the formula I'm using to get the negative time value right now for the difference between the actual wait time and the estimated wait time.

Thanks!

Tara

 

[DonkeyOte]

Tara, IMO, if you want to keep things simple as implied (running 1900) you'd be better off separating your calculations:

Excel Workbook
	A	B	C	D	E	F
1	Wait Time	Dif. Wait Time vs ETA	ETA	Average Dif. Wait Time vs ETA	calc1	calc2
2	04:01	-01:01:00	03:00	-01:01:00	-0.04236	-0.04236
3	03:00	00:00:00	03:00	-00:30:30	0	-0.02118
Sheet3

You can do this without E & F but things will become more complex for little value.

 

[tara_922]

Awesome! Thanks so much! I will try this as soon as I'm done with my audit.

P.S. I love your name!

Tara

 

[tara_922]

Hiya!

it's working well. I just had a couple questions. As much as I like being given formulas, I'd also like to understand what they mean so I can learn. can you explain what REPT and ABS does? And what does the $ do before the letter only and not the number?

Thanks for your help!

Tara

 

[MARK858]

Tara,
REPT repeats whatever is the string is a set number of times (See basic example) results in brackets
APS makes the number absolute i.e. it removes any +/- symbols (See basic example) results in brackets

The $ makes the range absolute i.e. if only the letter has it in front then it will only reference Row A but the column will increase by 1 as you copy and paste across.

Hope that covers the basics

Excel Workbook
	A	B	C
1	*	A	*
2	1	Data	*
3	2	-4	*
4	*	*	*
5	Formula	Description (Result)	*
6	=ABS(2)	Absolute value of 2 (2)	*
7	=ABS(-2)	Absolute value of -2 (2)	*
8	=ABS(A2)	Absolute value of -4 (4)	*
9	*	*	*
10	*	*	*
11	*	A	B
12	1	Formula	Description (Result)
13	2	=REPT("*-", 3)	Displays the string 3 times (*-*-*-)
14	3	=REPT("-",10)	Displays a dash 10 times (----------)
Sheet1