Forum Discussion

Fiona Sawyers's avatar
Fiona Sawyers
Copper Contributor
Oct 26, 2018

Help with Excel IF and AND or OR functions

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

excel
Formulas and Functions
  • erol sinan zorlu's avatar
    erol sinan zorlu
    Iron Contributor
    Oct 26, 2018

    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.

     

Resources