conditional formula with lots of conditions

i am trying to develop a conditional formula. the spreadsheet is separated
into sections, and in each 4-column section, there are 2 columns that contain
dates. the 3rd contains a formula to subtract one from the other to show the
number of days in between.

the problem is that some of the original 2-columns do not contain values. i
have figured out how to make the formula in the 3rd column show "N/A" if one
of the columns has no value. the issue now is in any of the NEXT 4-column
sections, IF there is a date value in that row, i want it to go back to the
last cell in that row that had a date in it and subtract THAT date.

for example: D5= 3/31/05, E5= 4/7/05, formula in F5 calculates "7".

If D10= "N/A" and E10= 4/14/05, F10 calcuates "N/A" (which is what i want).

But then H10="N/A" and I10= 8/29/05. Instead of J10 caluclating "N/A"
(which is what my current formula,
=IF(AND(COUNT(H10),COUNT(I10)),I10-H10,"N/A"), produces, I want a formula
that will see that since H10 is "N/A," it needs to go back and check first in
E10 and then in D10 and subtract the date from whichever of those it picks up
a date in first. So I want it to end up doing I10-E10, but I want to make it
conditional so I can make it consistent across the entire spreadsheet to
account for times when there are "N/A's" in the date fields.

This is in Excel 2003

=IF(AND(COUNT(H10),COUNT(I10)),I10-H10,"N/A")

You can reduce that to:

=IF(COUNT(H10,I10)=2,I10-H10,"N/A")

Ok, I'm confused as to where to get the *last* date to include in the
calculation. Can you reword your description?

--
Biff
Microsoft Excel MVP


"sarajane82" wrote in message
...
i am trying to develop a conditional formula. the spreadsheet is separated
into sections, and in each 4-column section, there are 2 columns that
contain
dates. the 3rd contains a formula to subtract one from the other to show
the
number of days in between.

the problem is that some of the original 2-columns do not contain values.
i
have figured out how to make the formula in the 3rd column show "N/A" if
one
of the columns has no value. the issue now is in any of the NEXT 4-column
sections, IF there is a date value in that row, i want it to go back to
the
last cell in that row that had a date in it and subtract THAT date.

for example: D5= 3/31/05, E5= 4/7/05, formula in F5 calculates "7".

If D10= "N/A" and E10= 4/14/05, F10 calcuates "N/A" (which is what i
want).

But then H10="N/A" and I10= 8/29/05. Instead of J10 caluclating "N/A"
(which is what my current formula,
=IF(AND(COUNT(H10),COUNT(I10)),I10-H10,"N/A"), produces, I want a formula
that will see that since H10 is "N/A," it needs to go back and check first
in
E10 and then in D10 and subtract the date from whichever of those it picks
up
a date in first. So I want it to end up doing I10-E10, but I want to make
it
conditional so I can make it consistent across the entire spreadsheet to
account for times when there are "N/A's" in the date fields.

This is in Excel 2003

I will try! For example, if there is a date in H10, but not in I10, it will
come up with N/A. But then later on in the spreadsheet, there may be a date
in L10 (these dates will always be sequential, so the latter is always more
recent). So I want a formula that will take L10 and subtract from it the
most recent date before it, whether it be in H10 or I10...it's like I want
the formula to use L10 and if there is a date in I10, subtract that. But if
there isn't a date in I10, I want it to check H10 and if there is a date
there, I want it to subtract that.

I want it to check certain previous cells until it finds one with a date in
it and subtract that from L10.

"T. Valko" wrote:

=IF(AND(COUNT(H10),COUNT(I10)),I10-H10,"N/A")

You can reduce that to:

=IF(COUNT(H10,I10)=2,I10-H10,"N/A")

Ok, I'm confused as to where to get the *last* date to include in the
calculation. Can you reword your description?

--
Biff
Microsoft Excel MVP


"sarajane82" wrote in message
...
i am trying to develop a conditional formula. the spreadsheet is separated
into sections, and in each 4-column section, there are 2 columns that
contain
dates. the 3rd contains a formula to subtract one from the other to show
the
number of days in between.

the problem is that some of the original 2-columns do not contain values.
i
have figured out how to make the formula in the 3rd column show "N/A" if
one
of the columns has no value. the issue now is in any of the NEXT 4-column
sections, IF there is a date value in that row, i want it to go back to
the
last cell in that row that had a date in it and subtract THAT date.

for example: D5= 3/31/05, E5= 4/7/05, formula in F5 calculates "7".

If D10= "N/A" and E10= 4/14/05, F10 calcuates "N/A" (which is what i
want).

But then H10="N/A" and I10= 8/29/05. Instead of J10 caluclating "N/A"
(which is what my current formula,
=IF(AND(COUNT(H10),COUNT(I10)),I10-H10,"N/A"), produces, I want a formula
that will see that since H10 is "N/A," it needs to go back and check first
in
E10 and then in D10 and subtract the date from whichever of those it picks
up
a date in first. So I want it to end up doing I10-E10, but I want to make
it
conditional so I can make it consistent across the entire spreadsheet to
account for times when there are "N/A's" in the date fields.

This is in Excel 2003

these dates will always be sequential
so the latter is always more recent

Maybe this:

=IF(COUNT(L10),L10-MAX(H10,I10),"N/A")

--
Biff
Microsoft Excel MVP


"sarajane82" wrote in message
...
I will try! For example, if there is a date in H10, but not in I10, it
will
come up with N/A. But then later on in the spreadsheet, there may be a
date
in L10 (these dates will always be sequential, so the latter is always
more
recent). So I want a formula that will take L10 and subtract from it the
most recent date before it, whether it be in H10 or I10...it's like I want
the formula to use L10 and if there is a date in I10, subtract that. But
if
there isn't a date in I10, I want it to check H10 and if there is a date
there, I want it to subtract that.

I want it to check certain previous cells until it finds one with a date
in
it and subtract that from L10.

"T. Valko" wrote:

=IF(AND(COUNT(H10),COUNT(I10)),I10-H10,"N/A")

You can reduce that to:

=IF(COUNT(H10,I10)=2,I10-H10,"N/A")

Ok, I'm confused as to where to get the *last* date to include in the
calculation. Can you reword your description?

--
Biff
Microsoft Excel MVP


"sarajane82" wrote in message
...
i am trying to develop a conditional formula. the spreadsheet is
separated
into sections, and in each 4-column section, there are 2 columns that
contain
dates. the 3rd contains a formula to subtract one from the other to
show
the
number of days in between.

the problem is that some of the original 2-columns do not contain
values.
i
have figured out how to make the formula in the 3rd column show "N/A"
if
one
of the columns has no value. the issue now is in any of the NEXT
4-column
sections, IF there is a date value in that row, i want it to go back to
the
last cell in that row that had a date in it and subtract THAT date.

for example: D5= 3/31/05, E5= 4/7/05, formula in F5 calculates "7".

If D10= "N/A" and E10= 4/14/05, F10 calcuates "N/A" (which is what i
want).

But then H10="N/A" and I10= 8/29/05. Instead of J10 caluclating "N/A"
(which is what my current formula,
=IF(AND(COUNT(H10),COUNT(I10)),I10-H10,"N/A"), produces, I want a
formula
that will see that since H10 is "N/A," it needs to go back and check
first
in
E10 and then in D10 and subtract the date from whichever of those it
picks
up
a date in first. So I want it to end up doing I10-E10, but I want to
make
it
conditional so I can make it consistent across the entire spreadsheet
to
account for times when there are "N/A's" in the date fields.

This is in Excel 2003

Thanks, I am getting closer! This worked, but now I am realizing that it is
a bit more complicated than that. So let's suppose I have:

D10=N/A
E10=4/14/2005
so F10= N/A (formula of E10-D10)

then...
H10=8/29/2005
I10= N/A
so I need J10 to calculate H10-E10.

I need the formula in J10 (and all other columns like this one in the sheet)
to use the most recent date and subtract the next most recent date from it,
even if it's not in the column right next to it. Like, if I10 DID have a
value in it, I would want J10 to have I10-H10, because that's the most recent
date minus the next most recent date. But if, for example, H10 had N/A and
I10 had 8/29/2005, I would want J10 to calucate I10 minus E10, because that
would be the most recent minus the next most recent.

If both H10 and I10 were N/A, I would want the formula in J10 to compute
"N/A" because the most recent time difference would already be accounted for
in F10. In this same scenario, if E10 was 4/14/2005, H10 and I10 were N/A,
and L10 was 8/20/2006, I would want the formula in N10 to calucate L10 minus
E10, or the closest date to the most recent date.

i dont know if that makes any sense. I really appreciate your help so far.
i am so close to having it how i want!

"T. Valko" wrote:

these dates will always be sequential
so the latter is always more recent

Maybe this:

=IF(COUNT(L10),L10-MAX(H10,I10),"N/A")

--
Biff
Microsoft Excel MVP


"sarajane82" wrote in message
...
I will try! For example, if there is a date in H10, but not in I10, it
will
come up with N/A. But then later on in the spreadsheet, there may be a
date
in L10 (these dates will always be sequential, so the latter is always
more
recent). So I want a formula that will take L10 and subtract from it the
most recent date before it, whether it be in H10 or I10...it's like I want
the formula to use L10 and if there is a date in I10, subtract that. But
if
there isn't a date in I10, I want it to check H10 and if there is a date
there, I want it to subtract that.

I want it to check certain previous cells until it finds one with a date
in
it and subtract that from L10.

"T. Valko" wrote:

=IF(AND(COUNT(H10),COUNT(I10)),I10-H10,"N/A")

You can reduce that to:

=IF(COUNT(H10,I10)=2,I10-H10,"N/A")

Ok, I'm confused as to where to get the *last* date to include in the
calculation. Can you reword your description?

--
Biff
Microsoft Excel MVP


"sarajane82" wrote in message
...
i am trying to develop a conditional formula. the spreadsheet is
separated
into sections, and in each 4-column section, there are 2 columns that
contain
dates. the 3rd contains a formula to subtract one from the other to
show
the
number of days in between.

the problem is that some of the original 2-columns do not contain
values.
i
have figured out how to make the formula in the 3rd column show "N/A"
if
one
of the columns has no value. the issue now is in any of the NEXT
4-column
sections, IF there is a date value in that row, i want it to go back to
the
last cell in that row that had a date in it and subtract THAT date.

for example: D5= 3/31/05, E5= 4/7/05, formula in F5 calculates "7".

If D10= "N/A" and E10= 4/14/05, F10 calcuates "N/A" (which is what i
want).

But then H10="N/A" and I10= 8/29/05. Instead of J10 caluclating "N/A"
(which is what my current formula,
=IF(AND(COUNT(H10),COUNT(I10)),I10-H10,"N/A"), produces, I want a
formula
that will see that since H10 is "N/A," it needs to go back and check
first
in
E10 and then in D10 and subtract the date from whichever of those it
picks
up
a date in first. So I want it to end up doing I10-E10, but I want to
make
it
conditional so I can make it consistent across the entire spreadsheet
to
account for times when there are "N/A's" in the date fields.

This is in Excel 2003

I'm not following you on this.

How many groups of cells do you have? It seems that with each new group to
the right you'd have to expand it to look back at every previous group to
the left.

If these are your groups:

D10:F10
H10:J10
L10:N10

Then what's in the cells between groups?

What's in G10, K10?

--
Biff
Microsoft Excel MVP


"sarajane82" wrote in message
...
Thanks, I am getting closer! This worked, but now I am realizing that it
is
a bit more complicated than that. So let's suppose I have:

D10=N/A
E10=4/14/2005
so F10= N/A (formula of E10-D10)

then...
H10=8/29/2005
I10= N/A
so I need J10 to calculate H10-E10.

I need the formula in J10 (and all other columns like this one in the
sheet)
to use the most recent date and subtract the next most recent date from
it,
even if it's not in the column right next to it. Like, if I10 DID have a
value in it, I would want J10 to have I10-H10, because that's the most
recent
date minus the next most recent date. But if, for example, H10 had N/A
and
I10 had 8/29/2005, I would want J10 to calucate I10 minus E10, because
that
would be the most recent minus the next most recent.

If both H10 and I10 were N/A, I would want the formula in J10 to compute
"N/A" because the most recent time difference would already be accounted
for
in F10. In this same scenario, if E10 was 4/14/2005, H10 and I10 were
N/A,
and L10 was 8/20/2006, I would want the formula in N10 to calucate L10
minus
E10, or the closest date to the most recent date.

i dont know if that makes any sense. I really appreciate your help so
far.
i am so close to having it how i want!

"T. Valko" wrote:

these dates will always be sequential
so the latter is always more recent

Maybe this:

=IF(COUNT(L10),L10-MAX(H10,I10),"N/A")

--
Biff
Microsoft Excel MVP


"sarajane82" wrote in message
...
I will try! For example, if there is a date in H10, but not in I10, it
will
come up with N/A. But then later on in the spreadsheet, there may be a
date
in L10 (these dates will always be sequential, so the latter is always
more
recent). So I want a formula that will take L10 and subtract from it
the
most recent date before it, whether it be in H10 or I10...it's like I
want
the formula to use L10 and if there is a date in I10, subtract that.
But
if
there isn't a date in I10, I want it to check H10 and if there is a
date
there, I want it to subtract that.

I want it to check certain previous cells until it finds one with a
date
in
it and subtract that from L10.

"T. Valko" wrote:

=IF(AND(COUNT(H10),COUNT(I10)),I10-H10,"N/A")

You can reduce that to:

=IF(COUNT(H10,I10)=2,I10-H10,"N/A")

Ok, I'm confused as to where to get the *last* date to include in the
calculation. Can you reword your description?

--
Biff
Microsoft Excel MVP


"sarajane82" wrote in message
...
i am trying to develop a conditional formula. the spreadsheet is
separated
into sections, and in each 4-column section, there are 2 columns
that
contain
dates. the 3rd contains a formula to subtract one from the other to
show
the
number of days in between.

the problem is that some of the original 2-columns do not contain
values.
i
have figured out how to make the formula in the 3rd column show
"N/A"
if
one
of the columns has no value. the issue now is in any of the NEXT
4-column
sections, IF there is a date value in that row, i want it to go back
to
the
last cell in that row that had a date in it and subtract THAT date.

for example: D5= 3/31/05, E5= 4/7/05, formula in F5 calculates "7".

If D10= "N/A" and E10= 4/14/05, F10 calcuates "N/A" (which is what i
want).

But then H10="N/A" and I10= 8/29/05. Instead of J10 caluclating
"N/A"
(which is what my current formula,
=IF(AND(COUNT(H10),COUNT(I10)),I10-H10,"N/A"), produces, I want a
formula
that will see that since H10 is "N/A," it needs to go back and check
first
in
E10 and then in D10 and subtract the date from whichever of those it
picks
up
a date in first. So I want it to end up doing I10-E10, but I want
to
make
it
conditional so I can make it consistent across the entire
spreadsheet
to
account for times when there are "N/A's" in the date fields.

This is in Excel 2003

i know, i know, this is a nightmare!

they are groups of 4 columns each (D:G, H:K, L:O, P:S, T:W, X:AA, and AB:AE)
in each group the first column is "quarter 1-2, fiscal year 05," the second
is quarter 3-4, fiscal year 05". the cells contain dates of when the
inspection was done, or N/A if no inspection was done. the third column is
the number of days between the inspections, so E-D, which results in N/A if
one of the cells has an N/A in it. the fourth column is a "Yes/no"
conditional formula that answers the question "was the 6 month standard met?"
(the inspection is required to be done every 180 days). if the value in F is
greater than 180, the answer in column G will be no.

so the next group of four columns is the same, just continuing in time, for
the next half of the year. so the issue is, if in the first set of 4 columns
there was a missed inspection (N/A), i still need the third column in the
second group of four to calculate the number of days between the 2 most
recent inspections, to show that the number of days between the inspections
was greater than 180.

i hope this helps. i appreciate you thinking so hard about this because i
think i have gotten as far as i can get on my own. at this point i am
considering just doing the ones with 'n/a' manually because it is all
historical data and will not change. i'm having another whole issue with the
conditional formatting of the 'yes/no' column, but that's another story
entirely!

"T. Valko" wrote:

I'm not following you on this.

How many groups of cells do you have? It seems that with each new group to
the right you'd have to expand it to look back at every previous group to
the left.

If these are your groups:

D10:F10
H10:J10
L10:N10

Then what's in the cells between groups?

What's in G10, K10?

--
Biff
Microsoft Excel MVP


"sarajane82" wrote in message
...
Thanks, I am getting closer! This worked, but now I am realizing that it
is
a bit more complicated than that. So let's suppose I have:

D10=N/A
E10=4/14/2005
so F10= N/A (formula of E10-D10)

then...
H10=8/29/2005
I10= N/A
so I need J10 to calculate H10-E10.

I need the formula in J10 (and all other columns like this one in the
sheet)
to use the most recent date and subtract the next most recent date from
it,
even if it's not in the column right next to it. Like, if I10 DID have a
value in it, I would want J10 to have I10-H10, because that's the most
recent
date minus the next most recent date. But if, for example, H10 had N/A
and
I10 had 8/29/2005, I would want J10 to calucate I10 minus E10, because
that
would be the most recent minus the next most recent.

If both H10 and I10 were N/A, I would want the formula in J10 to compute
"N/A" because the most recent time difference would already be accounted
for
in F10. In this same scenario, if E10 was 4/14/2005, H10 and I10 were
N/A,
and L10 was 8/20/2006, I would want the formula in N10 to calucate L10
minus
E10, or the closest date to the most recent date.

i dont know if that makes any sense. I really appreciate your help so
far.
i am so close to having it how i want!

"T. Valko" wrote:

these dates will always be sequential
so the latter is always more recent

Maybe this:

=IF(COUNT(L10),L10-MAX(H10,I10),"N/A")

--
Biff
Microsoft Excel MVP


"sarajane82" wrote in message
...
I will try! For example, if there is a date in H10, but not in I10, it
will
come up with N/A. But then later on in the spreadsheet, there may be a
date
in L10 (these dates will always be sequential, so the latter is always
more
recent). So I want a formula that will take L10 and subtract from it
the
most recent date before it, whether it be in H10 or I10...it's like I
want
the formula to use L10 and if there is a date in I10, subtract that.
But
if
there isn't a date in I10, I want it to check H10 and if there is a
date
there, I want it to subtract that.

I want it to check certain previous cells until it finds one with a
date
in
it and subtract that from L10.

"T. Valko" wrote:

=IF(AND(COUNT(H10),COUNT(I10)),I10-H10,"N/A")

You can reduce that to:

=IF(COUNT(H10,I10)=2,I10-H10,"N/A")

Ok, I'm confused as to where to get the *last* date to include in the
calculation. Can you reword your description?

--
Biff
Microsoft Excel MVP


"sarajane82" wrote in message
...
i am trying to develop a conditional formula. the spreadsheet is
separated
into sections, and in each 4-column section, there are 2 columns
that
contain
dates. the 3rd contains a formula to subtract one from the other to
show
the
number of days in between.

the problem is that some of the original 2-columns do not contain
values.
i
have figured out how to make the formula in the 3rd column show
"N/A"
if
one
of the columns has no value. the issue now is in any of the NEXT
4-column
sections, IF there is a date value in that row, i want it to go back
to
the
last cell in that row that had a date in it and subtract THAT date.

for example: D5= 3/31/05, E5= 4/7/05, formula in F5 calculates "7".

If D10= "N/A" and E10= 4/14/05, F10 calcuates "N/A" (which is what i
want).

But then H10="N/A" and I10= 8/29/05. Instead of J10 caluclating
"N/A"
(which is what my current formula,
=IF(AND(COUNT(H10),COUNT(I10)),I10-H10,"N/A"), produces, I want a
formula
that will see that since H10 is "N/A," it needs to go back and check
first
in
E10 and then in D10 and subtract the date from whichever of those it
picks
up
a date in first. So I want it to end up doing I10-E10, but I want
to
make
it
conditional so I can make it consistent across the entire
spreadsheet
to
account for times when there are "N/A's" in the date fields.

This is in Excel 2003

Sorry, I'm just not getting this!

--
Biff
Microsoft Excel MVP


"sarajane82" wrote in message
...
i know, i know, this is a nightmare!

they are groups of 4 columns each (D:G, H:K, L:O, P:S, T:W, X:AA, and
AB:AE)
in each group the first column is "quarter 1-2, fiscal year 05," the
second
is quarter 3-4, fiscal year 05". the cells contain dates of when the
inspection was done, or N/A if no inspection was done. the third column
is
the number of days between the inspections, so E-D, which results in N/A
if
one of the cells has an N/A in it. the fourth column is a "Yes/no"
conditional formula that answers the question "was the 6 month standard
met?"
(the inspection is required to be done every 180 days). if the value in F
is
greater than 180, the answer in column G will be no.

so the next group of four columns is the same, just continuing in time,
for
the next half of the year. so the issue is, if in the first set of 4
columns
there was a missed inspection (N/A), i still need the third column in the
second group of four to calculate the number of days between the 2 most
recent inspections, to show that the number of days between the
inspections
was greater than 180.

i hope this helps. i appreciate you thinking so hard about this because i
think i have gotten as far as i can get on my own. at this point i am
considering just doing the ones with 'n/a' manually because it is all
historical data and will not change. i'm having another whole issue with
the
conditional formatting of the 'yes/no' column, but that's another story
entirely!

"T. Valko" wrote:

I'm not following you on this.

How many groups of cells do you have? It seems that with each new group
to
the right you'd have to expand it to look back at every previous group to
the left.

If these are your groups:

D10:F10
H10:J10
L10:N10

Then what's in the cells between groups?

What's in G10, K10?

--
Biff
Microsoft Excel MVP


"sarajane82" wrote in message
...
Thanks, I am getting closer! This worked, but now I am realizing that
it
is
a bit more complicated than that. So let's suppose I have:

D10=N/A
E10=4/14/2005
so F10= N/A (formula of E10-D10)

then...
H10=8/29/2005
I10= N/A
so I need J10 to calculate H10-E10.

I need the formula in J10 (and all other columns like this one in the
sheet)
to use the most recent date and subtract the next most recent date from
it,
even if it's not in the column right next to it. Like, if I10 DID have
a
value in it, I would want J10 to have I10-H10, because that's the most
recent
date minus the next most recent date. But if, for example, H10 had N/A
and
I10 had 8/29/2005, I would want J10 to calucate I10 minus E10, because
that
would be the most recent minus the next most recent.

If both H10 and I10 were N/A, I would want the formula in J10 to
compute
"N/A" because the most recent time difference would already be
accounted
for
in F10. In this same scenario, if E10 was 4/14/2005, H10 and I10 were
N/A,
and L10 was 8/20/2006, I would want the formula in N10 to calucate L10
minus
E10, or the closest date to the most recent date.

i dont know if that makes any sense. I really appreciate your help so
far.
i am so close to having it how i want!

"T. Valko" wrote:

these dates will always be sequential
so the latter is always more recent

Maybe this:

=IF(COUNT(L10),L10-MAX(H10,I10),"N/A")

--
Biff
Microsoft Excel MVP


"sarajane82" wrote in message
...
I will try! For example, if there is a date in H10, but not in I10,
it
will
come up with N/A. But then later on in the spreadsheet, there may
be a
date
in L10 (these dates will always be sequential, so the latter is
always
more
recent). So I want a formula that will take L10 and subtract from
it
the
most recent date before it, whether it be in H10 or I10...it's like
I
want
the formula to use L10 and if there is a date in I10, subtract that.
But
if
there isn't a date in I10, I want it to check H10 and if there is a
date
there, I want it to subtract that.

I want it to check certain previous cells until it finds one with a
date
in
it and subtract that from L10.

"T. Valko" wrote:

=IF(AND(COUNT(H10),COUNT(I10)),I10-H10,"N/A")

You can reduce that to:

=IF(COUNT(H10,I10)=2,I10-H10,"N/A")

Ok, I'm confused as to where to get the *last* date to include in
the
calculation. Can you reword your description?

--
Biff
Microsoft Excel MVP


"sarajane82" wrote in
message
...
i am trying to develop a conditional formula. the spreadsheet is
separated
into sections, and in each 4-column section, there are 2 columns
that
contain
dates. the 3rd contains a formula to subtract one from the other
to
show
the
number of days in between.

the problem is that some of the original 2-columns do not contain
values.
i
have figured out how to make the formula in the 3rd column show
"N/A"
if
one
of the columns has no value. the issue now is in any of the NEXT
4-column
sections, IF there is a date value in that row, i want it to go
back
to
the
last cell in that row that had a date in it and subtract THAT
date.

for example: D5= 3/31/05, E5= 4/7/05, formula in F5 calculates
"7".

If D10= "N/A" and E10= 4/14/05, F10 calcuates "N/A" (which is
what i
want).

But then H10="N/A" and I10= 8/29/05. Instead of J10 caluclating
"N/A"
(which is what my current formula,
=IF(AND(COUNT(H10),COUNT(I10)),I10-H10,"N/A"), produces, I want a
formula
that will see that since H10 is "N/A," it needs to go back and
check
first
in
E10 and then in D10 and subtract the date from whichever of those
it
picks
up
a date in first. So I want it to end up doing I10-E10, but I
want
to
make
it
conditional so I can make it consistent across the entire
spreadsheet
to
account for times when there are "N/A's" in the date fields.

This is in Excel 2003

There is a flaw in your setup. You need to check the difference after each date
entry, not after every pair of date entries. Assume the following inspection dates:

3/1/05, 8/2/05, 3/7/06, 9/1/06, 2/3/07, 10/1/07

How may "Yes" and how many "No"?

Comparing within each year, there is only one "No" (in 07). But comparing
between years, there is another one between 05 and 06.


sarajane82 wrote:
i know, i know, this is a nightmare!

they are groups of 4 columns each (D:G, H:K, L:O, P:S, T:W, X:AA, and AB:AE)
in each group the first column is "quarter 1-2, fiscal year 05," the second
is quarter 3-4, fiscal year 05". the cells contain dates of when the
inspection was done, or N/A if no inspection was done. the third column is
the number of days between the inspections, so E-D, which results in N/A if
one of the cells has an N/A in it. the fourth column is a "Yes/no"
conditional formula that answers the question "was the 6 month standard met?"
(the inspection is required to be done every 180 days). if the value in F is
greater than 180, the answer in column G will be no.

so the next group of four columns is the same, just continuing in time, for
the next half of the year. so the issue is, if in the first set of 4 columns
there was a missed inspection (N/A), i still need the third column in the
second group of four to calculate the number of days between the 2 most
recent inspections, to show that the number of days between the inspections
was greater than 180.

i hope this helps. i appreciate you thinking so hard about this because i
think i have gotten as far as i can get on my own. at this point i am
considering just doing the ones with 'n/a' manually because it is all
historical data and will not change. i'm having another whole issue with the
conditional formatting of the 'yes/no' column, but that's another story
entirely!

"T. Valko" wrote:

I'm not following you on this.

How many groups of cells do you have? It seems that with each new group to
the right you'd have to expand it to look back at every previous group to
the left.

If these are your groups:

D10:F10
H10:J10
L10:N10

Then what's in the cells between groups?

What's in G10, K10?

--
Biff
Microsoft Excel MVP


"sarajane82" wrote in message
...
Thanks, I am getting closer! This worked, but now I am realizing that it
is
a bit more complicated than that. So let's suppose I have:

D10=N/A
E10=4/14/2005
so F10= N/A (formula of E10-D10)

then...
H10=8/29/2005
I10= N/A
so I need J10 to calculate H10-E10.

I need the formula in J10 (and all other columns like this one in the
sheet)
to use the most recent date and subtract the next most recent date from
it,
even if it's not in the column right next to it. Like, if I10 DID have a
value in it, I would want J10 to have I10-H10, because that's the most
recent
date minus the next most recent date. But if, for example, H10 had N/A
and
I10 had 8/29/2005, I would want J10 to calucate I10 minus E10, because
that
would be the most recent minus the next most recent.

If both H10 and I10 were N/A, I would want the formula in J10 to compute
"N/A" because the most recent time difference would already be accounted
for
in F10. In this same scenario, if E10 was 4/14/2005, H10 and I10 were
N/A,
and L10 was 8/20/2006, I would want the formula in N10 to calucate L10
minus
E10, or the closest date to the most recent date.

i dont know if that makes any sense. I really appreciate your help so
far.
i am so close to having it how i want!

"T. Valko" wrote:

these dates will always be sequential
so the latter is always more recent
Maybe this:

=IF(COUNT(L10),L10-MAX(H10,I10),"N/A")

--
Biff
Microsoft Excel MVP


"sarajane82" wrote in message
...
I will try! For example, if there is a date in H10, but not in I10, it
will
come up with N/A. But then later on in the spreadsheet, there may be a
date
in L10 (these dates will always be sequential, so the latter is always
more
recent). So I want a formula that will take L10 and subtract from it
the
most recent date before it, whether it be in H10 or I10...it's like I
want
the formula to use L10 and if there is a date in I10, subtract that.
But
if
there isn't a date in I10, I want it to check H10 and if there is a
date
there, I want it to subtract that.

I want it to check certain previous cells until it finds one with a
date
in
it and subtract that from L10.

"T. Valko" wrote:

=IF(AND(COUNT(H10),COUNT(I10)),I10-H10,"N/A")
You can reduce that to:

=IF(COUNT(H10,I10)=2,I10-H10,"N/A")

Ok, I'm confused as to where to get the *last* date to include in the
calculation. Can you reword your description?

--
Biff
Microsoft Excel MVP


"sarajane82" wrote in message
...
i am trying to develop a conditional formula. the spreadsheet is
separated
into sections, and in each 4-column section, there are 2 columns
that
contain
dates. the 3rd contains a formula to subtract one from the other to
show
the
number of days in between.

the problem is that some of the original 2-columns do not contain
values.
i
have figured out how to make the formula in the 3rd column show
"N/A"
if
one
of the columns has no value. the issue now is in any of the NEXT
4-column
sections, IF there is a date value in that row, i want it to go back
to
the
last cell in that row that had a date in it and subtract THAT date.

for example: D5= 3/31/05, E5= 4/7/05, formula in F5 calculates "7".

If D10= "N/A" and E10= 4/14/05, F10 calcuates "N/A" (which is what i
want).

But then H10="N/A" and I10= 8/29/05. Instead of J10 caluclating
"N/A"
(which is what my current formula,
=IF(AND(COUNT(H10),COUNT(I10)),I10-H10,"N/A"), produces, I want a
formula
that will see that since H10 is "N/A," it needs to go back and check
first
in
E10 and then in D10 and subtract the date from whichever of those it
picks
up
a date in first. So I want it to end up doing I10-E10, but I want
to
make
it
conditional so I can make it consistent across the entire
spreadsheet
to
account for times when there are "N/A's" in the date fields.

This is in Excel 2003