Matching dates and % values

[rhudgins]

I need help writing a formula that can scan down the dates in column A and the %'s in column B and return the word "Yes" if a % value > then 3% occurs in the first day of data.

ie- if a value of 3% or greater occurs in column B on 3/9/11 then return the word Yes.

[alansidman]

In column C

=IF(B2>0.03,"YES","")

Copy down

Alan

[rhudgins]

That formula does not take into consideration the dates in Column A...

[ChemistB]

Try this

=IF(AND(INT(A2)=INT(MIN($A:$A)),B2>3%),"Yes","")
Does that work for you?

[rhudgins]

That formula does not work. That formula appears to be checking if B3>3% which is not what I am looking for. The formula needs to be able to scan all of the days on 3/9/11(because 3/9/11 is the first set of days in the data set) and return "Yes" if there is a value that is greater then 3% that occurs on 3/9/11

[alansidman]

Since you only showed us one day, what do you want to happen to the rest of the days? I am very confused now. Perhaps you could show an example with more dates and data of what you want the end result to look like. It doesn't have to be a big file. Several lines with different dates and data and the result you want for each of those lines.

Alan

[rhudgins]

I made the following adjustment which now looks down all of column D. I am having one problem though. The date in A2 includes HH:MM:SS. How can i change the formula to only look at the mm/dd/yyyy part of the cell?


=IF(AND(INT(A2)=INT(MIN($A:$A)),$B:$B>3%),"Yes","")

[ChemistB]

The mm/dd/yy part is the integer part so INT(cell) should work for you.

Try this formula
=IF(SUMPRODUCT(--(INT($A$2:$A$1483)=INT(MIN($A$2:$A$1483))),--($B$2:$B$1483>3%))>0,"Yes","")
Does that work for you?

[rhudgins]

Yes sir. That works perfectly. Thanks!

[rhudgins]

I have two adjustments I am trying to make to the formula that you helped me out with.

Is there a way to change the INT to check for the first two available dates in column A?

Assuming this formula is in cell D1. If I drag the formula down to cell D2, D3, D4 ect. is there a way to change the formula to shift the ranges 25 columns to the right everytime the formula is moved down a cell?

[ChemistB]

First 2 available dates
Easiest if dates are sequential,
=IF(SUMPRODUCT(--(INT($A$2:$A$1483)<=INT(MIN($A$2:$A$1483)+1)),--($B$2:$B$1483>3%))>0,"Yes","")
I'll have to think on the second question.

[rhudgins]

Thank you for your help.

[rhudgins]

Any ideas?

[NBVC]

Using Chemists' formula and assuming you are using the same column A, but only jumping 25 columns at a time for the delta column... try:

Please Login or Register  to view this content.

adjusting the $ZZ$1483 to whatever the last column in your database happens to be.

If you want to jump 25 Date and Delta columns together, then

Please Login or Register  to view this content.

[rhudgins]

Thanks that helps alot. I tried to change the value to < -2% but the formula will not work. Any ideas what is wrong?

Please Login or Register  to view this content.

[NBVC]

Why did you switch the >0 operand to <0 at the end of the logical_test of the IF() function?

[rhudgins]

Thats why it is not working. Thanks for the catch

[NBVC]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[rhudgins]

Is there a way to change this formula to scan the first 2 Workdays instead of just 2 days?

Please Login or Register  to view this content.

[NBVC]

I think this is going to get too complex... is there any way you can remove the rows with weekend dates? then you can possibly use:

Please Login or Register  to view this content.

[rhudgins]

There are no weekend dates in my data set but the formula does not work. Any ideas why? This is my modified formula with different ranges.

Please Login or Register  to view this content.

[NBVC]

In what way doesn't it work?

Maybe a sample workbook showing it not working might help?

[rhudgins]

Here is the sample. The formula is in AF20. The data in column AJ contains a value less then <-2% and the formula returns "Yes" correctly. The data in column AV does not have a value <-2% in the first two days but the formula still says 'Yes". which is incorrect.

[NBVC]

I have to look at this over the weekend.... signing off for now....

So your data already doesn't include weekends, so you want to be able to skip to next available day after the first date it encounters, even though it might not be the next actual date on the calendar, correct?

[rhudgins]

Correct, I would like it to skip to the next working date on the calander.

[NBVC]

In column AI you have dates below all the April dates that fall in March. Is that right? Hopefully, that is wrong and they should be chronologically listed dates instead.

If so then try:

Please Login or Register  to view this content.

CSE confirmed and copied down.

If not..we have another problem....

[rhudgins]

Thanks thsi formula works. You were right, the data should have been listed in chronological order. How can I change this formula to change to the first day only instead of the first two days?

[NBVC]

So how many times are you going to change the condition in this thread?

Isn't that what ChemistB started you off with?

[rhudgins]

Yes you are correct. Thank you for your help.