Help with an Excel Formula?

noobman

[H]ard|Gawd
Joined
Oct 15, 2005
Messages
1,475
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;

I've been wracking my brain on this with no results. Help?
 

Pwyl_The_Destroyer

Limp Gawd
Joined
Aug 22, 2005
Messages
139
=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
 
Joined
Apr 4, 2003
Messages
836
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]
Joined
Nov 5, 2005
Messages
44,154
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
Joined
Jun 26, 2004
Messages
12,776
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
Joined
Aug 6, 2002
Messages
8,070
=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
Joined
Aug 22, 2005
Messages
139
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.
 

bonsai

[H]ard|Gawd
Joined
Jun 27, 2008
Messages
1,282
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
Joined
Jan 31, 2008
Messages
1,129
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.
 
Top