Nested IF Statements Many Criteria

[cglor]

Capture.PNG

I need some serious Excel help on this one.

In column D I have over 7000 decimal dates ranging from the mythical year 0 to 1999.

In Column Q I have a sample value of sulfate (SO4).

In Column V I have a list of years that are acting as my year range.

In Column AB I have a threshold that samples in Column Q must be above. This value represents an average between two dates from Column V. 1.485 is the average I found between years 1974 and 1999.

If the sample is above the threshold, I would like to have TRUE appear in Column S.

I need my IF statment to look at the value in Column Q, decide if the associated date in Column D is between 2 years in Column V. If it is, then look at the value from Column Q and see if it is above the associated value in Column AB. If it is, then say TRUE in Column S, if not, then leave blank or return FALSE.

For example, Q5 has value 0.59497 and is dated as 1998.96. Is this value greater than the 1.485 average from 1974 - 1999? No, then Column S should express this.

Help!

[6StringJazzer]

An image attachment has very little value. Just attach the Excel file. It's easier than taking a screenshot first and then attaching that.

It will be much easier to understand your problem if you provide your file. This allows us to see and experiment with your data, layout, formulas, and possibly attach a file with a completed solution.

Under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

Your file may be too large to attach as an Excel file. If so, zip it and try attaching the zip file, which is allowed to be larger.

[FDibbins]

what on earth is a decimal date?
what (real) date would something like 1998.96 represent? 0.96 of a year - 350.4 days?

[leelnich]

Hi all- cglor, this would have been much easier with workbook data ready to test. Paste it in S2 and copy down:
=INDEX($AB$2:$AB$19,MATCH(INT(D2),$V$2:$V$19,-1)+1)<Q2

2017-NOV-11 12:58 - Use the following formula instead. See post #6 for explanation.

Please Login or Register  to view this content.

NOTE: The formula yields TRUE or FALSE. It assumes column V contains STARTING dates for each period, so the 1974 threshold applies to 1/1/1974 - 12/31/1998.

[6StringJazzer]

what on earth is a decimal date?
what (real) date would something like 1998.96 represent? 0.96 of a year - 350.4 days?

The way I interpreted this was that it is not a date, it's more of a serial number where the first four digits are the year when the data was recorded. For the purposes of this analysis the only part that matters is the year.

[leelnich]

For the purposes of this analysis the only part that matters is the year.

Agreed. The period dates (column V) are integers - I took them to refer to the first moment of the year -, so I used the integer portion of the measurement dates (column D) for comparison. However, the original formula in post #4 would fail with any dates greater than 12/31/1999 because of the way MATCH works*. I have amended said post with a more robust version.

*MATCH with a match_type of -1 finds the smallest value greater than or equal to the match_value. This type works with lists sorted in descending order, but fails if the list doesn't contain such a value. To work with 21st century dates, the original formula would have required an amendment to your list as follows:

Row\Col	V	AB	AC
1	Starting date	Threshold
2	2024
3	1999	1.144862	Applies to period 1/1/1999 - 12/31/2024
4	1974	1.241145	Applies to period 1/1/1974 - 12/31/1998