Help with an Excel Formula?

N

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?
 
=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
 
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.
 
You might want to investigate Excel array formulas. Just search on it, they very useful for this type of logic.
 
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.
 
=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.
 
mikeblas said:
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.
Click to expand...


Ahh... inexact match... excellent!

Much better than doing nested if statements.
 
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
 
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.
 
You must log in or register to reply here.
Back
Top