CODEWITHSUNDEEP
vbaexcelexcel-formula
KMac
KMac

Reputation: 3

Determining which value in a row, if any, is greater

I have a large table of data where I need to find out if, along one row, any values are greater than a threshold number. If it finds a number, I need it to either return that number, or return the column header so I can find that number.

One problem that may occur is that there may be multiple values that exceed this threshold.

The data can't be sorted in ascending or descending order because that may mess up other orders, or take them out of the necessary column they need to be in.

Below is a small snippet of data that I'm dealing with. I need to know if the numbers are ever greater (more positive) than -50. I need to know which numbers exceed it and/or which column it's in.

I'd rather it not be vba or macro, but if needs be then such is life.

Help? Two Sample data rows

Upvotes: 0

Views: 232

Answers (2)

user6432984
user6432984

Reputation:

From the Home Tab choose Conditional Formatting

enter image description here Select the Range that you what Conditionally Formatted. Enter the formula that what evaluated. In this case >-50.
enter image description here

Upvotes: 2

Gary's Student
Gary's Student

Reputation: 96753

With data in A1 through E12, in F1 enter the array formula:

=MIN(IF(A1:E1>-50,A1:E1))

and copy down:

enter image description here

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.

Note:

If all the values are less than -50, the formula will return 0

Upvotes: 1

Related Questions