Conditionally Display Fixed Value Every N Columns

[TacticalExcel]

Hello,

I am looking for some help with writing a formula that places a fixed value into a cell every N columns. The formula I am using is below.

When I try each part of the formula individually it returns the expected results, but I must be missing something because when I put everything together it does not return my desired results.

=IF($D$4=F7,$D$5,IF(F7<$D$4,"",IF(MOD(COLUMN(F10)-1,$C$8)=$D$5,$D$6,"0")))


In this example D4 is a date indicating a starting payment period, and F7 is a date in a column header. D5 is the $ figure to display when D4 is equal to the date in the selected column.

Once the date in a given column is beyond the initial payment date I want to display a different payment amount ever N months. In this case N is driven by the value in C8 in the formula above (e.g. I could switch the payment cadence from once every 2 months to once every 3 months).

I hope my explanation is not too confusing. I wanted to include an image of some sample data, but I was unable to get one to display.

Thank you.

 

[James006]

Hello,

You do not explain your Headers row F7:Z7 ... and the frequency ...

But you should check your Mod calculation ...

=IF(MOD(COLUMN(F10)-1,$C$8)=0,$D$5,$D$6)

Hope this will help

 

[TacticalExcel]

Hello,

You do not explain your Headers row F7:Z7 ... and the frequency ...

But you should check your Mod calculation ...

=IF(MOD(COLUMN(F10)-1,$C$8)=0,$D$5,$D$6)

Hope this will help

F7:Z7 are a set of rolling dates in the format "mmm-yyyy". In other words F8 is 1/1/2018 and F7 is Jan-2018. G7 is Feb-2018 and so on.

Does this provide more clarity?

 

[James006]

Thanks ...

But have you tried the formula suggested in message # 2 ...???