# Formula Generator

### Question

• Hello:

I am a novice when it comes to generating formulas and the IIF can get complicated. Is there some type of tool that will generate the logic?

For example:

IF field1 > 1 AND field1 < 5 (do this)

ELSE

field1 > 6 and field1 < 10 (do this)

ELSE

field1 > 10 and field1 (do this)

etc.

Thursday, August 17, 2017 10:26 PM

### All replies

• KP_FNA123,

Yeah, it's called the human mind. Seriously though, I'm not aware of any tool that will generate logical equations for you but your example is pretty straightforward. The syntax would look like this:

IIF([field1] >1 and [field1] < 5, "do this", IIF([field1]>6 and [field1]<10, "do this", IIF([field1] > 10 and [field1] < xx, "do this", etc.)))

I agree, nested IF statements can get complicated in a hurry and you can only nest 15.

An alternate is the SWITCH statement which goes:

SWITCH(expresssion1, value1, expression2, value2, etc.)

If you have a specific formula you want to implement in Project, we can help you right here on this forum. Just give us the details and we'll get you going.

As far as more formal help with formulas, you might want to take a look at the following link:

http://www.ismetkocaman.com/Formulas/eBook.html

Hope this helps.

John

Thursday, August 17, 2017 11:31 PM
• Thank you John, for mentioning the website. Here are some other resources:

Project functions for custom fields
http://office.microsoft.com/en-us/project-help/project-functions-for-custom-fields-HP010080956.aspx

Guide to expression syntax
http://office.microsoft.com/en-us/access-help/guide-to-expression-syntax-HA010341730.aspx

Functions (Category List)
http://msdn.microsoft.com/en-us/library/office/ff836861%28v=office.14%29.aspx

Using Formulas and Graphical Indicators with Custom Fields
http://msdn.microsoft.com/en-us/library/office/ee767700%28v=office.14%29.aspx

Friday, August 18, 2017 2:57 AM
• And Ismet Kocaman has a book as well or building formulas.  Its a great reference.

http://www.ismetkocaman.com/

Actually that would be a good project for someone to tackle as a project.   A tool that can take a limited bit of VBA code and create formulas.

Michael Wharton, MVP, MBA, PMP, MCT, MCTS, MCSD, MCSE+I, MCDBA
Website http://www.WhartonComputer.com
Blog http://MyProjectExpert.com contains my field notes and SQL queries

Friday, August 18, 2017 3:13 AM
• Thank you Michael, for your support. I hope the resources help KP_FNA123 to learn writing formulas.
Friday, August 18, 2017 8:56 PM