SOLVED

IF Statement Pain Scores

Copper Contributor

HI All - I am trying to write an IF Statement for the following

 

I have two columns of numbers (very long columns).

The first list is numbers from 7-10 (A2) , the second list is numbers from 0-10 (B2).

 

I need to identify (count) the rows where the second column has a number at least 2 or more lower than the first column. The outcome would be all of the highlighted numbers below.

 

Can you help me write the formula to achieve this?

 

Mel_from_the_Territory_0-1628827517920.png

 

3 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@Mel_from_the_Territory First of all, your "numbers" are in fact texts (except for cell B2, that seems to contain the real number 10) and it's difficult to calculate with texts.

 

Start by selecting column B. On the Data ribbon, select Text-to-columns and press Finish. Repeat for column C. That should have transformed the texts to numbers and the small green triangles should be gone now.

 

With regard to the formula needed for the highlights in the screenshot you could use conditional formatting with a rule (based on a formula) like: =C2>=B2-2, applied to the range starting in C2 all the way down.

 

Or, if you prefer a formula in a separate column you could enter =IF(C2>= B2-2, "x","") in an empty column on row 2 and copy it all the way down. An x would mark the rows that meet the criteria.

Oh thank you! Such an obvious error on my part to start with that I couldn't see for looking!
Your answer Riny has worked perfectly, thank you so very much! Stay COVID Safe :)

@Mel_from_the_Territory 

My formula for conditional formatting would be

=(@FirstPain-@LastPain)<=2

but I have forgotten what that would look like pre-365.  Of further interest you also mention 'count' along with 'identify'.   A count could be obtained from

= COUNT(IF((FirstPain-LastPain)<=2,1))

Using 365 a list of the highlighted rows could be returned by

= LET(
  criterion, (FirstPain-LastPain)<=2,
  FILTER(ID, criterion))

(a similar formula would apply with legacy Excel.

image.png

 

1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@Mel_from_the_Territory First of all, your "numbers" are in fact texts (except for cell B2, that seems to contain the real number 10) and it's difficult to calculate with texts.

 

Start by selecting column B. On the Data ribbon, select Text-to-columns and press Finish. Repeat for column C. That should have transformed the texts to numbers and the small green triangles should be gone now.

 

With regard to the formula needed for the highlights in the screenshot you could use conditional formatting with a rule (based on a formula) like: =C2>=B2-2, applied to the range starting in C2 all the way down.

 

Or, if you prefer a formula in a separate column you could enter =IF(C2>= B2-2, "x","") in an empty column on row 2 and copy it all the way down. An x would mark the rows that meet the criteria.

View solution in original post