Excel

Solved/Closed
Mike - Mar 11, 2010 at 02:00 PM
 mike - Mar 12, 2010 at 01:25 PM
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,
Related:

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
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.
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.
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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), "")
mike > rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022
Mar 12, 2010 at 01:25 PM
Thanks, that worked :)