Excel - IF Function Problem - Expert Solution

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

I need to combine multiple if conditions with and conditions
Solved by F. J. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 05/08/2018 - 02:11
Hello
Excelchat Expert 05/08/2018 - 02:11
thanks for using the got it pro
Excelchat Expert 05/08/2018 - 02:11
may i know how can i help u
User 05/08/2018 - 02:12
it is little bit complicated statement
Excelchat Expert 05/08/2018 - 02:12
ok do you have your file so we can check on it
User 05/08/2018 - 02:13
wait let me first tell you the conditions then i will send you the file
Excelchat Expert 05/08/2018 - 02:13
ok
User 05/08/2018 - 02:15
If Handicapped =Yes, Pay Level = 1 or 2, Basic Pay between 0 to 22400 then TA = 1350*2 If Handicapped =No, Pay Level = 1 or 2, Basic Pay between 0 to 22400 then TA = 1350 If Handicapped =Yes, Pay Level = 1 or 2, Basic Pay = 22400 and above then TA = 3600*2 if Handicapped =No, Pay level = 1 or 2, Basic Pay = 22400 and above then TA = 3600 If handicapped = Yes, Pay level = 3 to 8, basic pay 22400 and above then TA = 3600*2 if Handicapped =No, Pay level = 3 to 8, Basic Pay = 22400 and above then TA = 3600 If handicapped = Yes, Pay level = 9 to 13, basic pay 22400 and above then TA = 7200*2 if Handicapped =No, Pay level = 9 to 13, Basic Pay = 22400 and above then TA = 7200
User 05/08/2018 - 02:15
these are the conditions
User 05/08/2018 - 02:16
And here is the excel sheet
[Uploaded an Excel file]
Excelchat Expert 05/08/2018 - 02:16
ok analyzing now on it
User 05/08/2018 - 02:16
I want result in Column G3
Excelchat Expert 05/08/2018 - 02:16
ok
Excelchat Expert 05/08/2018 - 02:16
let me have a minute to analyze it first
User 05/08/2018 - 02:16
Ok. Take your time
User 05/08/2018 - 02:18
Did you got the concept
Excelchat Expert 05/08/2018 - 02:18
yes
User 05/08/2018 - 02:18
ok. Then help me now
Excelchat Expert 05/08/2018 - 02:18
im just checking all the condition that you give
User 05/08/2018 - 02:18
okay
Excelchat Expert 05/08/2018 - 02:20
the formula for column F, is it already correct?
User 05/08/2018 - 02:21
yeah its correct
Excelchat Expert 05/08/2018 - 02:21
ok
User 05/08/2018 - 02:21
you only have to work on TA(column F)
Excelchat Expert 05/08/2018 - 02:21
ok, im just checking it all since condition are link on those values
User 05/08/2018 - 02:22
okay check it
Excelchat Expert 05/08/2018 - 02:23
so we have 8 condition for this column right?
User 05/08/2018 - 02:23
Yes
Excelchat Expert 05/08/2018 - 02:23
ok i got it now
Excelchat Expert 05/08/2018 - 02:23
just give me a minute to work on this :)
Excelchat Expert 05/08/2018 - 02:24
you could extend our time, so we can finish your template
User 05/08/2018 - 02:24
Actually you have 13+13 conditions to apply. 13 for handicapped and 13 for normal people
Excelchat Expert 05/08/2018 - 02:24
ok
User 05/08/2018 - 02:25
because pay is level wise.
User 05/08/2018 - 02:26
and you can omit basic pay condition from level 4
Excelchat Expert 05/08/2018 - 02:26
yes don't worry i will work on all that condition
User 05/08/2018 - 02:26
okay
User 05/08/2018 - 02:27
how to extend time ??
Excelchat Expert 05/08/2018 - 02:27
there have an option will promt
Excelchat Expert 05/08/2018 - 02:27
if we are below 3 mins
User 05/08/2018 - 02:27
okay
User 05/08/2018 - 02:28
I have extended our time. You can take your time
Excelchat Expert 05/08/2018 - 02:28
ok :)
User 05/08/2018 - 02:35
I m eagerly waiting for the solution
Excelchat Expert 05/08/2018 - 02:35
yes im still working on it
Excelchat Expert 05/08/2018 - 02:36
i will advise you once donw
Excelchat Expert 05/08/2018 - 02:36
done
User 05/08/2018 - 02:36
Will you make it on time ??
Excelchat Expert 05/08/2018 - 02:36
yes
Excelchat Expert 05/08/2018 - 02:36
we have another 20 minutes if this time will run out again
Excelchat Expert 05/08/2018 - 02:36
dont worry i can finish it on time :)
User 05/08/2018 - 02:37
Then its okay
Excelchat Expert 05/08/2018 - 02:42
how about if the condition is like this
Excelchat Expert 05/08/2018 - 02:42
If handicapped = Yes, Pay level = 3 to 8, basic pay between 0 to 22400
Excelchat Expert 05/08/2018 - 02:42
what is the TA?
Excelchat Expert 05/08/2018 - 02:43
on pay level 1 & 2 has the condition for below 22400
Excelchat Expert 05/08/2018 - 02:43
the others don't have
Excelchat Expert 05/08/2018 - 02:44
also for this one
Excelchat Expert 05/08/2018 - 02:44
if Handicapped =Yes, Pay Level = 1 or 2, Basic Pay = 22400 and above then TA = 3600*2
Excelchat Expert 05/08/2018 - 02:44
is it correct?
User 05/08/2018 - 02:44
See minimum Basic Pay is not below 18000 and it if basic pay is below 22400 he is entitled to get 1350. but if he is a handicapped then he will get double TA
User 05/08/2018 - 02:45
Yes Conditions are correct
Excelchat Expert 05/08/2018 - 02:45
even the paylevel was 1or 2, it still be 3600*2?
User 05/08/2018 - 02:46
Yes. BUt basic pay should be 22400 and above
Excelchat Expert 05/08/2018 - 02:46
ok
User 05/08/2018 - 02:47
Now did you got the conditions ?
Excelchat Expert 05/08/2018 - 02:47
im almost near
User 05/08/2018 - 02:48
Time extended
Excelchat Expert 05/08/2018 - 02:54
[Uploaded an Excel file]
Excelchat Expert 05/08/2018 - 02:54
check you check now on this
User 05/08/2018 - 02:55
Wait
Excelchat Expert 05/08/2018 - 02:55
you may check the solution on sheet2
User 05/08/2018 - 02:56
But I have already made yes no column in column L3
User 05/08/2018 - 02:57
and level in R1
Excelchat Expert 05/08/2018 - 02:57
just go on the sheet 1(2)
Excelchat Expert 05/08/2018 - 02:57
the solution was there
User 05/08/2018 - 02:57
i m on sheet 2 only
Excelchat Expert 05/08/2018 - 02:58
ok then you type yes on L3?
Excelchat Expert 05/08/2018 - 02:59
oh wait
User 05/08/2018 - 02:59
hmm i will modify the formula
Excelchat Expert 05/08/2018 - 02:59
i seen error now
Excelchat Expert 05/08/2018 - 02:59
[Uploaded an Excel file]
Excelchat Expert 05/08/2018 - 02:59
here you go again
User 05/08/2018 - 03:01
You are so close
Excelchat Expert 05/08/2018 - 03:01
ok
Excelchat Expert 05/08/2018 - 03:01
which one still not work?
User 05/08/2018 - 03:02
Pay level needs a modification. Pay level is required to be choosen form column R1
User 05/08/2018 - 03:02
NOt to be entered manually
Excelchat Expert 05/08/2018 - 03:02
oh i see
Excelchat Expert 05/08/2018 - 03:02
wait
User 05/08/2018 - 03:02
hmm waiting
Excelchat Expert 05/08/2018 - 03:03
it was level I,II, III?
Excelchat Expert 05/08/2018 - 03:03
can we make that as number?
User 05/08/2018 - 03:04
Yes you can
Excelchat Expert 05/08/2018 - 03:04
ok
Excelchat Expert 05/08/2018 - 03:07
[Uploaded an Excel file]
Excelchat Expert 05/08/2018 - 03:07
you could check it now
User 05/08/2018 - 03:07
wait
Excelchat Expert 05/08/2018 - 03:07
ok
User 05/08/2018 - 03:09
Problem in choosing basic pay as 22400
Excelchat Expert 05/08/2018 - 03:09
which one?
User 05/08/2018 - 03:09
Its returning only 1350
User 05/08/2018 - 03:10
in any level
Excelchat Expert 05/08/2018 - 03:10
since we dont have that condition
Excelchat Expert 05/08/2018 - 03:10
that one you gave was all above 22400
User 05/08/2018 - 03:10
Check third statement
Excelchat Expert 05/08/2018 - 03:10
is it ok for you to raise the quastion again so we can finish it?
User 05/08/2018 - 03:10
basic pay = 22400 and above

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