Search Search for: Business ApplicationsAll Forums

# Excel IF functions

 Burner102
Member with 4 posts.

Join Date: Aug 2012
Experience: Intermediate
08-Aug-2012, 06:06 AM #1
Excel IF functions
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.
 etaf   (Wayne)
Moderator with 55,263 posts.

Join Date: Oct 2003
Location: Surrey, UK
08-Aug-2012, 06:16 AM #2
=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
__________________
Wayne
Please let us know what the final solution was to any problem posted
 Burner102
Member with 4 posts.

Join Date: Aug 2012
Experience: Intermediate
08-Aug-2012, 08:01 AM #3
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?
 etaf   (Wayne)
Moderator with 55,263 posts.

Join Date: Oct 2003
Location: Surrey, UK
08-Aug-2012, 10:42 AM #4
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
Quote:
 IF A1 is greater than 30 then 1 to 30 you muliply by 2
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
Quote:
 y 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 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
 Burner102
Member with 4 posts.

Join Date: Aug 2012
Experience: Intermediate
08-Aug-2012, 11:17 AM #5
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!

 etaf   (Wayne)
Moderator with 55,263 posts.

Join Date: Oct 2003
Location: Surrey, UK
08-Aug-2012, 11:28 AM #6
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))) )
 Burner102
Member with 4 posts.

Join Date: Aug 2012
Experience: Intermediate
08-Aug-2012, 11:47 AM #7
Perfect and a lot more obvious than i thought! thank you
 techguy.org/1064288
As Seen On

WELCOME TO TECH SUPPORT GUY!

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

 Tags excel, if statements, variables

Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

### Find the solution to your computer problem!

 Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)