Excel
Solved/Closed
Hello,
I have an excel file with Start dates, end dates, total day, and networkdays columns.
I would like to average these number for the average total days and average networkdays. But I cant because there are blank cells which will yield a #num! and #value! or -12400 number depending on the date...This means I cannot average the totals I would like to ....any help for a novice...Thanks,
I have an excel file with Start dates, end dates, total day, and networkdays columns.
I would like to average these number for the average total days and average networkdays. But I cant because there are blank cells which will yield a #num! and #value! or -12400 number depending on the date...This means I cannot average the totals I would like to ....any help for a novice...Thanks,
Related:
- Excel
- Excel mod apk for pc - Download - Spreadsheets
- Number to words in excel - Guide
- Gif in excel - Guide
- Kernel for excel - Download - Backup and recovery
- Excel marksheet - Guide
3 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Mar 12, 2010 at 04:26 AM
Mar 12, 2010 at 04:26 AM
Could you provide sample data? May be upload to a sharing site like https://authentification.site
here are some numbers
Calander days include weekends and holidays
Total Workdays reduce the amount of weekends and holidays from the total calander days
1 A B C D
2 Date Case is Open---------Date Case is Closed----------Total Calander Days--------Total Workdays
3 1/1/10 1/25/10 24 16
4 1/18/10 2/05/10 18 15
5 2/10/10 3/4/10 22 17
6 1/1/10 Pending #NUM! -28700
7 n/a n/a #VALUE!
I would like to know the average total calander days and average total workdays but i cant derive this number because of the errors and (-) numbers skewing the average.
Hope this helps clarify the problem.
Calander days include weekends and holidays
Total Workdays reduce the amount of weekends and holidays from the total calander days
1 A B C D
2 Date Case is Open---------Date Case is Closed----------Total Calander Days--------Total Workdays
3 1/1/10 1/25/10 24 16
4 1/18/10 2/05/10 18 15
5 2/10/10 3/4/10 22 17
6 1/1/10 Pending #NUM! -28700
7 n/a n/a #VALUE!
I would like to know the average total calander days and average total workdays but i cant derive this number because of the errors and (-) numbers skewing the average.
Hope this helps clarify the problem.
Here are some numbers
Calander days include weekends and holidays
Total Workdays reduce the amount of weekends and holidays from the total calander days
1 ------A------------------------------- B----------------------------- C----------------------------- D
2--- Date Case is Open---------Date Case is Closed----------Total Calander Days--------Total Workdays
3 ---1/1/10 -----------------------------1/25/10 -----------------------------24 ----------------------16
4--- 1/18/10 -----------------------------2/05/10 ---------------------------18 -----------------------15
5--- 2/10/10----------------------------- 3/4/10----------------------------- 22 ----------------------17
6--- 1/1/10----------------------------- Pending----------------------------- #NUM! ----------------(-28700)
7 ---n/a ----------------------------------n/a --------------------------------#VALUE! -----------------------------
I would like to know the average total calander days and average total workdays but i cant derive this number because of the errors and (-) numbers skewing the average.
Hope this helps clarify the problem.
Calander days include weekends and holidays
Total Workdays reduce the amount of weekends and holidays from the total calander days
1 ------A------------------------------- B----------------------------- C----------------------------- D
2--- Date Case is Open---------Date Case is Closed----------Total Calander Days--------Total Workdays
3 ---1/1/10 -----------------------------1/25/10 -----------------------------24 ----------------------16
4--- 1/18/10 -----------------------------2/05/10 ---------------------------18 -----------------------15
5--- 2/10/10----------------------------- 3/4/10----------------------------- 22 ----------------------17
6--- 1/1/10----------------------------- Pending----------------------------- #NUM! ----------------(-28700)
7 ---n/a ----------------------------------n/a --------------------------------#VALUE! -----------------------------
I would like to know the average total calander days and average total workdays but i cant derive this number because of the errors and (-) numbers skewing the average.
Hope this helps clarify the problem.
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Mar 12, 2010 at 12:16 PM
Mar 12, 2010 at 12:16 PM
Why not enclose the all with in IF and ISERROR
=IF(ISERROR(B3-A3),"", B3-A3)
and
=IF(ISERROR(NETWORKDAYS(A3,B3)), "", NETWORKDAYS(A3,B3)))
If blank cells are issue then you can try this
=IF(AND(ISNUMBER(A3), ISNUMBER(B3)), B3-A3, "")
AND
=IF(AND(ISNUMBER(A3), ISNUMBER(B3)), NETWORKDAYS(A3,B3), "")
=IF(ISERROR(B3-A3),"", B3-A3)
and
=IF(ISERROR(NETWORKDAYS(A3,B3)), "", NETWORKDAYS(A3,B3)))
If blank cells are issue then you can try this
=IF(AND(ISNUMBER(A3), ISNUMBER(B3)), B3-A3, "")
AND
=IF(AND(ISNUMBER(A3), ISNUMBER(B3)), NETWORKDAYS(A3,B3), "")
mike
>
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
Mar 12, 2010 at 01:25 PM
Mar 12, 2010 at 01:25 PM
Thanks, that worked :)