CODEWITHSUNDEEP
excelexcel-formula
Vivek Nuna
Vivek Nuna

Reputation: 1

How to get the rows which has a value greater than a number across all columns in excel sheet?

I am newbie to the excel formulas.I have an excel sheet which has lets say 100 rows and 100 columns. columns have different values from 0 to 20. I want the rows where any of the column value is greater than 10.

How to ignore the hidden columns, if these are in the range, without modifying the range? I mean based on some IsHidden kind of property?

Upvotes: 0

Views: 8629

Answers (4)

Michael
Michael

Reputation: 4848

You can't automatically ignore hidden columns in formulas, but you can automatically ignore hidden rows. If you transpose your data, using Copy > Paste Special, you can use this formula to test the maximum value ignoring hidden rows:

=AGGREGATE(4,5,A1:A100)>10

To understand the first two parameters, you can look at the help on the AGGREGATE function, or you can type out the function manually to get pop-ups lists that explain the options.

Upvotes: 0

Error 1004
Error 1004

Reputation: 8230

You could try:

=IF(MAX(A1:C1)>10,"Greater",IF(MAX(A1:C1)=10,"Equal",IF(MAX(A1:C1)<10,"Smaller")))

Image:

enter image description here

New Version as per request:

=IF(MAX(A1:B1,E1:F1)>10,"Greater",IF(MAX(A1:B1,E1:F1)=10,"Equal",IF(MAX(A1:B1,E1:F1)<10,"Smaller")))

Upvotes: 1

Michael
Michael

Reputation: 4848

If you have data in 100 columns from A to CV, put this at the top of an empty column (CW1) and fill down:

=MAX(A1:CV1)>10

Upvotes: 2

JvdV
JvdV

Reputation: 75900

Something simple like:

enter image description here

Formula used in F1:

=IF(COUNTIF(A1:E1,">10")>0,TRUE,FALSE)

Upvotes: 2

Related Questions