Excel 2016 Time Avg Issue
i have some raw data that I'm trying to calculate avg time from stage to stage however running into issues with #VALUE!
Stage 1
Column A has date in format 01/01/2000
Column B has time in format hh:mm:ss
Column C had a formula which combines the two above columns (A and B) into 1 column. =TEXT(A2,"mm/dd/yyyy")&" " &TEXT(B2,"hh:mm:ss")
Stage 2
Column D has date in format 01/01/2000
Column E has time in format hh:mm:ss
Column F has a formula which combines the two above columns (D and F) into 1 column. =TEXT(E2,"mm/dd/yyyy")&" " &TEXT(F2,"hh:mm:ss")
now here's where the problem comes in for me.
Column G is where I need to determine the amount the amount of time difference between column C and column F. A couple of caveats first. Sometimes Column A/B/C will not have a time/date in them but D/E/F will. when this happens, a #value! is displayed.
so through about 500 rows I need to determine the avg of time for column G. Can't get it to work. Always generates a #value!
So I'm either doing something wrong, or it's not possible to do with where dates are not all input.
I do have it set in a pivot table
I have tried filtering
I have tried conditional formatting
all to no avail.
Book1.xlsx
Stage 1
Column A has date in format 01/01/2000
Column B has time in format hh:mm:ss
Column C had a formula which combines the two above columns (A and B) into 1 column. =TEXT(A2,"mm/dd/yyyy")&" " &TEXT(B2,"hh:mm:ss")
Stage 2
Column D has date in format 01/01/2000
Column E has time in format hh:mm:ss
Column F has a formula which combines the two above columns (D and F) into 1 column. =TEXT(E2,"mm/dd/yyyy")&" " &TEXT(F2,"hh:mm:ss")
now here's where the problem comes in for me.
Column G is where I need to determine the amount the amount of time difference between column C and column F. A couple of caveats first. Sometimes Column A/B/C will not have a time/date in them but D/E/F will. when this happens, a #value! is displayed.
so through about 500 rows I need to determine the avg of time for column G. Can't get it to work. Always generates a #value!
So I'm either doing something wrong, or it's not possible to do with where dates are not all input.
I do have it set in a pivot table
I have tried filtering
I have tried conditional formatting
all to no avail.
Book1.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
yo_bee
Glad to help.
In the column where you calcuate the difference you need to add a if statement to ensure the fileds are populated.
If you do this you will not get the #value and the average will work.
If you do not know how to do this I will help , just ask
Kind regards
Eric
If you do this you will not get the #value and the average will work.
If you do not know how to do this I will help , just ask
Kind regards
Eric