Looking to check back three or possibly four cells in a column till it finds the larger

This is the formula I'm currently using.

=IF(B31="?","",IF(OR(B31>B32,AND(B31=B32,B31>!B33)),1,""))

What I'm needing to do is if B31 is equal to B33, I need it to check back one more cell in B34 then do it's calculating.

I've tried to rewrite it with both the AND/OR functions, but can't get it to work. What am I doing wrong with it?


Thanks a ton guys and sorry to bug ya'll again.

Thanks FDibbins. Yeah, I see that now. This is a toned down version of the formula. I just didn't get the ! took out when putting it on here. It was a copy and paste error on my part.
That part works. I need it to check if B31 is greater than B32, if B31 is equal to B32, then I need it to check B31 against B33 to see if it is greater than it. That works right, but if something happens that B31 is also equal to B33, then I need it to check B34 to see if it is greater than it. I don't think there would be any reason to make it any longer to check B35 also, because I have not needed it to so far and I've been doing this a while now. (As in what I'm looking at, not the formula writing). Still light years behind you guys.

Originally Posted by FDibbins

Not sure if this your problem, but try removing the ! in front of B33

=IF(B31="?","",IF(OR(B31>B32,AND(B31=B32,B31>!B33)),1,""))

Originally Posted by FDibbins

Hard to say without seeing some dummy data, but have you considered looking at using max() with match() to find the row number?

or something like a series of nested if()'s?

=if(B31>=B34,"what_you_want_here1",if(B31>=B33,"what_you_want_here2",if(B31>=B32,"what_you_want_here3","what_you_want_here4")))

That would be great if I had any idea of how to do that. I don't . LOL. I'm still learning even the smaller stuff like the AND and OR.
I'm trying some nested IF's but can't get it right. I can't use the >= together to make it do the way I need it to.

What I'm needing is if B31 is greater than B32, then my if true statement, but if B31 is equal to B32 then I need to check B31 against B33. If B31 is equal to B33, then it would need to check B31 to see if it is greater than B34.

That's what I'm looking to do anyway.

I may have to do some column hiding to get it the way I want it, and that's ok to. As long as it works, is all I need anyway.

Thanks for the help FDibbins!!! Again.

Here is a sample of what I'm up against.

I've set it up to show how I had it working with the formula also to give an idea.

I think I got it to where you can all see what I'm needing.

I will have the same formula with the > switched to < for if it's less than. If I can get one working, I can change all that to how I need it. All my nested If's are just doing nothing.

Thank you again!!!

[QUOTE=dredwolf;3191272]Okay, B7= B8 , C7 shows B7?...I am sorry if I am sounding dense here...it is your upload is not showing 1's anywhere...what would you expect where ?[/QUOTE

Your not. It's the way I put it on there I guess. The formula is in F15 that I'm using. I've put it on there to show how it works. It starts with that 7 in B7 then tests that 7 against the 7 in B6. Because they are the same, it then checks the 7 in B7 against the 5 in B9. Since that 7 is greater than the 5, it gave me the 1 result I'm looking for.

What I'm needing is the same thing but if it comes up like in D where there are the same digits back to back to back, for it to check back and compare that 7 in D7 till it gets to the 5 in D11.

I just need if it is greater than to show a 1 in the cell the formula is in is all. I have another set of formulas that pick up that 1 and does it's thing. It doesn't really matter what cell the formula goes in. If there is an easier place for you to put it, that's ok with me.

I appreciate it, and sorry for the confusion.

Originally Posted by FDibbins

did you try jis 1?

=if(B31>=B34,1,if(B31>=B33,1,if(B31>=B32,1,1)))

Hello again Fdibbins,

Yeah, but because of the = to involved in the formula it returns the value. I'm looking for greater than only and that returns if it's equal to also. I'm needing it to only check elsewhere if it is equal to the previous. If that makes sense.

I may have to do some hidden cells with it, but that's ok. As long as it works is all that matters.

Originally Posted by dredwolf

Obviously, me and you are not communicating, so, I'll follow this thread, but I will not post any more replies, I'm interested in seeing what finally works for you, just not interested enough to spend anymore time on it, sorry

LOL. I don't blame you. I can't explain what I'm thinking worth a dime. I'll just do some hidden cell type of thing and go from there. It'll take a little longer, but that's ok.

I'll set up one to check say B7 against B8 and if it's even, it will give a blank cell. Then another column with if B7 is greater than B9 and if it's the same digit it will stay blank. And so on. Then I'll fix the other part where this was supposed to go, to pick up the data from there. It's no big deal. I've just gotten to greedy with how great this stuff works is all. A little extra work is no problem at all.

I really appreciate you looking at it for me and your time!!!!

Originally Posted by FDibbins

so remove the =. I used this on your sample data and it seems to work...
=IF(B7>B8,1,IF(B7>B9,1,IF(B7>10,1,IF(B7>B11,1,""))))

You can extend that as far as you want

It's still giving a value when it shouldn't.

It's ok. I can go the hidden cells direction. It's no problem at all.

I've put enough time into this "thing" I'm working on, a little more won't hurt me.

Thank you both very much for your time!!!!!!

I can use the old formula I was in a couple of columns and just change it up a little, then hide them. Won't take long a' tall.

Ok, let's see if this one shows what I'm needing.

If it doesn't, then ya'll don't even worry about it.

Ok. In case anyone else runs into this problem here is what I cam up with for this. Since I couldn't figure out how to explain what I was needing to do before, and now that I have it fixed, I can explain it. (Brains forget to work in my head sometimes). But, say that B3 is 7, B4 is 7, B5 is 7 and B6 is 5. In C3 I would have needed to find out if B3 is greater than B4. Since B4 is equal to, I would have to check B3 against B5 to see if it was greater than it. Here again, it was equal to. So I needed to check B3 against B5. Again, the same thing. So I would have to go back to B6 and check B3 against it. Here B3 was not equal to B7 and it was greater than. That's the answer I was looking for. So I had to put it in a nested IF(AND situation that if B3 was = to B4, to check B3 against B5. Then the next go around, I had to include if B3 was equal to B4 and B5 then greater than B6. I had equal to each preceding cell to the equal to checking before doing the next greater than check. So here is the formula. It took me about ten thousand tries, but I'd rather do that than have you all set and try to guess what I need taking up your time. Again, I honestly do appreciate all the help any and all you people have given me. Even the offering to look at my problems. Your all invaluable and have taught me what I know about this stuff. Thank you all!!!!

=IF(B1>B2,1,IF(AND(B1=B2,B1>B3),1,IF(AND(B1=B2,B1=B3,B1>B4),1,IF(AND(B1=B2,B1=B3,B1=B4,B1>B5),1,"")))))

This one was a little longer because it's working from one sheet to another, but it works the way I needed it to.

Thanks again all of you!!!!