Excel Nested IF statement with various text conditions

Question

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?

Try this =IF(H5="CIVIL",I5*33000,IF(H5="ARCHITECT",I5*16000,IF(H5="ELECTRIC",I5*12000,IF(H5="MECHANIC",I5*10000)))) — Andy
– Andy, Commented Apr 3, 2015 at 11:44
In previous time i used that way but not work my function is correct. when i use only CIVIL,ARCHITECT,ELECTRIC AND MECHANIC it is work but when i use two statement not work — SAFEEN 1990
– SAFEEN 1990, Commented Apr 3, 2015 at 11:48
The logic for nested IFs remains the same in any language you use IF( condition1, value1, IF( condition2, value2)) — Andy
– Andy, Commented Apr 3, 2015 at 11:51

pnuts · Accepted Answer · 2015-04-03 11:53:22Z

2

Please try:

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

answered Apr 3, 2015 at 11:53

pnuts

59.7k11 gold badges92 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SAFEEN 1990 Over a year ago

don't show me the error message but when i use CIVIL or ARCHITECT or ELECTRIC text every time multiple I5 Cell by 2000 and not multiple by 5000 for civil and 3000 for architect

user3819867 Over a year ago

"double inverted commas"

MikeD · Accepted Answer · 2015-04-03 15:38:42Z

1

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!

edited Apr 3, 2015 at 15:38

answered Apr 3, 2015 at 12:00

MikeD

8,9412 gold badges32 silver badges50 bronze badges

3 Comments

SAFEEN 1990 Over a year ago

please help me i don't understand your explain

MikeD Over a year ago

well ... I just abbreviated ARCHITECT by Arch ... use whatever ... and of course, if you have to multiply the rate found by VLOOKUP based on a profession in H5 with hours specified in -say- I5, you'd say =I5*VLOOKUP(H5, ... ) ... the message here really is to use a table once to specify the rates in one place, and then retrieve the values of that table using VLOOKUP rather than hardcoding important values into formulae.

MikeD Over a year ago

@pnuts re delimiters ... my bad !! I was able to mentally "translate" the first as I was typing, for the others my (Austrian) autopilot apparently took over ... corrected.

user3819867 · Accepted Answer · 2015-04-03 12:15:59Z

0

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))))

answered Apr 3, 2015 at 12:15

user3819867

1,1181 gold badge8 silver badges20 bronze badges

Collectives™ on Stack Overflow

Excel Nested IF statement with various text conditions

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related