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,

3 replies

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Mar 12, 2010 at 04:26 AM
Could you provide sample data? May be upload to a sharing site like https://authentification.site
0
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.
0
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.
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
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), "")
0
mike > rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022
Mar 12, 2010 at 01:25 PM
Thanks, that worked :)
0