Help to create a formula

[Angela_Wilkes]

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 months, 5 days), these cells update automatically. I have one column with the employees current wage rate and another column with the set minimum wage for that employees age range.

I would like the Minimum Wage Rate to change colour when it falls below the employees current rate of pay column.

Thank you

[#2]

@Angela_Wilkes 

You may apply conditional formatting rule with the formula which compares values of Minimum Wage Rate with value in Current Rate

[#3]

Hi, thank you for your reply, which formula?@Sergei Baklan 

[#4]

@Angela_Wilkes 

For such range

rule formula is

[#5]

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"

@Sergei Baklan 

[#6]

@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.

[#7]

@Sergei Baklan 

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

[#8]

@Stella1000 

Could you please provide small sample file or at least screenshot of how your data is structured. How Excel knows age is under 19, etc. With desired result please.

[#9]

@Sergei Baklan Hi Sergei thank you for the response I have attached a sample of my file :)

I will populate columns A to J each month 

[#10]

@Stella1000 , thank you for the file. Could you please clarify - you have conditions

- Apprentices over>19  and <1 years service

- Apprentices<19

What shall be taken if Apprentices=19?

What shall be taken if Apprentices over>19  and >= 1 years service (or you fire such)?

[#11]

Hi Sergei apprentices equal to and over 19 need to have the NMW for their age,
NMW rate
National Living Wage (23+) £10.42
21-22 Year Old Rate £10.18
18-20 Year Old Rate £7.49
16-17 Year Old Rate £5.28
Apprentice Rate £5.28
Apprentices aged 19 and over, but in the first year of their apprenticeship £5.28

thank you

[#12]

@Stella1000 

That could be

=IF(
    AND(
        $J2 = "Yes",
        OR(  $K2 < 19,
             AND( $K2 >= 19, $N2 < 1)
        )
    ), 5.28,
    LOOKUP( $K2,{0,18,21,23},{5.28,7.49,10.18,10.42} )
)

Please check attached.

[#13]

Thank you so much, I haven't got time at the moment to check, but will do as soon as I can, once again thank you, I really appreciate your help :)

[#14]

@Stella1000 , you are welcome. Please post if any questions.

[#15]

I have been able to check the calculation now and it has worked!!!!

THANK YOU!!!!

[#16]

@Stella1000 , great to know it works as expected, thank you for the feedback.