Oct 26 2018 02:10 AM
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:
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
Oct 26 2018 02:47 AM
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.
Oct 26 2018 07:06 AM
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
Oct 26 2018 10:54 PM
SolutionHello ,
Please check the attached file. I tried to combine several conditions all together how ever the results shoudl be checked throughly
Oct 27 2018 04:04 AM
I'm not sure what the logic described covers all possible combinations, now it looks like
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")
Oct 29 2018 06:32 AM
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
Oct 29 2018 06:33 AM
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
Nov 05 2018 07:08 AM
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
Nov 05 2018 08:55 AM
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","")))
Nov 07 2018 12:53 AM
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
Nov 07 2018 02:48 AM
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.
Oct 26 2018 10:54 PM
SolutionHello ,
Please check the attached file. I tried to combine several conditions all together how ever the results shoudl be checked throughly