none
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.

    Thanks in advance.

    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
    Moderator
  • Thank you Michael, for your support. I hope the resources help KP_FNA123 to learn writing formulas.
    Friday, August 18, 2017 8:56 PM