muti vaient formula

Here's an attempt. I am not a fan of large single cell mega formulas, so I have spread the logic out over several columns.

Since the difference between each entry and that days standard is important, there are two columns (H:I) to compute the difference between each entry (max and min) and the standard.

The logical tests seem to be about checking, in two separate columns (J:K):
is max and/or min within one of standard
has max or min "crossed" standard (checked by seeing when difference changes sign).


The final result for each day is the first row where either of the above conditions is true. Column L checks when either condition is true, and then the match() functions in column M return the row # of the first TRUE result for each day.

Since I am not a fan of single cell mega formulas, I will leave it to someone else to nest all of that into a single formula. This shows how I would structure a spreadsheet like this, and should also allow you to confirm that the logic I have read into the question is correct (if someone does choose to undertake the single cell megaformula).

Most people would assume that "not found" or N/A is the only error that would be returned, and will use the IFERROR() function: https://support.office.com/en-us/art...6-63f3e417f611

The "changes sign" formula in column L is looking at the current and the previous row. In the case of row 650 (and probably others that return 1), the formula in column L is comparing the sign of the first entry for the current day with the last entry for the previous day. These comparisons are probably invalid comparisons. "Simply" add another "criteria" column to check if this is the first entry for a given day and take appropriate action.

Or, is it possible that the first entry of a given day will never be the correct result? We could have the MATCH() functions start their search with the 2nd entry for each day, if we (you) can state with absolute certainty that the first entry of the day will never be the result we should receive. For example, in row 434, you indicate that this day should have returned the 1st entry, but I cannot see the logic that says this should be the first entry instead of the 2nd entry.

So, in this case, Excel is not making a mistake. Excel is doing exactly what we (I) have told it to do. In order to get Excel to give a different result, we need to think carefully through the logic we need Excel to use and take appropriate action.

Not only is there "a" way to look at data in blocks of 24 lines, there likely many different strategies.

One that I frequently use is to enter the data in blocks of 24. Instead of a long list of entries like you have here, I will often arrange the data in a table that might look something like:

Please Login or Register  to view this content.

My analysis formulas will then go into the rows below each data set, until I get my final result towards the bottom. Because I don't like to scroll to the bottom to see the final result, I will usually have that link to somewhere near the top (maybe row 26, since the data is in rows 2:25). If I pay careful attention to these formulas, I can then simply copy these formulas across to perform the analysis for each day.

Where you have already entered your data into a long list, you may not want to rearrange it, or there is some other consideration that makes a long list preferable. In those cases, you may want additional IF() functions to test for "start of day" and/or "end of day". The Time column (column B) might be the easiest column to look at. You might use something like
=IF(B2=0,what do do for the 1st entry of a day,what to do for other entries of a day).

how can i ask excel to check for these? can it bring back list of cell? rows? or just highlighted cells?

I'm sure there are many possible strategies.

We have not talked at all about where these data come from or how they get into Excel. In some cases, I find that identifying and dealing with these kind of errors is easier if handled before importing the data to Excel. At this point, only you can know if that is the case here.

If I must handle these errors in Excel, I might start with a strategy like this:

1) Boolean tests and operations to identify the error conditions. You the programmer/developer will need to know what kind of "errors" might exist so you can test for them all (and there is probably a development process involving some "oh, there's a new error I had not anticipated"). I am not sure of the logic in identifying the errors you have highlighted. Perhaps "is the difference in times in column C anything but 1 hour" (=round(C3-C2,8)<>1 -- the round() function is there to capture any potential round off error due to binary arithmetic).
2a) With the boolean logic in place to identify the error conditions, you might choose to use Conditional Formatting to highlight the rows where the errors exist. Can I assume you are familiar with conditional formatting? If not, there are several tutorials on the internet or in your help files that describe how to use conditional formatting.
2b) With the boolean logic in place in the spreadsheet, you can use the Autofilter tool (Data Tab) to display only those rows that are flagged with the error. Again, can I assume you are familiar with autofilter? If not, search your help files for autofilter and you should find help for creating filtered lists.
3) Deal with these errors as you see fit. You have not explained what you want to do with these errors. If you have trouble dealing with these errors in the way you want, let us know and we'll see what suggestions we can make.