0

I was trying to formulate an automated indicator for the lot size of commodities, so I drafted this IF function but it gives an error saying "too many arguments". How can I overcome this hindrance?

=IF(E2="GOLD",100,IF(E2="GOLDM",10,IF(E2="SILVER",30,IF(E2="SILVERM",5,IF("CRUDEOIL",100,IF(E2="COPPER",1000,IF="COPPERM",250,IF(E2="NATURALGAS",1250,0)))))))
Improve this question
9
  • 1
    From memory, Excel's IF function can only be nested 6 deep. You should investigate the VLOOKUP function. – Mike Fitzpatrick Jun 13 '18 at 7:18
  • @MikeFitzpatrick Nope. I don't know what the limit is but it's at least 8. – robinCTS Jun 13 '18 at 7:30
  • @MikeFitzpatrick Just checked and it works nested for at least 42 deep ;-) – robinCTS Jun 13 '18 at 7:34
  • Only few corrections,, you have missed to include cell address E2 with CRUDEOIL & COPPERM,, check @robinCTS formul, will work for you. – Rajesh S Jun 13 '18 at 7:36
  • 1
    Obligatory hint: Don’t try to solve everything with Excel. Take a step back and think about this again. It looks terrible and it’s not maintainable. – Daniel B Jun 13 '18 at 7:44
0

You are missing (E2 in the COPPERM part (which then requires an extra closing )). That's what's causing the error.

The CRUDEOIL part is also missing the check against E2, which I have fixed:

=IF(E2="GOLD",100,IF(E2="GOLDM",10,IF(E2="SILVER",30,IF(E2="SILVERM",5,IF(E2="CRUDEOIL",100,IF(E2="COPPER",1000,IF(E2="COPPERM",250,IF(E2="NATURALGAS",1250,0))))))))


As recommended by a few users, here's a simple example of how to use VLOOKUP() to do the same thing as your formula:

Worksheet Screenshot

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.