nested ifs

debbie5154

I have the following nested if, for the highlighted line item (see picture):

IF NOT 'exclude' AND 'Sum fill rate gallons actuals' > 0 THEN 'Sum fill rate gallons actuals' ELSE IF 'Sum fill rate gallons PY' > 0 THEN 'Sum fill rate gallons PY' ELSE IF 'fill rate gallons FOR  movement avg' > 0 THEN 'fill rate gallons FOR  movement avg' ELSE 0

The highlighted row should be returning one of the other line items and it's giving me complete different results.  There is no version on this module or the module the original data comes from.  Any thoughts, or is my formula too complex?  Thank you so much for any suggestions.

obriegr

@debbie5154

Above I have created what your Nested IF tree looks like and interacts.

Assuming you did a direct copy + paste of your line item, and assuming that your time modules are set correctly. I would look into your exlusion property/line item, and or add additional AND statements that interact with the 'exlusion' property. Do you have other line items later in the module that have similar names that is in the wrong place; eg. exclusion vs exclusions.

Your formula is most definitely not too complex, I have some cases that had over 50 if then statements. As a best practice I change the model to complete a lookup of a list and we amend the list to reflect appropriately versus having to rewrite the if-then arguement every few months.

I find it helpful to generate a visual (as I have above; done in Microsoft Visio in under 5 minutes) and generate a literally action by action to make sure that I have the formula working properly. As well as generate what I expect to occur and throw test cases at it (by hand!) and validate that I have the appropriate result.

Sometimes simply re-creating the formula (clearing it out, and rewriting it without copy pasting) can fix some issues.

As the other responder said, there are too many factors for me to give you the answer, but the formula looks close, but I can not say for sure that it is pulling correctly or doing what you want to do.

As a best practice, I would look into your naming convention so that they are consistent (either all fill rate or fill-rate; not intermixed, capitalization is consistent; 'FOR' vs 'for', spacing is consistent; always 1 vs 2 spaces between words, first character of a line item capitalized or not). Some of these things can be ignored, but at that point I would use conditional formatting if you are attempting to bring attention to a particular area of the table of data.

Nested_Ifs_Question_37643.JPG