conditional formula with lots of conditions

[sarajane82]

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

 

[T. Valko]

=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?

 

[sarajane82]

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]

these dates will always be sequential

so the latter is always more recent

Maybe this:

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

 

[sarajane82]

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]

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?

 

[sarajane82]

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]

Sorry, I'm just not getting this!

 

[Glenn]

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.