Excel if statements?!?! 1

[killinggameshow]

Alright, here's the deal. I need to be able to do calculations based on the a certain value falling within a certain range.

For example:

0 < X < 100 : value is 10 + (x * .15)
100 < X < 300 : value is 20 + (x * .23)

etc etc

With the IF statements only allowing for one true and one false value, i'm not sure how to go about pulling this off. Any thoughts?

 

[mikevh]

Following is from the online help for the IF worksheet
function:

[/b]
Up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. See the following example.

Suppose you want to assign letter grades to numbers referenced by the name AverageScore. See the following table.

If AverageScore is Then return
Greater than 89 A
From 80 to 89 B
From 70 to 79 C
From 60 to 69 D
Less than 60 F


You can use the following nested IF function:

IF(AverageScore>89,&quot;A&quot;,IF(AverageScore>79,&quot;B&quot;,
IF(AverageScore>69,&quot;C&quot;,IF(AverageScore>59,&quot;D&quot;,&quot;F&quot)))
In the preceding example, the second IF statement is also the value_if_false argument to the first IF statement. Similarly, the third IF statement is the value_if_false argument to the second IF statement. For example, if the first logical_test (Average>89) is TRUE, &quot;A&quot; is returned. If the first logical_test is FALSE, the second IF statement is evaluated, and so on.
[/b]

I hope this helps.

 

[Dreamboat]

You can use vlookup too.

http://www.theofficeexperts.com/excel.htm#TheDreadedVlookup

and see the Free download available too.

Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[weetnie]

Hi There
Try
=IF(A1=&quot;&quot;,&quot;&quot;,IF(A1<100,A1+10+(A1*0.15),IF(A1<200,A1+20+(A1*0.23),IF(A1<300,A1+30+(A1*0.35)))))

First if brings back blank for blank (Use 0 for calculation field) then you can get 6 more IF functions in (Can do an extra 7 if you use a another reference field.

 

[IFRs]

Have you tried something like this?

IF(AND(X>0,X<100),10+(X*.15),if(AND(X>100,X<300),20+(X*.23)))

0 < X < 100 : value is 10 + (x * .15)
100 < X < 300 : value is 20 + (x * .23)

 

[weetnie]

Try typing 100

 

[Zathras]

Slightly simpler, if you have no more than 7 ranges, is this nested IF statement:

  =A1*IF(A1<0,0,IF(A1<100,0.15,IF(A1<300,0.23,IF(A1<500,0.31,0.39))))

If you need more than 7 nested IF statements, then the better approach would be to use the VLOOKUP function.