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

Seb_Mar

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

Is this the correct logic?

1. If Freq is monthly then you use the formula you have to place a value in each of the date columns.

But

2. If the Freq is quarterly the value should appear in only the date column for the month that ends after the Imp date and then every 3rd month after that?

3. Are there any other values for Freq?

Seb_Mar

That is correct!

1. That's right
2. Yes, that is right.
3. Monthly, Bimonthly, Quarterly, Semi-Annually, and Annually.

The goal is to automate a pretty big sheet so I don't have to update each month/year manually if the revenue or share changes for a project.

KPH

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

Value if true Your formula (puts in the amount)

Value if false ...

IF Freq is annually AND Month from the column heading is equal to month from Imp Date

Value if true Your formula (puts in the amount)

Value if false...

IF Freq is Semi-annually AND Month from Imp Date is equal to the month from the column heading OR equal to month from col head + 6 OR equal to month from col head -6

Value if true Your formula (puts in the amount)

Value if false...

IF Freq is Quarterly AND Month from Imp Date is equal to the month from the column heading OR equal to month from col head + 3 OR equal to month from col head - 3 or + 6 or - 6 or + 9 or -9

Value if true Your formula (puts in the amount)

Value if false...

IF Freq is Bi-Monthly* AND Month from Imp Date is EVEN and month from the column heading is EVEN OR Month from Imp Date is ODD and month from the column heading is ODD

Value if true Your formula (puts in the amount)

Value if false...

"0"

* This assumes bi-monthly means every other month judging by your sequence (not twice in one month).

KPH

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.