Multiple If Statement results in #NAME?

Chelrie · Jan 30, 2010

|.....G.......|.....J.....|
|COMPLETE|...122...|

(. = space)

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CUsers%5CChelrie%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CUsers%5CChelrie%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CUsers%5CChelrie%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style>  </style> [FONT=&quot]<o

> </o

>=IF($G28="COMPLETE",”COMPLETE”,IF($J28>1,”OVERDUE”,IF($J28<0,”DUE”,IF($J28<1,”SOON”,IF($J28<14,”PLAN”,”.”)))))<o

></o

>[/FONT]
I'm putting a formula into column c to create various texts based on the values of column G and J.

I want to have a hierarchial, multiple if statement in the order they are listed...i.e....

It's most important if g28 says "complete", I want my cell to say "Complete"
if that's not true, but J28>1, I want my cell to say "Overdue"

and so on down the line...and if none of these statements are true, then I want " "

I've tried this formula six ways to Sunday - so if anyone has any suggestions, it would be helpful.

Thanks!

jeffreybrown · Jan 30, 2010

Hi Chelrie,

Is this what you want...

=IF($G28="COMPLETE","COMPLETE",IF($J28>1,"OVERDUE",IF($J28<0,"DUE",IF($J28<1,"SOON",IF($J28<14,"PLAN",".")))))

Peter_SSs · Jan 30, 2010

Chelrie

Just checking that your logic is correct and simplified: Can you give any examples of J28 values that would produce the result "PLAN"? The only one I can see is J28 = 1. If that is the case, then I would think the last condition would be changed to 'IF(J28 = 1..' rather than 'IF(J28 < 14..'

Perhaps you could spell out the conditions in words, in order of priority?

Chelrie · Jan 31, 2010

thanks!

So this makes sense, I have a column that calculates the days between today and the due date in column J. If I can get rid of that column, and just do this calculation from column D which has the due date, that would be great - but everytime I used TODAY in a calculation I got an error.

C = Where I want to have words show up
D = Due Date
G = Status
J = Calculation between today and due date

Here is the wording priority:

If G27 says COMPLETE, then I want my cell (C27) to say COMPLETE
If G27 says something else or is blank,
and my due date is older than today - then I want my cell to say OVERDUE
if my due date is today or tomorrow - I want my cell to say DUE
if my due date is within two weeks, I want my cell to say SOON
if my due date is within four weeks, I want my cell to say PLAN

AND if all reference cells are blank it would be nice if my cell was blank.

Thanks so much for taking the time to look at this.

barry houdini · Jan 31, 2010

Try like this in C27, which doesn't require J27

=IF(G27="COMPLETE",G27,IF(D27="","",LOOKUP(D27-TODAY(),{-50000,0,2,14,28;"OVERDUE","DUE","SOON","PLAN","FUTURE"})))

If the date is 4 weeks or more in the future then formula says "FUTURE", you can change that to a blank with "" or some other text as required

Chelrie · Jan 31, 2010

worked like a charm! Thanks!

Multiple If Statement results in #NAME?

Chelrie

New Member

Excel Facts

jeffreybrown

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Chelrie

New Member

barry houdini

MrExcel MVP

Chelrie

New Member

Similar threads

Forum statistics

Share this page

Multiple If Statement results in #NAME?

Chelrie

New Member

Excel Facts

jeffreybrown

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Chelrie

New Member

barry houdini

MrExcel MVP

Chelrie

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock