Forum Discussion
Help with Excel IF and AND or OR functions
Hello ,
Please check the attached file. I tried to combine several conditions all together how ever the results shoudl be checked throughly
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
Hello ,
Please check the attached file. I tried to combine several conditions all together how ever the results shoudl be checked throughly
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
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")
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
