Reputation: 310
I am creating IF statements in Excel to budget according to the discipline of engineers. I want:
IF(H5="CIVIL") then print for me value (I5*5000)
IF(H5="ARCHITECT") then print for me value (I5*3000)
IF(H5="ELECTRIC") then print for me value (I5*3000)
IF(H5="MECHANIC") then print for me value (I5*2000)
I am using a nested IF statement:
=IF(H5="CIVIL",I5,I5*5000,IF(H5="ARCHITECT",I5,I5*3000,IF(H5="ELECTRIC",I5,I5*3000,IF(H5="MECHANIC",I5,I5*2000))))
but I see the message "You've entered too many arguments for this function".
How can I use the formula without any problem?
Upvotes: 2
Views: 24053
Reputation: 8941
There will be more professions with more values, so a "single formula" approach is second best. You should use a table with profession and value, and use VLOOKUP, then you can change values and add professions as you like without updating spaghetti formula code ... e.g.
A B
1 Civil 5000
2 Arch 3000
3 Elec 3000
4 Mech 2000
Specifying a profession in H5
, you would use the following formula in I5
:
=VLOOKUP(H5,A1:B4,2,FALSE)
Looking at your edit ... one more reason NOT to hide the unit cost in a formula, because in 2016 the rates might be different --> use a table!
Upvotes: 1
Reputation: 1118
You can always open your formula in a text editor and check it. I use line breaks to separate arguments. If you don't comment like I did you can even reimplement the now formatted formula. Saved me a lot of times.
Also, I've found your formula's hiatus:
=IF(
H5="CIVIL", 'test
I5, 'If True
I5*5000, 'If False
IF( 'WAT
H5="ARCHITECT",
I5,I5*3000,IF(H5="ELECTRIC",I5,I5*3000,IF(H5="MECHANIC",I5,I5*2000))))
Upvotes: 0
Reputation: 59495
Please try:
=IF(H5="CIVIL",5000,IF(OR(H5="ARCHITECT",H5="ELECTRIC"),3000,2000))*I5
Upvotes: 2