Forum Discussion

Angela_Wilkes's avatar
Angela_Wilkes
Copper Contributor
Mar 20, 2020

Help to create a formula

Hi I am trying to create a formula that tells me that I need to change the rate of pay for someone when they reach a new age category. I have a column and formula to create the exact age (17years, 3 ...
Formulas and Functions
  • Angela_Wilkes's avatar
    Angela_Wilkes
    Copper Contributor
    Mar 20, 2020

    The formatting is helpful thank you but what about the initial formula?

     

     

    I Require the following

     

    If (CELL) is equal to or greater than 25 then £8.21
    IF (CELL) is less than 25 or equal to or great than 21 then £7.70 
    IF (CELL) is less than 21 or equal to or greater than 18 then £6.15
    IF (CELL) is less than 18 then £4.35

     

    Do you see what I mean? Are you able to help me create that formula? 

     

    The (CELL) i refer too is an exact age formula as follows:

     

    =DATEDIF(C4,TODAY(),"Y") & " Years, " & DATEDIF(C4,TODAY(),"YM") & " Months, " & DATEDIF(C4,TODAY(),"MD") & " Days"

    SergeiBaklan 

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Mar 20, 2020

      Angela_Wilkes 

      For this formula could be

      or

      =LOOKUP(A1,{0,18,21,25},{4.35,6.15,7.7,8.21})

      but it's better to keep constants in a range.

      • Stella1000's avatar
        Stella1000
        Copper Contributor
        Sep 21, 2023

        SergeiBaklan 

         

        Hi this formula is very useful, is it possible to add another string into the formula based on length of service, as apprentices over the age of 19 but have completed their first year are entitled to the minimum wage based on their current age, apprentices under 19 and also apprentices with less than 1 years service are entitled to £5.28. Any idea how I can add to the formula to show that please

         

Resources