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.
