**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.*