Forum Discussion

Mel_from_the_Territory's avatar
Mel_from_the_Territory
Copper Contributor
Aug 12, 2021
Solved

IF Statement Pain Scores

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...
excel
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Aug 12, 2021

    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.

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Aug 13, 2021

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.