Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Excel IF functions


(!)

Burner102's Avatar
Burner102 Burner102 is offline
Member with 4 posts.
THREAD STARTER
 
Join Date: Aug 2012
Experience: Intermediate
08-Aug-2012, 07: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's Avatar
etaf   (Wayne) etaf is online now
Computer Specs
Moderator with 49,856 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
Experience: Intermediate
08-Aug-2012, 07: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's Avatar
Burner102 Burner102 is offline
Member with 4 posts.
THREAD STARTER
 
Join Date: Aug 2012
Experience: Intermediate
08-Aug-2012, 09: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's Avatar
etaf   (Wayne) etaf is online now
Computer Specs
Moderator with 49,856 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
Experience: Intermediate
08-Aug-2012, 11: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's Avatar
Burner102 Burner102 is offline
Member with 4 posts.
THREAD STARTER
 
Join Date: Aug 2012
Experience: Intermediate
08-Aug-2012, 12:17 PM #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!

Thanks in advance for your help - i hope this is a tad clearer!
etaf's Avatar
etaf   (Wayne) etaf is online now
Computer Specs
Moderator with 49,856 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
Experience: Intermediate
08-Aug-2012, 12:28 PM #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's Avatar
Burner102 Burner102 is offline
Member with 4 posts.
THREAD STARTER
 
Join Date: Aug 2012
Experience: Intermediate
08-Aug-2012, 12:47 PM #7
Perfect and a lot more obvious than i thought! thank you
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


Tags
excel, if statements, variables

(clock)
THIS THREAD HAS EXPIRED.
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)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