1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel IF functions

Discussion in 'Business Applications' started by Burner102, Aug 8, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. Burner102

    Burner102 Thread Starter

    Joined:
    Aug 8, 2012
    Messages:
    4
    I need some help in trying to create a formula that solves the following problem that has 3 variables:

    IF A1 is less that 30 then multipy by 2, IF A1 is greater than 30 but less than 60 then multiply by 3, IF A1 is greater than 60 multiply by 4.

    Any suggestions would be greatly appreciated.
     
  2. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    55,967
    =IF(A1<30 , A1*2, IF( A1 > 60, A1*4, A1*3) )
    Note the > does not include equal to

    so < is less that 30
    if you want less than or = to
    use
    <=

    same for the 60 - its greater than 60
    so if you want > and =
    >=

    so in the above 30 and 60 will also be * by 3

    =IF(A1<=30 , A1*2, IF( A1 >= 60, A1*4, A1*3) )
    so now times by 3 will not include 30 or 60

    30 will be times by 2
    and 60 will be times by 4
     
  3. Burner102

    Burner102 Thread Starter

    Joined:
    Aug 8, 2012
    Messages:
    4
    That it is perfect thanks - can anyone help with a similar but more complex problem. Using the same principle as the first problem.

    IF A1 is less than 30 then multiply by 2 - IF A1 is greater than 30 then 1 to 30 you muliply by 2 but any number greater than 31 and up to 60 multiply by 3 and then add the 2 together. If the number is greater than 60 then you add the 0 -30 +30 -60 + >60 forample if A1 = 35 then the answer would be 75. because:

    (A1-(A1-30))*2 +(A1-30)*3 = 75

    If the number was greater than 60 ie A1 = 65 then the answer would be 170

    0 - 30 = 30*2 = 60
    30 - 60 = 30*3 = 90
    + 60 = 5*4 = 20

    60+90+20 = 170

    How can i express this in an excel IF formula?
     
  4. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    55,967
    lets see if i get this correct - it will fit an IF statement - just getting the condition and maths correct

    condition 1
    IF A1 is less than 30 then multiply by 2 -

    condition 2
    IF A1 is greater than 30 then 1 to 30 you muliply by 2 but any number greater than 31 and up to 60 multiply by 3 and then add the 2 together.

    condition 3
    If the number is greater than 60 then you add the 0 -30 +30 -60 + >60 forample if A1 = 35 then the answer would be 75. because:

    trying to break it down
    if A1 is greater then 30 - then there isnt a 1-30
    it would just be (60 + ((A1-30)*3))

    so in my IF statement that would be
    =IF(A1<=30 , A1*2, IF( A1 >= 60, A1*4, (60 + ((A1-30)*3))) )

    Again if the number is greater than 60
    you must have the
    60 and the 90 already - so that would be
    (150 + ((A1-60)*4))

    so in the formula

    =IF(A1<=30 , A1*2, IF( A1 >= 60, A1*4, A1*3) )
    that would be
    =IF(A1<=30 , A1*2, IF( A1 >= 60, (150 + ((A1-60)*4)) , A1*3) )

    so the whole thing becomes

    =IF(A1<=30 , A1*2, IF( A1 >= 60, (150 + ((A1-60)*4)) , (60 + ((A1-30)*3))) )

    unless i have missed understood completely
     
  5. Burner102

    Burner102 Thread Starter

    Joined:
    Aug 8, 2012
    Messages:
    4
    OK thanks for your help - however i am not quite sure it is there. Let me try and explain better! (i just re read my problema nd realise it was not very clear)

    I want to create a spreadsheet that calculates the following percentages within a sliding range

    Range 1: 0 to 30,0000 = 10%
    Range 2: 31,000 to 60,000 = 20%
    Range 3: 61,000+ = 30%

    However, I want to calculate the figures for each range. The figure above the range is only calculated with the amount that is above the sliding scale.

    For example:

    If i want to work out the figure for 75,000 I would

    Range 1 -The first 30,000 of the 75,000 is 10% of this figure (10%of 30000 = 3,000)
    Range 2 - The second 30,000 of the 75,0000 is 20% of this figure (20% of 30,000 = 6000)
    Range 3 The third range is the remaing 15,000 (30k + 30k+15k = 75K) (30% of 15,000 = 4,500)

    Therefore the answer i want is 3000 +6000 + 4500 = 13,500

    I just need to a formula that can calculate this - it might not be an IF statement!

    Thanks in advance for your help - i hope this is a tad clearer!
     
  6. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    55,967
    so if the number is over 60 ie above 61
    then it has to have the range 1 and range 2 for the maximum amount - so it will always be the 3000+ 6000 = 9000 - so there is no need to do any calculations because its got to be
    9000 + what ever the % is for the above 60 bit

    so the formula would be

    9000 + (the calc for the over 60 bit)

    which is ( the value - 60 ) * 0.3 to work out the 30% of the remaining part

    9000 + (( 75,000-60,000 ) * 0.3)
    9000 + ((15000)*.3)
    9000 + 4500
    =
    13500

    is that correct

    =IF(A1<=30000 , A1*0.1, IF( A1 > 60000, (9000 + ((A1-60000)*0.3)) , (3000 + ((A1-30000)*0.2))) )
     
  7. Burner102

    Burner102 Thread Starter

    Joined:
    Aug 8, 2012
    Messages:
    4
    Perfect and a lot more obvious than i thought! thank you
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1064288