[Burner102]

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]

=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

[Burner102]

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]

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

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]

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!

[etaf]

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]

Perfect and a lot more obvious than i thought! thank you