All solutions IF Expert Solution – Excel IF Problems

Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I need to match two column, if one number in a column number falls in a range within the 2nd column. The result is on track or off track. Condition: (Column A: Age, ColumnB: Rank1-8) Under Age 30, Rank 1-2 ontrack Over Age 30, Rank 1-2 off track Between Age 31-37, Rank 3-8 On Track Between Age 38-45, Rank 5-8 On Track Above 45, Rank below Rank 8 Off Track
Solved by F. Q. in 36 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 24/07/2018 - 08:35
Hi
Excelchat Expert 24/07/2018 - 08:35
How are you doing?
User 24/07/2018 - 08:35
Hi
User 24/07/2018 - 08:35
Can u help me with the formular
Excelchat Expert 24/07/2018 - 08:35
sure..
Excelchat Expert 24/07/2018 - 08:35
Do you have an excel to work with?
User 24/07/2018 - 08:36
the excel is private as I am doing analysis
Excelchat Expert 24/07/2018 - 08:36
ok
User 24/07/2018 - 08:36
I can type out some dummy column
Excelchat Expert 24/07/2018 - 08:36
yes i see it on the right give me 1 min
User 24/07/2018 - 08:37
How to produce the result automatically?
Excelchat Expert 24/07/2018 - 08:37
you are looking for the result column column c right
Excelchat Expert 24/07/2018 - 08:37
?
User 24/07/2018 - 08:38
Yes
User 24/07/2018 - 08:38
now I have manually compare age and see if they fall into the rank and determine if this person is on track or off track
User 24/07/2018 - 08:38
but I have many record
Excelchat Expert 24/07/2018 - 08:39
anything above age 30 and rank 1,2 is off track
Excelchat Expert 24/07/2018 - 08:39
anything below 30 and rank 1,2 is on track
Excelchat Expert 24/07/2018 - 08:39
right
Excelchat Expert 24/07/2018 - 08:39
how about below 30 rank 3-4
Excelchat Expert 24/07/2018 - 08:40
give me a general way you are comparing the rank and age to get the result
User 24/07/2018 - 08:40
I draw a table on the right
User 24/07/2018 - 08:40
a new table do you see it?
Excelchat Expert 24/07/2018 - 08:41
yes
User 24/07/2018 - 08:41
If below 30 and rank above is ontrack (although not happen in reality)
Excelchat Expert 24/07/2018 - 08:42
so 30 below and rank equal and above 2 then ontrack
Excelchat Expert 24/07/2018 - 08:43
ohh so anything below the rank of the age it is at then off track above is on track right
User 24/07/2018 - 08:43
so by 30 in Rank 3 is off track
Excelchat Expert 24/07/2018 - 08:45
31 and rank 3?
User 24/07/2018 - 08:45
on track
Excelchat Expert 24/07/2018 - 08:46
so 30 and 3 is also off track?
Excelchat Expert 24/07/2018 - 08:46
its on track right?
User 24/07/2018 - 08:46
Yes
User 24/07/2018 - 08:46
Yes on track
Excelchat Expert 24/07/2018 - 08:47
by your description by 30 you should at least reach rank 2 so anything above should be good is that right?
User 24/07/2018 - 08:47
Yes
Excelchat Expert 24/07/2018 - 08:47
ok give me 1 min to build the formula
User 24/07/2018 - 08:47
Thank you
Excelchat Expert 24/07/2018 - 08:53
just the 1st condition less than 30 any rank is on track right?
Excelchat Expert 24/07/2018 - 08:53
eg 25
User 24/07/2018 - 08:53
Yes
User 24/07/2018 - 08:58
Still there?
Excelchat Expert 24/07/2018 - 08:59
yes
Excelchat Expert 24/07/2018 - 09:00
using a nested if so will take some time to analyse the criterias
Excelchat Expert 24/07/2018 - 09:07
can you see the formula in column D
Excelchat Expert 24/07/2018 - 09:07
can you try to test it
User 24/07/2018 - 09:07
YEs
Excelchat Expert 24/07/2018 - 09:10
for your reference =IF($A2="","",IF($A2<=30,IF($B2>=1,"On Track","Off Track"),IF($A2<31,IF($B2>=2,"On Track","Off Track"),IF($A2<=38,IF($B2>2,"On Track","Off Track"),IF($A2<=45,IF($B2>5,"On Track","Off Track"),IF($B2>8,"On Track","Off Track"))))))
User 24/07/2018 - 09:10
I think it works thanks
Excelchat Expert 24/07/2018 - 09:10
welcome :)
Excelchat Expert 24/07/2018 - 09:11
please do click on end session and leave a feedback

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc