solve formula problem

Here is my working formula BUT when I add another section I get an error message.

Am I limited to a certain number of sections (excel 2000).

My working formula:

=IF(H511>0,IF(K511="YM",O511*5,IF(K511="TY",O511*3125,IF(K511="CL",O511*500,IF(K511="GC",O511*100,IF(K511="BP",O511*62500,IF(K511="JY",O511*12.5,IF(K511="US",O511*3125))))))))*(P511)

My NON workign formula:

=IF(H511>0,IF(K511="YM",O511*5,IF(K511="TY",O511*3125,IF(K511="CL",O511*500,IF(K511="GC",O511*100,IF(K511="BP",O511*62500,IF(K511="JY",O511*12.5,IF(K511="US",O511*3125,IF(K511="NQ",O511*50)))))))))*(P511)

Note: this is what was added: ,IF(K511="NQ",O511*50)

I believe you can only nest a max of 7 IF statements. Perhaps, you should be working on finding a more efficient way of doing your calculation. Post a .zip sample of your workbook for suggestions.

Originally Posted by BigBas

I believe you can only nest a max of 7 IF statements.

You're right

Originally Posted by BigBas

Perhaps, you should be working on finding a more efficient way of doing your calculation. Post a .zip sample of your workbook for suggestions.

My excel version isn't in english.Then i hope InvGrp2 will understand.I suppose you have four columns.In the first, you have your data. In the second,you calculate according your conditions but with a max of 7 if tatements.Select B1 and after,select Insert menu/Name: give a name: for example Calc1.You do the same thing in the third column: you call this column Calc2.
At least, in the fourth column, you type the following formula in D1:
=IF(Calc1;Calc1;Calc2)

Raiby:

That is one way around the limitation Excel offers, but generally, in situations like this, incorporating a variation of a lookup function is more efficient and easier to modify in the future.

Originally Posted by BigBas

Raiby:

That is one way around the limitation Excel offers, but generally, in situations like this, incorporating a variation of a lookup function is more efficient and easier to modify in the future.

Dear BigBas

In fact, i don't know really this function .Let's see what will say InvGrp2.

I'd use a VLOOKUP function. Create a table somewhere on the worksheet showing all possible values of K511 in one column and the conversion rate in the next column, e.g. if these values are in G3:H10

YM 5
TY 3125
CL 500
GC 100
BP 6250
JY 12.5
US 3125
NQ 50

Use the formula

=IF(H511>0,VLOOKUP(K511,G3:H10,2,0))*O511*P511

You can extend the table as far as you want. Be sure to adjust the formula if you do. If you don't want a table you can "hardcode" all of this into the formula, i.e.

=IF(H511>0,VLOOKUP( K511,{"YM",5;"TY",3125;"CL",500;"GC",100;"BP",6250;"JY",12.5;"US",3125;"NQ",50},2,0))*O511*P511

Thanks for all your input.

I did ended up using the Vlookup formula:

=IF($K518="","",VLOOKUP($K518,Specs2!$B$3:$D$15,2,FALSE))*Q518

with the following table:

Symbol $ Point Value $ Fees

AD $100,000.00 ($5.50)
BP $62,500.00 ($5.50)
CD $100,000.00 ($5.50)
EC $125,000.00 ($5.50)
JY $125,000.00 ($5.50)
SF $125,000.00 ($5.50)
US $3,125.00 ($4.30)
TY $3,125.00 ($4.30)
CL $500.00 ($4.30)
GC $100.00 ($4.30)
YM $5.00 ($4.30)
NQ $20.00 ($4.30)

Thanks again.

Bruce