Excel If (or similar)

[Andy Roberts]

I have a cell which calculates the amount of working days between 2 dates
(start date and end date) using:-
=NETWORKDAYS(A1,B1)

This works fine. What is happening is that until I fill both dates in I get
large negative answers (which I understand). I would like the computation
to be clear if the answer is less than 0 as the spreadsheet is difficult to
read with all these large menaingless numbers which only become readable
when both dates are completed but it may be several weeks before I complete
the end date.

I know its something to do with the IF command but I can't figure it out.
Can anyone shed any light?

Win XP
Excel 2007

 

[Bernard Liengme]

How about: =IF(COUNT(A1:B1)=2,NETWORKDAYS(A1,B1),"X")
You can delete the X leaving just two double quotes with nothing between to
have a blank cell, or replace X by your own message
best wishes

 

[Pete_UK]

You could do it this way:

=IF(B1>A1,NETWORKDAYS(A1,B1),"")

or like this:

=IF(OR(A1="",B1=""),"",NETWORKDAYS(A1,B1))

Hope this helps.

Pete

 

[Andy Roberts]

Bernard

Thanks

This works fine except if there are actually dates in both cells, I still
get a blacnk cell. I only want it blank if there ARENT both dates. I tried
:-

=IF(COUNT(A1:B1)<2,NETWORKDAYS(A1:B1)," ")

but it still gives a blank cell.

Andy

 

[Dave Peterson]

At least one of those "dates" isn't really a date.

Try reformatting the cell (or both cells) as date and reenter the values.

Then use the formula that Bernard suggested. (You have typos in yours.)

 

[Andy Roberts]

Pete

2nd one works perfectly, thanks

On a similar subject can I do the same thing with the #DIV message.

The scenario is that I have the number of working days calculated between
two dates as above for each month of the year. I then have an average total
cell for each entry for each month, so a Jan average working days, Feb
average working days etc which then is summarised on a different worksheet
and a yearly average of all the monthly averages.

Everything works in principal, except the #DIV meaasge appears in the total
for each month if the month has no data (as I presume you cant have an
average of 0). This caused a problem in that the summary sheet has some
months hsowing ~DIv and therefore the yearly average to wont calculate.

How do I get around this problem?

Apologies if the description isn't great.

You could do it this way:

=IF(B1>A1,NETWORKDAYS(A1,B1),"")

or like this:

=IF(OR(A1="",B1=""),"",NETWORKDAYS(A1,B1))

Hope this helps.

Pete

 

[Pete_UK]

You get #DIV0 when you try to divide by zero, so suppose you have
something like:

=SUM(A1:A10) / COUNT(A1:A10)

to get your averages, then you can check on the divisor like this:

=IF(COUNT(A1:A10)=0,"",SUM(A1:A10) / COUNT(A1:A10))

or like this if you are using the AVERAGE function:

=IF(COUNT(A1:A10)=0,"",AVERAGE(A1:A10))

Change the range to suit your data.

Hope this helps.

Pete