Evaluate and return value per given criteria

[dcourtney]

Problem Statement:

Using given criteria return;
1) first value (absolute), moving left to right, that falls within the specified criteria for that item or if none found then return first value found in the row, or
2) the number one, if it exists, if not, then return zero,
for each row of the raw data set.

Example files attached.

I'm not sure what to do with this. I'm looking for some ideas or something.

Thanks!

[JeteMc]

I don't understand the logic. In H3 the value zero is the minimum for ID 1 however it is not the value chosen as being 'Actual'. On the other hand in I9 the absolute value of -0.245 is 0.245 which is the minimum for ID 5 and is chosen as being 'Actual'. Is the minimum included as is the case with I9, or excluded, as is the case with H3, or am I missing something?

[dcourtney]

Sorry for the confusion and thanks for your help.

There are two different possible types of raw data for any ID.
1) 0 or 1:
Return the number one if it exists in any column of the row. Otherwise return zero if one does not exist.
2) Any other number (non whole numbers):
Return the first value, moving left to right, that falls within the min/max criteria. If conforming value does not exist then return the first value.

Does that make sense?


Thanks!

[JeteMc]

This solution employs four helper columns (IR:IU) to take care of the four cases involve. The formulas for the helper columns respectively are:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Of which the last two are array entered formulas, activated by pressing Ctrl, Shift and Enter simultaneously from the edit mode.
The formula that populates column F is:

Please Login or Register  to view this content.

Let us know if you have any questions.

[dcourtney]

That works as needed. This is very useful. Thank you!

[dcourtney]

I don't see any problems with the sample data but when I copy the formulas over to the another sheet I'm running into problems. For one, when I have only one value in the row or when there are multiple values in the row that do not fall within the criteria range it is resulting in #N/A. Is it the difference between Excel 2010 and 2013 maybe.

Also, is the H1:Z1 reference a typo?

Please Login or Register  to view this content.

[JeteMc]

In order to address the #N/A issue lets replace the array entered formula in column IU with another:

Please Login or Register  to view this content.

Remember that it has to be activated by pressing Ctrl, Shift and Enter simultaneously while in the edit mode.
As to the H1:Z1 reference in the formula that populates column IT, that sets up an array of column numbers for the formula to work with, so not a typo.
Let us know if you have any questions.

[dcourtney]

Great! No problems now. Thank you so much!

[JeteMc]

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