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, 2018 at 7:18
  • @MikeFitzpatrick Nope. I don't know what the limit is but it's at least 8.
    – robinCTS
    Jun 13, 2018 at 7:30
  • 1
    @MikeFitzpatrick Just checked and it works nested for at least 42 deep ;-)
    – robinCTS
    Jun 13, 2018 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 Sinha
    Jun 13, 2018 at 7:36
  • 2
    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, 2018 at 7:44

1 Answer 1

Reset to default
1

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .