index and match/ LOOKUP TO FIND the last number over 50

[ARNIEBUN]

What I want to do is create an index and match array that will look up that last number over 50 then lookup the column next to it to see if there are any numbers below 0, if there is return true or the value and if not return false or 0. the diagram shows in the highlighted yellow the look up for the first part and in blue the look up for the second part, and in green where i want the result displayed. it would be a massive help if you could please help.

[Limor_OP]

Hi
Try this:
=LOOKUP(2,(--(C3:C14>50)),D3:D14)

[ARNIEBUN]

hi thanks for your response, what i am trying to do is see if its the fist instance of below 0 when the number is over 50, and then again for when its under 40 if its the fist number over 0, i can have 2 different formulas for that to track it, when I copy your code down it gives me the next minus number and doesn't indicate that one is already in the over 50 section. another solution would be much welcomed.

[JeteMc]

what i am trying to do is see if its the fist instance of below 0 when the number is over 50

As to the above, perhaps the following in E3 and copied down:

Please Login or Register  to view this content.

Note that I deleted row 15.
If the formula doesn't yield the expected results then please tell us a few instances in which the results are incorrect and explain why.
Let us know if you have any questions.

[Limor_OP]

Hi,
Please try in E3 and down:
IF(D4=LOOKUP(2,(--($C$3:$C$14>50)),$D$3:$D$14),"True","")

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[ARNIEBUN]

HI thanks for your solutions, unfortunate is doesn't work in very basics I need to know when the number is above 50 and the change is less than 0 for the first time, and below 40 and above 0 for the first time. the 2 options can be in different cells. it has to be the first time the change in below 0, while the number has got to 50. I could live with it checking the last 12 cells lets say.

[AliGW]

Your updated title was still not descriptive of your problem - it was generic. I have updated it again to show you the sort of thing we mean when we ask for descriptive titles.

I have looked at your workbook and your posts and have no idea what you are attempting. You need to take us through some concrete examples.

[ARNIEBUN]

hi so i have uploaded a better example please let me know if this helps with understanding what i am trying to do, i have included a description in the sheet. which is :

I NEED TO CHECK ROW C AND D, SO I AM LOOKING TO SCORE THE 2 YELLOW HIGHLIGHTED PARTS OF THIS SCORE CARD.
AS A EXAMPLE LETS LOOK AT A1 YELLOW.
SO MY THINKING IS THAT IF I HAVE A FORMLARE TO CHECK COLUM C BACK UP TO WHEN THE NUMBER GOES OVER 50, THEN FROM THAT PIONT CHECKS ROW D DOWN TO SEE HOW MANY VALUES UNDER 0 THERE ARE. IF I CAN GET A TURE OR FALSE i CAN USE ANOTHER FORMLAUR TO SCROE IT FROM THE SCORE CARD SO i DON'T NEED THAT PART, i JUST NEED THE PARTS IN YELLEW. i COULD HAVE ONE FORMULAR THAT CHECK A1 IN E AND ANOTHER THAT CHECKS A2 IN F WOULD BE A GREAT SOLUTION. i HVE BEEN WORKING ON THIS FOR SOME TIME TO TRY AND CRACK IT AND COME CLOSE BUT TO BUILD ON THAT WOULD MEAN MAPPING EVERY POSSIBLE OUTCOME FROM THE LAST 12 MONTHS TO SCORE IT. i HOPE THIS EXPLAINS IT BETTER.

EXAMPLE in sheet.

[JeteMc]

I thought that I might understand until I looked at cell R39. I do not understand why it displays FALSE even though the value in P39 is less than 50 and the value in Q39 is greater than 0.
I have the same basic question about the FALSE on cell O54.

[ARNIEBUN]

because i am looking at the numbers in a time line perspective, so when the numbers in column P go under 50 in cell P30, i want to look down from that point and mark the first instance of where the numbers in column Q go over 0, i have marked how it would look on the values over 0 in column Q this should help. please let me know if you have any more questions.

[JeteMc]

