Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

3 CONDITIONS formuale

0

Hi, attached is a file for my salary calculation and need to base on age.

Look at sheet "monthly-(2)" col H2, my formulae able to perform the 1st and last conditions but enable to perform the center condition.

Condition:

H2 = sum of E2:G2, and multiply by either if D2=>55 * 20%, if D2>55 and <=60 * 13%, if D2 >=61*7.5%.

Please assist to correct my mistakes.

Thank you

Answer
Discuss

Discussion

Am sorry. the Cpf rate criteria is 
age : < = 55 is 0.20
         >=55 years 1 mth and <=60.0 is 0.13
        >=60 years 1 mth and <=67.0 is 0.075.
My excel skill is very basic there I may read up more b4 i could understand your suggestion.  I need to go goggle to study it first.  Hope you can assist. tq
kywong (rep: 6) Apr 22, '20 at 9:45 am
Funny people! You calculate the age from DOB. That's where you adjust the 1 month. So, an employee doesn't get rated as 55 yrs old until 1 month later. But they may not be as funny as it seems. Perhaps it's your description that makes them so. How about the new age becoming applicable "on the first day of the month following the month of the birthday"? So, anybody born in March turns 55 on April fool's day. Still, this consideration goes into the calculation of the age which should be renamed to be "Applicable Age" for clarity. What the INDEX function does with the "Age" remains unaffected.
Variatus (rep: 4889) Apr 22, '20 at 10:26 pm
hi, thank you so much. It's my cpf criteria description not clear.

<= 55 is 0.20. (meanining once a pax reached 55 on that month he will contribute 20% but come next month he will be in the range of age >55.0 and <=60 and contribute 13%.)
Anyway thank you so much for your advise.
kywong (rep: 6) Apr 22, '20 at 10:58 pm
Add to Discussion

Answers

0
Selected Answer

The solution lies in the realisation that you don't have 3 brackets but 4.

  • Below 55
  • Above 55
  • Above 60
  • Above 65

Pay close attention to the meaning of "below 55". It excludes someone who is 55. Therefore the following descriptions are wrong. They shouldn't be "Above 55" but "55 and above" because the employee who is 55 - and therefore excluded from the first group - must be included here.

So, I created a named range on your sheet "CPF rate" and called it AgeBrackets. It doesn't matter where this range is but it must have 4 cells with the numbers 1, 55, 60 and 65 in them. The first number could/should be 0. It's insignificant but the bracket "below 55" must start somewhere.

Now, I applied the Custom cell format 0 "and above" to cells 2:4. To cell #1 I applied a custom format "Normal". That simply has the effect that whatever number you enter the display will be "Normal" but for calculation and lookup purposes the cell retains the value of the entered number, here 1 or 0. So, now you have a range by the name of AgeBrackets with the values 1, 55, 60, 65 which are displayed as 

Normal
55 and above
60 and above
65 and above

Since you have 4 brackets, what are the associated CPF rates?

  • Normal = 20%
  • 55 and above is 13%
  • 60 and above is 7.5% and
  • 65 and above is 0% (I presumed)

This range of 4 cells I gave the name of E_CPF (for Employee's CPF) and I created a corresponding range C_CPF (for Company CPF) to hold the rates payable by the employer.

Now your formulas in H2 and K2 can be

[H2] =INDEX(E_CPF,MATCH($D2,AgeBrackets,1))*SUM($E2:$G2)
and
[K2] =INDEX(C_CPF,MATCH($D2,AgeBrackets,1))*SUM($E2:$G2)

Observe that I used absolute column addressing so that the formula can be copied from column H to column K and only one letter changed.

Read up on the MATCH function before you set up a similar arrangement for the SFD rate. I recommend to avoid the TRUNC function. Use ROUND() instead.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login