CODEWITHSUNDEEP
excelexcel-formula
cpburke94
cpburke94

Reputation: 1277

How can I pull any cells with a value greater than a number?

I asked a similar question to this here but I realized the >=1400 can be either before or after my "x".

I'm trying to look at the number of users who are on screens larger than 1400. I have my Excel sheet, and tried doing an IF statement, but because of the "x" in the middle, it's not working properly and is instead just pulling all of the cells, even the ones with screen sizes less than 1400. I want to be able to pull all cells with Screen Resolution values larger than 1400 on either the first or second number.

All of the numbers highlighted in yellow would be the ones pulled.

I've tried doing slight variations of the =IF(--LEFT(A2,SEARCH("x",A2&"x")-1)>1400,A2,"No") but I can't find the right format to narrate that I want the formula to look at both numbers on either side of the "x".

enter image description here

Upvotes: 0

Views: 2311

Answers (5)

mstr hz
mstr hz

Reputation: 1

Command:

=IF(A1>=0.1,1,$A1)

Explanation:

If $A1 is greater than 0.1, set it to 1

Otherwise, if $A1 is not greater than 0.1 then keep it the same.

Upvotes: 0

A_Patterson
A_Patterson

Reputation: 555

As an alternative to using a complicated formula, you could just split the data up.

Create two new columns next to column A. Then use text to columns and delimit on the “x”.

In the E column use a simple formula of

IF(OR(B2>=1400,C2>=1400),”True”,”False”)

Upvotes: 0

Harun24hr
Harun24hr

Reputation: 36890

As you are using Excel365 then try below formula.

=FILTER(A2:A10,(--TRIM(LEFT(SUBSTITUTE(A2:A10,"x",REPT(" ",100)),100))>=1400)+(--TRIM(RIGHT(SUBSTITUTE(A2:A10,"x",REPT(" ",100)),100))>=1400))

Or use-

=FILTER(A2:A10,(FILTERXML("<t><s>"&SUBSTITUTE(A2:A10,"x","</s><s>")&"</s></t>","//s[1]")>=1400)+(FILTERXML("<t><s>"&SUBSTITUTE(A2:A10,"x","</s><s>")&"</s></t>","//s[2]")>=1400))

enter image description here

Upvotes: 0

basic
basic

Reputation: 11968

Another option with array formula:

=IF(OR(FILTERXML("<a><b>"&SUBSTITUTE(A2,"x","</b><b>")&"</b></a>","//b[1 or 2]")>1400),A2,"No")

Array formula after editing is confirmed by pressing ctrl + shift + enter

enter image description here

Upvotes: 0

VBasic2008
VBasic2008

Reputation: 54838

You can try this:

=IFERROR(IF(OR(VALUE(LEFT(A2,SEARCH("X",A2)-1))>=1400,VALUE(RIGHT(A2,LEN(A2)-SEARCH("X",A2)))>=1400),A2,"No"),"")

Upvotes: 1

Related Questions