Calculating portions of a whole across multiple columns

[KirkWard]

I have a row where I need a formula that will calculate the portion of a whole that is to be applied in an individual cell. The purpose is to develop sales goals for each month of the year.

For this, I have a row with twelve columns, one representing each month of the year, where I would like to have this formula display its results. I also have a data entry cell where I enter the annual sales goals. So, if $M$9 is the cell for the annual sales goal (in units) and the row where I want the monthly sales goals to appear is in the range $c$3:$N$3. At this time, no other references are involved.

As an example, if in a particular year I would set a goal of selling three (3) units, I would like a one (1) to be returned for the first, fifth and ninth months (C3, G3 and K3) with all other cells in the range showing zero. If I set a goal of selling six (6) units, I would like a one (1) to be returned for every other month, beginning with the first. If I have a goal of selling eighteen (18) units, I would like a two (2) in every other month beginning with the first month, and a one (1) in the other months.

I have been experimenting with the use of COLUMN, COLUMNS, MOD, QUOTIENT, IF and everything else I can think of, but to no avail. I would like to ask for help if someone has an idea on this.

Thanks,
Kirk Ward

[RusselB]

Suggestion
Personally I can see situations where this isn't ideal, but it might be good enough for you.

[KirkWard]

This looks like a good solution for my needs. Let me play with it a bit and respond.

[KirkWard]

That was abig help, and I will continue to work with it as a base, but it does not evenly distribute the sales goals throughout the period. additional ideas will be appreciated.

[RusselB]

The distribution appeared to be more even with higher goals....but I'll keep thinking about it

[KirkWard]

Thanks. This is to establish interim goals for a large ticket item which will only sell a few in the first year or two, then make large increases in later years as the product is proven. And, the sales force is limited, so each goal gives them time to start a campaign. We have a prototype in the works, which is why I mentioned starting with a one (1) in the first cell. Our first year goal is three (3) including that first one. Then doubling the next and attempting exponential growth through year five.

Okay, I'm being overly optimistic, but I want to build the whole workbook with reasonable formulas that can be modified to make users feel like we know what we're talking about.

Your help is greatly appreciated.

[KirkWard]

I think I'm making progress. I set this formula across all twelve columns and got values in each column, reaching the calculated number plus one (due to my plus one in the first cell). If I can figure a way to subtract the displayed result of the previous cell, then it might display the goal.

Code: Select all

=1+(ROUNDDOWN(COLUMNS($C$97:C97)*($M$10/12)))

I'm working on it. Intersting exercise.

[KirkWard]

Or, think about this, with $M$10 being the data entry cell

Code: Select all

=INT(COLUMNS($C$97:C97)*$M$10/12)

Gives the same result as above, but help page says this may give less errors.

[KirkWard]

@Russell,

I have a solution that seems to spread the numbers evenly at various levels of quantity, but does not start with any at the first month. I may have to live with that, but would prefer a solution where the first cell has values.

Any ideas?
KW

[keme]

Relating to the real world... (Haven't inspected the spreadsheet. Just a general point of view...)

You may need to set a "sales force start" point, and expect the force to work the market for a while before you have significant sales. With large sale units, it is not uncommon that there is no sales in the beginning, so zero units may be a realistic sales target for first month.

[KirkWard]

Totally understand the real world, however the first sale will be our prototype. As soon as development and testing is complete, the first unit will be sold, and we open our doors, which is why month one has a sale. May just add one to the first cell.

[RusselB]

Not home today, but if you know you're going to have that 1 in that first month, you could force that by putting a fixed 1 for the first month, then use a formula for the rest of the months.