Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Nov 2013
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need help - Mutliple IF conditions

    Hi,
    I need help on multiple IF conditions for:

    I have a salary range of 80-100. If I promote an individual I need to give a minimum of 10% if that takes the individual to 80 or above.

    If I give a 15% increase and still does not reach 80 keep it at 15%,

    If any increase between 10% to 15% bring this employee to 80. I need get that percentage.

    Looking forward to a simple formula for the same.

    Regards
    Pooja

  2. #2
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Manchester, United Kingdom
    Posts
    116
    Thanks
    8
    Thanked 17 Times in 16 Posts
    =IF($A5*(1+HighPercent/100)<LowSalary,HighPercent,IF($A5*(1+LowPercent/100)>=LowSalary,LowPercent,(LowSalary-$A5)/$A5*100))

    Formula in Col B alongside the old salaries in Col A, HighPercent is 15, LowPercent is 10, LowSalary is 80

    Ian.

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Pooja,

    Here is a user defined function. In Cell B2, place the formula =Promotion(A2) then copy down.

    HTH,
    Maud

    Percentages.png

    Code:
    Public Function Promotion(rng As Range)
    Application.Volatile
    For I = 0.1 To 0.15 Step 0.01
        If rng * (I + 1) >= 80000 Then
            Promotion = Format(I, "0.00%")
            Exit Function
        End If
    Next I
    Promotion = Format(I, "0.00%")
    End Function
    Attached Files Attached Files
    Last edited by Maudibe; 2014-03-26 at 06:22. Reason: B2 instead of A2

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    OK, so anyone getting 80/(1+0.15)=69.56521739 or less gets 15% and anyone getting 80/(1+0.1)=72.72727273 or more gets 10%. So a formula to calculate the % increase that will ensure these parameters are met for a value in A1 is:
    =MAX(MIN((A1*1.15<=80/(1+0.15))*15,(80/A1-1)*100),(A1*1.1<=80/(1+0.15))*10)
    Last edited by macropod; 2014-03-25 at 20:23.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    New Lounger
    Join Date
    Nov 2013
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you Ian / Maud / Paul...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •