CODEWITHSUNDEEP
excelif-statementexcel-formulaworksheet-function
SAFEEN 1990
SAFEEN 1990

Reputation: 310

Excel Nested IF statement with various text conditions

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: 24050

Answers (3)

MikeD
MikeD

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

user3819867
user3819867

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

pnuts
pnuts

Reputation: 59495

Please try: 

=IF(H5="CIVIL",5000,IF(OR(H5="ARCHITECT",H5="ELECTRIC"),3000,2000))*I5

Upvotes: 2

Related Questions