Quantities Per Month

[chet645]

Hi Everyone,

I am wondering if anybody can help me come up with a consise formula so that I can enter the number of Y units to be sold every X months (the alternative result being 0). I would like the formula to work no matter how many columns I drag the formula across. For example:

Hard Code Cells:
# of Units (Y): 20
Every (X) mos: 5

Result Cells:
Month: mo1 mo2 mo3 mo4 mo5.....
Units Sold: 0 0 0 0 20......

You guys never cease to amaze me, and a solution this problem would be very helpful.

Thanks,


Chet

 

[Robert Mika]

Try in B1
=IF(MOD(COLUMNS($A$1:B$1),5)=1,20,0)
drag accross.

 

[T. Valko]

Hi Everyone,

I am wondering if anybody can help me come up with a consise formula so that I can enter the number of Y units to be sold every X months (the alternative result being 0). I would like the formula to work no matter how many columns I drag the formula across. For example:

Hard Code Cells:
# of Units (Y): 20
Every (X) mos: 5

Result Cells:
Month: mo1 mo2 mo3 mo4 mo5.....
Units Sold: 0 0 0 0 20......

You guys never cease to amaze me, and a solution this problem would be very helpful.

Thanks,


Chet

Here's another one.

Entered in B2 and copied across as needed:

=(MOD(COLUMNS($B2:B2),5)=0)*20

 

[chet645]

Very Nice! Thank you so much. Works perfectly.

 

[T. Valko]

Very Nice! Thank you so much. Works perfectly.

You're welcome. Thanks for the feedback!

 

[chet645]

Hi guys, I have an additional wrinkle that I would like to add to the formula. How would I modify the formula if I wanted to designate a month at which point the sales would begin and follow the quantities per month as I previously stated. In other words, how would I modify the formula if I wanted to designate that sales begin in month 3 (or whatever month I designate), then sale at a certain number of units per month. Any help is much appreciated.

Thanks,


Bryce

 

[T. Valko]

Hi guys, I have an additional wrinkle that I would like to add to the formula. How would I modify the formula if I wanted to designate a month at which point the sales would begin and follow the quantities per month as I previously stated. In other words, how would I modify the formula if I wanted to designate that sales begin in month 3 (or whatever month I designate), then sale at a certain number of units per month. Any help is much appreciated.

Thanks,


Bryce

If I understand what you want...

Maybe this...

A1 = start month like 3.

Entered in B2 and copied across:

=IF(COLUMNS($B2:B2)<$A1,"",(MOD(COLUMNS($B2:B2)-$A1+1,5)=0)*20)