Aug 12 2021 09:09 PM
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?
Aug 12 2021 10:44 PM
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.
Aug 13 2021 01:04 AM
Aug 13 2021 02:22 AM
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.
Aug 12 2021 10:44 PM
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.