These return the expected results for the sample.
For A1 in E3 and down:

Please Login or Register  to view this content.

For A2 in F3 and down:

Please Login or Register  to view this content.

Let us know if you have any questions.

[ARNIEBUN]

hi thanks for your solution its the best one i have found so far, however there are 2 instances where it doesn't pick up on both the columns I have attached a sheet that will show you where. thank you so much for this

[JeteMc]

It seems that the logic for case A2 is 'each time the values in column C falls below 50, find the first value in column D that is greater than zero'.
Does that sound right?
If so then I believe the steps below will do what you want:
1: Paste the following into cell A2 and copy down:

Please Login or Register  to view this content.

2: Place a zero in cell C2
3: Paste the following into cell E3 and copy down:

Please Login or Register  to view this content.

4: Paste the following into cell F3 and copy down:

Please Login or Register  to view this content.

Let us know if you have any questions.

[ARNIEBUN]

Hi, thank you so much, it works on sheet 1 ok, there are a lot of #refs but where it says true i had to offset it to line up with the data, i did however get some data and past it into sheet 2 but couldn't get it to work, i was wondering, what have i done wrong and how i get rid of the #refs and replace with something else?

thank you so much for your help.

[JohnTopley]

Change A2 as per post #14

=IF(AND(C4>50,C3<50),SUM(A1,1),A1)

[ARNIEBUN]

It seems that the logic for case A2 is 'each time the values in column C falls below 50, find the first value in column D that is greater than zero'.
Does that sound right?

yes so when it fall below 50 first positive value in column d and when it goes over 50 the first negative value in d, is exactly what i need.

when I have done it I will post the previous solution that didn't work as well ad this one complete with scoring that score card. I know there are a lot of people trying to do this so should bring some traffic.

[JeteMc]

Here are the formulas from post #14 applied to the file.
It works for Sheet1 with a small modification to the formula in column A: =IF(AND(C2>50,C1<50),SUM(A1,1),A1)
As to Sheet2 the formulas seem to work down to row 60. The reason for the failure at that point is that the value in C59 is 50. We have only discussed values above and below 50. Should a value of 50 be considered as part of case A1 or case A2?
Let us know if you have any questions.

[ARNIEBUN]

Hi was wondering if you could factor in when its 50, its over 50 that would be a great help.

[JeteMc]

Update formulas for:
Column E (C>=50,D<0): =IF(C3<50,"",IF(AND(COUNTIFS(C$3:C3,">=50",D$3:D3,"<0",A$3:A3,A3)=1,D3<0),TRUE,IF(AND(COUNTIFS(C$3:C3,">=50",D$3:D3,"<0",A$3:A3,A3)>1,D3<0),FALSE,"")))
Column F (C<50,D>0): =IF(C3>=50,"",IF(AND(COUNTIFS(C$3:C3,"<50",D$3:D3,">0",A$3:A3,A3)=1,D3>0),TRUE,IF(AND(COUNTIFS(C$3:C3,"<50",D$3:D3,">0",A$3:A3,A3)>1,D3>0),FALSE,"")))
Let us know if you have any questions.

[ARNIEBUN]

Hi thank you so much for the solution I will build out the rest of it and post the finished article which uses the score card for the PMI date made for the Anton Kril PTFM course and will automate the scoring method. should help a lot op people.

[ARNIEBUN]

I have compleated the score card for the Uunited States PMI based on the Anton Kril PTFM course which will automate the scoring. when the number meets the condtions set out in the score card, its scores the minimum value when 0 to plus, and the maxium value when 0 to minus. then you add the change to that value you flipping that number when its plus but is scoring to the minus side, and the sc0re will only score to a maximum values, eg if the score is 0 to 5 but its over 50 and slowing you would get lets say -1.2 flip it to 1.2 and add it to 0, but it the value was -6 we can only flip it to 6 then score it 5. and vise verse. the complete formula across the cells will calculate all of that for you. so if you have found this i have saved you loads of work, enjoy.

[JeteMc]

Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.