Excel
Solved/Closed
Related:
- Excel
- Excel date format dd.mm.yyyy - Guide
- Excel free download - Download - Spreadsheets
- Number to words in excel - Guide
- Excel marksheet - Guide
- How to take screenshot in excel - 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 :)
