# Help to create a formula

Copper Contributor

# 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 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

15 Replies

# Re: Help to create a formula

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

For such range

rule formula is

# Re: Help to create a formula

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"

# Re: Help to create a formula

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.

# Re: Help to create a formula

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

# Re: Help to create a formula

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.

# Re: Help to create a formula

@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

# Re: Help to create a formula

@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)?

# Re: Help to create a formula

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

# Re: Help to create a formula

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} )
)``````

# Re: Help to create a formula

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 :)

# Re: Help to create a formula

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

# Re: Help to create a formula

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

THANK YOU!!!!

# Re: Help to create a formula

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