Excel If (or similar)

  • Thread starter Thread starter Andy Roberts
  • Start date Start date
A

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
 
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
 
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
 
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
 
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.)
 
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
 
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
 
Back
Top