Attempting a formula with multiple criteria

Sally Osborne

Hi, can anyone help with the formula for the following required outcomes?

• $0 if the management agreement has not started [Mgnt Start Date], 
• Equal to the chargeable amount [chargeable amt]  if the Management agreement has started ie [Mgnt Start Date] in the prior month
• Equal to the previous month [Jan 17] if the agreement has started and the anniversary is not in this month.[Anniversary mth]
• Equal to the previous month + a percentage increase [% increase]  if the management agreement anniversary is in this month

Thanks 

 

Shaine Greenwood

Hello Sally,

You'd want to make a Nested IF statement that will return the values you need. An example is below, you may need to tweak this to best fit your needs:

=IF([Mgnt Start Date]1 < TODAY(), 0, IF(AND(MONTH([Mgnt Start Date]1) = (MONTH(TODAY()) - 1), YEAR([Mgnt Start Date]1) = YEAR(TODAY()), [chargeable amt]1, IF(AND(MONTH([Anniversary mth]1) <> MONTH(TODAY()), YEAR([Mgnt Start Date]1) < YEAR(TODAY())), [Jan 17]1, IF(AND(MONTH([Anniversary mth]1) = MONTH(TODAY()), YEAR([Mgnt Start Date]1) < YEAR(TODAY())), [Jan 17]1 * (1 + [% increase])))))

I'm assuming that your "Anniversary mth" column is a date column on this.

If you receive an #UNPARSEABLE error, you may need to fix some parenthesis that I could've misplaced.