SOLVED

Help with Excel IF and AND or OR functions

Copper Contributor

Hi there,

 

I'm new to this group so I'm hoping that someone can help me...

 

I have a spreadsheet with data concerning staff pay scales, scale point and start date and need to be able to put in some formula to work out if they are eligible to move up onto the next payscale and what that will be.

 

There are 3 different pay scales: Teachers Main, Teachers Upper and Leadership

The criteria is that if a member of staff is on:

 

  • Teachers Main Scale point 1 for 2 years they are eligible to move up onto scale point 2.
  • If they are on Teachers Main and scale point 2-5 for one year they are eligible to go up to the next scale (3-6).
  • If they are on Teachers Main and on scale 6 they need to apply to go up onto Teachers Upper.
  • If they are Teachers Upper on any scale (between 1 and 3) for 2 years they need to apply to go up onto the next scale.
  • If they are on the Leadership Scale they can apply to go up after being on the scale for 2 years.

 

So far I have been able to put in a formula to return a value in years of how long they have been on a scale using a reference date that can be changed when necessary.

 

I have also used an IF and AND formula to return TRUE or FALSE if they are on Teachers Upper and have been for more than 2 years but I need to be able to add more to this formula to cover the rest of the criteria.

 

Any help on this would be much appreciated!

 

Thanks in advance,

Fiona

10 Replies

The best way to create this kind of multi level IFS (nested IFS) is to create them seperately in different cells and then combine them all together. Or hide the cells that includes AND, OR formulas.

 

First you need to decide the teacher's pay scale, I assume the pay scale is given in C column starting from 2 row . You can write below formula to D2 for example

=IF(C2="MAIN";1;0)

 

And further on. Using AND and OR in this type of IF formulas is a bit tricky. You have to create a table to consider all the possible checks. If you omit a single one the formula will not do what it is intended to do.

 

I can help further if you can provide a sample file.

 

Hi Erol,

 

Thank you for your quick response, I replied to your email but I have realised you probably won't get it so I have attached the file here in the hope that you can see it.  As I said in my first post I have already put some formula into it but feel free to remove/amend.

 

Regards,

Fiona

best response confirmed by Fiona Sawyers (Copper Contributor)
Solution

Hello ,

Please check the attached file. I tried to combine several conditions all together how ever the results shoudl be checked throughly

I'm not sure what the logic described covers all possible combinations, now it looks like

image.png

For the above the formula could be

=IFS((D2="Teachers Main Scale")*(E2=6),"=>Upper Pay Spine",(H2>=2)+(H2>=1)*(D2="Teachers Main Scale")*(E2>1),"=>" & E2+1,TRUE,"not now")

 

 

 

Hi Erol,

 

This is amazing - thank you so much.  I have tweaked it a little bit as the formula for Teachers Upper didn't quite work - just because the s was missing from Teachers.

 

I've tested all of the different scenario's and am happy that I'm getting the correct result returned.

 

I'm just working on getting the next scale point to show where someone is either eligible to move up to the next scale or eligible to apply for the next scale.

 

Your help has been invaluable, thanks once again!

 

Regards,

Fiona

Hi Sergie,

 

Thank you for taking the time to look at my problem and send me through a resolution.  I have used another contributors help for the main problem but I am going to look at using some of your solution for the next payscale scale point.

 

Thanks again,

Fiona

Hi again Erol,

 

I have used the formula you helped me with and added to it to work out the next scale as per the original brief:

If a teacher is on teachers main 1 - 5 they can go up onto the next scale and that would be 2-6

If a teacher is on teachers main 6 they can apply to go up to teachers upper and this is now returning a next scale of TU1

If a teacher is on teachers upper for more than 2 years they can apply to go up onto the next scale which is returning 2-4 but the user is now asking if it would be possible to return Top of Scale where a teacher is on teachers upper scale 3 as they can't go up any further.

 

I've tried to amend the formula in column I to pick up this information but I can't make it work correctly so I think it may be better to be included in column J instead.

 

Any help would be greatly appreciated!

 

Thanks in advance,

Fiona

You need to add another "IF" at the end of the nested Ifs. you have a long formula ending with:

 

=...;"Need 1 year for next scale application"),))

 

it should be:

;"Need 1 year for next scale application"), IF($C3="Teacher Upper","Top Scale","")))

Thanks Erol,

 

Unfortunately it didn't work but I've been able to use what you suggested in the first place as part of a new formula to get the information to display.

 

Regards,

Fiona

Hello,

 

I thought this would be a one condition check of cell where the position of a teacher written. So I added that in the end of the line.

 

No matter it is good to hear you have solved it. These formulas need some carefull attention as a simple mistake can ruin all the output.

 

have a nice day.

1 best response

Accepted Solutions
best response confirmed by Fiona Sawyers (Copper Contributor)
Solution

Hello ,

Please check the attached file. I tried to combine several conditions all together how ever the results shoudl be checked throughly

View solution in original post