Duwin

New Member
Joined
Sep 19, 2019
Messages
15
I have a bank of data that is updated monthly. I need to analyze said data to determine first all values above 35 then I need to look for two consecutive instances of this event. Once found, I would like to indicate for inspection if not ok
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
You may need to provide more info, but have a look at Autofilter for your "above 35"....that might also give you your consecutive instances
 
Last edited:
Upvote 0
Welcome to the MrExcel board!

..can i do it in if functions?
Probably, but we don't know what your data is like, where it is or exactly what you need. Could you give us a small set of dummy data and explain in realtion to that?
My signature block below has help on how to do that.
 
Upvote 0
Thanks peter..

For example i have values from cell
A1 15
A2 35
A3 50
A4 20
A5 10
A6 55
A7 70
A8 65
A9 50
A10 35

In A11 the logic must be when there is a consecutive cell with values equal or greater than 35..it must say "for inspection" ..if none it must say "okay"
 
Upvote 0
In A11 the logic must be when there is a consecutive cell with values equal or greater than 35..it must say "for inspection" ..if none it must say "okay"
Okay, try this. I've given an example of each result.
This is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}.

00 HTML Conversions.xlsm
AB
11515
23525
35039
42030
51025
65588
7704
86516
95032
103537
11for inspectionokay
Duwin
Cell Formulas
RangeFormula
A11A11=IF(MAX(FREQUENCY(IF(A1:A10>35,ROW(A1:A10)),IF(A1:A10<=35,ROW(A1:A10))))>1,"for inspection","okay")
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Last edited:
Upvote 0
Follow up question

Given same data

In a11 the logic must be when there is a cell with greater or equal to 35 and the consecutive cells are not yet zero, it must say "inspect" if not "ok"
 
Upvote 0
Follow up question

Given same data

In a11 the logic must be when there is a cell with greater or equal to 35 and the consecutive cells are not yet zero, it must say "inspect" if not "ok"
If we have the same data how is the requirement different?

What do you mean by "consecutive cells are not yet zero"?

Perhaps we do need a couple more sets of sample data and the results?
 
Upvote 0

Forum statistics

Threads
1,212,934
Messages
6,110,762
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top