I wonder what inconsistent formula actually means?

Just looked it up and apparently it means that the formula in adjacent cells
seem to follow a pattern, and the formula in that cell does not match the
formula.

So it seems reasonable to just ignore it.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Mendz5" wrote in message
...
Bob,

In 2003 when excel thinks there is an error it puts a little green
triangle
in the upper left corner of the cell. When you click on the flag, a drop
down appears with several options. At the top of the drop down it states
inconsistent formula. I selected ignore error.

The formula does appear to work properly. I took the earliest and the
lastest dates and plugged them into one of the formulas that I know work
and
received the same results.

So thanks again! This discussion group has been a great help.

Mendz

"Bob Phillips" wrote:

I don't know, I don't have Excel 2003 so I don't know what a green flag
actually means. Do you get the correct answer?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Mendz5" wrote in message
...
Bob,

After entering the formula a green flag appeared in the cell and
states
that
the the formula is inconsistent. Should I ignore it?

Mendz

"Bob Phillips" wrote:

Hi Mendz,

I have found a better solution

=IF(COUNT(J2:K2,M2:N2,Q2:R2)<2,"N/A",
ABS(NETWORKDAYS(MIN(J2:K2,M2:N2,Q2:R2),MAX(J2:K2,M 2:N2,Q2:R2))))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Mendz5" wrote in message
...
Bob,

I'm back with something even more complex:

6 columns, but they are not next to each other.

J2, K2, M2, N2, Q2, R2

If only 1 cell is populated, then I want to put "N/A" in the
target
cell

If 2 or more cells are populated, then I want to put the number of
days in
the target cell

I think I can create a formula if all cells are populated, but I'm
having
trouble figuring out how to exclude 1 or more cells that are
blank.

Thanks,

Mendz

If t

"Bob Phillips" wrote:

Ok.

First, I check if all 3 cells are completed

COUNTIF(A1:C1,"<")=3

If this is true, I determine the earliest (MIN(A1:C1)) and
latest
(MAX(A1:C1)) dates and calculate the difference using

NETWORKDAYS(MIN(A1:C1),MAX(A1:C1))

If they are not all completed I check if any two are

COUNTIF(A1:C1,"<")<2

and if so error with "N/A"

If any two are completed, then either A or B must be one of
them, so
I
get
the first date with

IF(A1<"",A1,B1)

then I determine the second date as either (A or B) or C,
depending
upon
whether the first chosen is A or B (for instance if A is
completed,
then
the
second date must be B or C). The formula for this is

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

I pass the two selected dates to NETWORKSDAYS and ABS it in case
I
don't
pass them in date order.

That's it.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"Mendz5" wrote in message
...
Bob,

Thank you so much, the formula works perfectly. I know the
basic
functions
of excel, could you explain the formula you created.

Thanks,

Mendz

"Bob Phillips" wrote:

Mendz,

No it was my error on the second part. This corrects the
always
1
problem





=IF(COUNTIF(A1:C1,"<")<2,"N/A",ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1=""
,B1=""),C1,B1))))

I though A1 and B1 and C1 was not a valid condition, so
didn't
cater
for
it.
What do you want to happen if they are all present as
NETWORKDAYS
only
works
on 2 dates. If you want earliest to latets then perhaps,


=IF(COUNTIF(A1:C1,"<")=3,NETWORKDAYS(MIN(A1:C1),M AX(A1:C1)),
IF(COUNTIF(A1:C1,"<")<2,"N/A",

ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(OR(A1="",B1="" ),C1,B1)))))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"Mendz5" wrote in message
...
Bob,

The formula almost works, unless I fat fingered something,
which
is
entirely
possible (I'll double check again).

When A1 & B1 are populated the calculation works.

When A1 & C1 are populated the calculation works.

When A1, B1 & C1 are populated, only A1 & B1 are added.

When B1 & C1 are popluated the result is always "1", no
matter
what I
put
in
C1.

Thanks,

Mendz

"Bob Phillips" wrote:






=IF(COUNTIF(A1:C1,"<")<2,"N/A",ABS(NETWORKDAYS(IF(A1<"",A1,B1),IF(B1<"",B
1,C1))))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if
mailing
direct)

"Mendz5" wrote in
message

...
I have 3 columns of dates (A1, B1, C1).
Several different scenarios:

1. If A1 is blank, but B1 & C1 are populated, I want
to
put
the
result
in
D1.

2. If B1 is blank, but A1 & C1 are populated, I want
to
put
the
result
in
D1.

3. If C1 is blank, but A1 & B1 are populated, I want
to
put
the
result
in
D1.

4. If A1 & B1 are blank, I want to put "N/A" in D1.

5. If A1 & C1 are blank, I want to put "N/A" in D1.

6. If B1 & C1 are blank, I want to put "N/A" in D1.

Why are there 3 date fields you ask, the powers that
be
want
it
that
way,
lucky me.

Thanks,

Mendz