Link to home
Start Free TrialLog in
Avatar of Biggles1
Biggles1Flag for United States of America

asked on

IIF function is not working in Excel

See the following Excel table (Row numbers not showing)

Column G uses 'IIF(D1="Work",A1,0)'

Returns an error #Name? instead of 15.52

A                B               C                D                E                F                 G                H

15.52                                        Work                                                 iif(D1="Work",A1,0)

                                                                                                         

Avatar of Biggles1
Biggles1
Flag of United States of America image

ASKER

Thanks for any hint you can give me.

George
ASKER CERTIFIED SOLUTION
Avatar of Flyster
Flyster
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Biggles1
Biggles1
Flag of United States of America image

ASKER

Thanks Paul, I could ave sworn I used IIF in excel!!

Well this works fine!
Avatar of Flyster
Flyster
Flag of United States of America image
You're welcome. Glad I could help out!
Avatar of Biggles1
Biggles1
Flag of United States of America image

ASKER

One more thing Paul, if you could possibly help me:  I cant copy the If expression to other cells.  I get all sorts of errors.
Avatar of Flyster
Flyster
Flag of United States of America image
That would depend on where you are trying to paste them. The current formula has a relative reference. What that means is the cell references will change in relation to where it is copied from. For example, say you have your formula in G1. If you copy it and paste it into G2 you will get:
=IF(D2="Work",A2,0)

Open in new window

The formula was moved down one row so the row references in the formula will increase by one. Because it is in the same column, the column references do not change. Now say you wanted that formula in H1 instead of G1. If you were to copy it and paste it into H1 you would get:
=IF(E1="Work",B1,0)

Open in new window

Because you moved one cell to the right, the column references increased by one and the row reference stayed the same. If you do not want one of the references to change, you will have to change it to an absolute reference. What this means is that no matter where you paste the formula, the formula reference will remain the same. To make it an absolute reference, you need to place the dollar sign in front of it like this:
=IF($D1="Work",$A1,0)

Open in new window

Doing this, no matter where you paste this formula in row 1, the formula will still reference columns D and A and the row number will stay the same. If you paste it down from rows 2 to 10, the row reference will increase by one for each rows.
If this explanation was not helpful, let me know how you are trying to copy the formula and I'll see if I can figure out the problem.