How to populate information on specific columns based on frequencies i.e. Quarterly, Monthly, etc

Question

Hello,

Is there a way to write a formula that would populate a calculation based on a project's invoice frequency? I had to duplicate the Column titles in the first row so I could use those dates. I was able to get a result for a basic calculation, see below:

=IF(AND(YEAR($[Imp Date]@row) = VALUE(RIGHT([1/31/23]$1, 4)), MONTH($[Imp Date]@row) = VALUE(LEFT([1/31/23]$1, 2))), ($Rev@row * $[Share %]@row) / $[Total Invoices]@row, 0)

Now I am not sure how to make it skip certain months based on the frequency. I hope this makes sense.

KPH · Answer

Hi @Seb_Mar

How did you get on? Do you need any help taking the logic and building a nested formula? If you have what you need, please click “yes” next to Did this answer the question? on the answer. This will help others locate the information and know this is solved. If not, feel free to ask a follow up.

KPH · Answer

OK @Seb_Mar. I get it.

We could write a long formula with some nested IF, IF(AND and IF(OR.

Below is what I am thinking. Have a look at this. Wait a little in case someone has a better idea. Then try it out, if they don't. I can help write the actual formula if needed. Where I put ... I mean move onto the next section that is indented to show it is nested. (edit - the indents are removed when I post the comment - I hope this is still legible)

IF    Freq is monthly