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.

On a spreadsheet i have job roles and there average hours per shift. Different roles work different lengths of shift and these lengths are graded, IE less than 5 hours scores 0. Between 5-10 scores 1. Anything over 10 scores 0. Then another role hours logged between 5-11 will score 1. I've tried several IF functions that work for one role but can not get the formula to work for either role in the same cell
Solved by O. J. in 27 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 07/05/2018 - 01:45
Hi there!
User 07/05/2018 - 01:45
hi
Excelchat Expert 07/05/2018 - 01:45
I am sure I can help you with your IF function... sounds like just a small tweak is required.
Excelchat Expert 07/05/2018 - 01:45
Are you possibly able to share your file that you're working with so I can take a peek?
User 07/05/2018 - 01:46
yes
User 07/05/2018 - 01:47
im not sure actually due to data protection
User 07/05/2018 - 01:47
i can quickly copy the info into the google sheet?
Excelchat Expert 07/05/2018 - 01:47
Totally understand
Excelchat Expert 07/05/2018 - 01:48
Yeah perhaps maybe just copy the parts that I need to see into the google sheet
Excelchat Expert 07/05/2018 - 01:48
or make a quick example
User 07/05/2018 - 01:50
does this make sense?
Excelchat Expert 07/05/2018 - 01:52
I think so... i just need to understand the desired logic for each role
User 07/05/2018 - 01:52
ok
Excelchat Expert 07/05/2018 - 01:52
so for this - "less than 5 hours scores 0. Between 5-10 scores 1. Anything over 10 scores 0"
User 07/05/2018 - 01:52
yes for an operative
Excelchat Expert 07/05/2018 - 01:52
is that just for one role?
Excelchat Expert 07/05/2018 - 01:52
gotcha
User 07/05/2018 - 01:52
for a team lead anything between 5-11
Excelchat Expert 07/05/2018 - 01:53
right
Excelchat Expert 07/05/2018 - 01:53
there are just the 2 roles correct?
User 07/05/2018 - 01:53
yeh
Excelchat Expert 07/05/2018 - 01:53
perfect
Excelchat Expert 07/05/2018 - 01:53
and the roles are in column C or J?
User 07/05/2018 - 01:53
in my sheet J
Excelchat Expert 07/05/2018 - 01:53
Perfect. and the formula row is E, and the avg hrs are in D, correct?
User 07/05/2018 - 01:53
i should have copied them into the same columns then i wouldnt have needed to amend the formula! haha
Excelchat Expert 07/05/2018 - 01:53
its ok!
User 07/05/2018 - 01:53
yes
Excelchat Expert 07/05/2018 - 01:53
ok amazing
Excelchat Expert 07/05/2018 - 01:54
One sec I will code this for you
User 07/05/2018 - 01:54
brilliant
Excelchat Expert 07/05/2018 - 02:03
So sorry this took so long!
Excelchat Expert 07/05/2018 - 02:03
Tricky indeed.
Excelchat Expert 07/05/2018 - 02:03
I think I have it - please review the example and let me know if all is good
User 07/05/2018 - 02:03
not a problem ive been trying for 3 days! haha
User 07/05/2018 - 02:03
yeh looks spot on!
User 07/05/2018 - 02:04
thanks so much
Excelchat Expert 07/05/2018 - 02:04
yay!
Excelchat Expert 07/05/2018 - 02:04
It's my pleasure to help!
Excelchat Expert 07/05/2018 - 02:04
Please feel free to ping us with a new session if you need further assistance.
Excelchat Expert 07/05/2018 - 02:04
I hope you have a great day! All the best friend ~
User 07/05/2018 - 02:04
will do! thanks
Excelchat Expert 07/05/2018 - 02:05
If we are all good here - feel free to end the session at your convenience at the top right side of the screen. Until the next time!
User 07/05/2018 - 02:08
hi again, ive just downloaded this and when i open it in excel it comes up with #Name?
Excelchat Expert 07/05/2018 - 02:09
ah.. the IFS function may not work with your version
Excelchat Expert 07/05/2018 - 02:09
Let me change it to IF
User 07/05/2018 - 02:09
thanks :)
Excelchat Expert 07/05/2018 - 02:09
:) of course - won't be long
User 07/05/2018 - 02:09
brill
Excelchat Expert 07/05/2018 - 02:11
Try that one out
User 07/05/2018 - 02:11
yeh doesn't come up with the error now! great
User 07/05/2018 - 02:12
thanks again!!
Excelchat Expert 07/05/2018 - 02:12
:) fantastic!
Excelchat Expert 07/05/2018 - 02:12
Anytime - my pleasure

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