IF Function - More than 7

[BRANDON52]

Good Day,

I need to add one more argument however getting an error. You help/advice is greatly appreciated.

This is the formula that i am using to calculate the amount of orders and what the cost would be.

# of order/ $ per order
0-5 = 2
6-7 = 5
8-9 = 8
10-11 = 9
12-13 = 10
14-15 = 11
16-17 = 12
18-19 = 13
20+ =15


=(IF(E48<=5,N48*2,IF(E48<=7,N48*5,IF(E48<=9,N48*8,IF(E48<=11,N48*9,IF(E48<=13,N48*10,IF(E48<=15,N48*11,IF(E48<=17,N48*12,IF(E48<=19,N48*13))))))))) This works perfect... However

I am trying to add another IF(E48>=20,J44*15) I get a error. Am i missing something?


If i have 20 palates. i want the formula to calculate by $15 per palate.

[Don]

Hi and welcome to the forum!

You will need a newer version of Excel (Excel 2007 or later) in order to get more than 7 nested IF's in a cell. But, this is a perfect example of when you should use a vlookup function along with a lookup table to get your pricing data. Vlookups can be tricky at first, especially when using them for price or quantity discounts, so here is a video tutorial that will walk you through exactly what you need to do:

http://www.youtube.com/watch?v=TYlf0rmNGGY

(Note that, in the long-run, using the vlookup function with a lookup table will be easier to maintain and use.)

hope this helps

[Sisyphus]

Hi,
Don is suggesting that you take a completely different approach to the problem, therefore using a completely different way to get the result. The beginning is in the understanding of the task.
You say, if the order is for 5 pcs or less the cost will be $2 etc. From this starting point your concatenated IF functions follow naturally, much like working out 5 * 4 as 4+4+4+4+4.
The alternative is to ask, what will be the cost if the order is for x pcs? from this question the logical demand is for a table, in fact, the very table you have posted. Look for the biggest number in the first column that is smaller or equal to your x and move your finger to the adjacent column to get the answer.
Instead of 7 or more operations you have only two. And if you ever need to adjust your cost you do so by modifying the table, not the formulas that read it.

Don has suggested VLOOKUP. There are other ways, too, and some of them work with any Excel still alive. For you, the shortest way to at least one solution is to post a worksheet that has your table somewhere, a couple of cells where x has been entered somewhere else, and a series of cells where you want a formula that produces the result you describe in your post.
Have a great day!

[BRANDON52]

Thanks for the replies. I will attempt to try both methods. I have attached the excel file of what i am trying to accomplish. Thanks for your assistance.

[Sisyphus]

Hi,
Look at the formula in H49:

Code: Select all

=VLOOKUP(H48,Table,2)


In plain English it says "Take the value in H48, Find the largest value in the first column of Table that is smaller than H48, Go to the 2nd column of Table in that same row and return the value you find there".
Note that the first column is implicit, the second one isn't. That is why you don't find a 1 in the formula but must have the 2. You could specify another column if your table offers more (or less) choice.

So, where is the "Table"? Table is the range $J$7:$K$14. You could use absolute addressing using A1 referencing and the formula would work the same way. If I wouldn't have told you, you could have found Table by clicking Insert/Name and then click "Table" on the list that appears.

Since you didn't provide a place for the table I have placed it where I found room. After you move it to a more suitable location select it and go to Insert/Name. The selected address appears there. Enter a name and click "Add". You can then refer to that range by the name you have given anywhere in your workbook, even on other sheets. This implies that you could place the table on another sheet if you don't find place you can spare on the one where you want to use it. You could also arrange the table horizontally across the top of your sheet and use HLOOKUP (H for "Horizontal").

It's like multiplying 4 by 5. Once you know how it works it's really quite simple, right?
Enjoy 2011! There is so little of it left.

[BRANDON52]

Thanks Sisyphus,

I understand the concept. You and Don are truly a great Help. Thank again for helping an old guy out.