# Help with an Excel Formula?

#### noobman

##### [H]ard|Gawd
Hey all,

I need to create a nested if statement for Excel.

This is what it would look like in a normal programming language:

Code:
``````if (orders < 500)
cost=20;
else if (orders >= 500 && < 1000)
cost=18;
else if (orders >= 1000 && < 1500)
cost=16;
else if (orders >= 1500 && < 2000)
cost=14;
else if (orders >= 2000 && < 4000)
cost=13;
else if (orders >= 4000 && < 6000)
cost=12;
else if (orders >= 6000 && < 7000)
cost=12;``````

#### Pwyl_The_Destroyer

##### Limp Gawd
=IF(A1<500,20,(IF(A1<1000,18,IF(A1<1500,16,IF(A1<2000,14,IF(A1<4000,13,IF(A1<6000,12,IF(A1<7000,12))))))))

Should get you started

#### nameless_centurian

##### Gawd
the general form is:

Code:
``=if(condition_criteria, value_if_true, value_if_false)``

and you can nest any functions you want in any parameter as Pwyl_The_Destroyer has demonstrated.

#### heatlesssun

##### Extremely [H]
You might want to investigate Excel array formulas. Just search on it, they very useful for this type of logic.

#### mikeblas

##### [H]ard|DCer of the Month - May 2006
I'd use the lookup functions; you can find the first item within a range, then get the values associated with that range. Once it's hooked up, you can just edit the reference table to change the values around.

#### rayman2k2

##### Supreme [H]ardness
=IF(H31<7000,IF(H31<=6000,IF(H31<=4000,IF(H31<=2000,IF(H31<=1500,IF(H31<=1000,IF(H31<500,20),18),16),14),13),12),12)

That may not be fully accurate, but the general gist, take a look and see if you can figure out what I did there.

#### Pwyl_The_Destroyer

##### Limp Gawd
I'd use the lookup functions; you can find the first item within a range, then get the values associated with that range. Once it's hooked up, you can just edit the reference table to change the values around.

Ahh... inexact match... excellent!

Much better than doing nested if statements.

#### rayman2k2

##### Supreme [H]ardness
Ahh... inexact match... excellent!

Much better than doing nested if statements.

Nah, nested if statements ftw, not complicated or overly-heinous at all!

#### bonsai

##### [H]ard|Gawd
I'd setup a reference table and use vlookup to find the values. That way if your ranges and/or prices change you only have to update them in one place, not every cell. Also other tables will be able to access the same reference values.

Check out: http://contextures.com/xlFunctions02.html under Create a VLookup formula for a range of values

#### zaxour

##### [H]ard|Gawd
You may want to consider doing some macros using simple Visual Basic (hit alt-F11):

Right Click on "VBAProject(Filename.xls), click Insert>Module, and paste the following code into your module:

Function Cost(orders As Double)
If orders < 500 Then
Cost = 20
ElseIf orders >= 500 And orders < 1000 Then
Cost = 18
ElseIf orders >= 1000 And orders < 1500 Then
Cost = 16
ElseIf orders >= 1500 And orders < 2000 Then
Cost = 14
ElseIf orders >= 2000 And orders < 4000 Then
Cost = 13
ElseIf orders >= 4000 And orders < 6000 Then
Cost = 12
ElseIf orders >= 6000 And orders < 7000 Then
Cost = 12
End If
End Function

Now you can just do =Cost(CELL) to calculate.

#### Pwyl_The_Destroyer

##### Limp Gawd
Nah, nested if statements ftw, not complicated or overly-heinous at all!

You'll notice that was my first solution as well (except my formula works as-is =P )
The lookup is much more maintainable for the future though

#### rayman2k2

##### Supreme [H]ardness
You'll notice that was my first solution as well (except my formula works as-is =P )
The lookup is much more maintainable for the future though

Yeah, far easier to update, etc.