conditional formatting
 

I want to be able to search for two things in a column. The first is a value
of less than or greater than $100. (OK so that is really two things I guess).
The second is to be able to see if that amount is within the previous 365
days. There is another column for the date. I can get part to work but am
having trouble with the date. Any ideas would be greatly appreciated.

conditional formatting
 

Try {IF(A1<9,A1,"")}, in a separate column of use the conditional formatting
to change the color or highlight the field if it doesn't equal $100.

"Russell" wrote:

I want to be able to search for two things in a column. The first is a value
of less than or greater than $100. (OK so that is really two things I guess).
The second is to be able to see if that amount is within the previous 365
days. There is another column for the date. I can get part to work but am
having trouble with the date. Any ideas would be greatly appreciated.

conditional formatting
 

Not sure what you mean by "within the previous 365 days".

if you're referring to 365 days prior to the current date

=(B2<100)*(A2TODAY()-365)

or if you mean 365 days prior to the value's adjacent date (with sorted
dates)

=(B2<100)*SUMPRODUCT(($A$2:$A1$A2-365)*($B$2:$B1=B2))

or (if dates are not sorted)

=(B2<100)*SUMPRODUCT(($A$2:$A$1000$A2-365)*($B$2:$B$1000=B2))


HTH
Steve D.

"Russell" wrote in message
...
I want to be able to search for two things in a column. The first is a
value
of less than or greater than $100. (OK so that is really two things I
guess).
The second is to be able to see if that amount is within the previous 365
days. There is another column for the date. I can get part to work but am
having trouble with the date. Any ideas would be greatly appreciated.

conditional formatting
 

Thanks Steve. I am going to give it a shot. The more I read the more I think
that I am asking too much of Excel. At least the 2003 version that I am using.

Have a good day.

Russell

"Steve Dunn" wrote:

Not sure what you mean by "within the previous 365 days".

if you're referring to 365 days prior to the current date

=(B2<100)*(A2TODAY()-365)

or if you mean 365 days prior to the value's adjacent date (with sorted
dates)

=(B2<100)*SUMPRODUCT(($A$2:$A1$A2-365)*($B$2:$B1=B2))

or (if dates are not sorted)

=(B2<100)*SUMPRODUCT(($A$2:$A$1000$A2-365)*($B$2:$B$1000=B2))


HTH
Steve D.

"Russell" wrote in message
...
I want to be able to search for two things in a column. The first is a
value
of less than or greater than $100. (OK so that is really two things I
guess).
The second is to be able to see if that amount is within the previous 365
days. There is another column for the date. I can get part to work but am
having trouble with the date. Any ideas would be greatly appreciated.