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.
