How to formulate an exponential function so that the SUM is always 100%

Clarification, please:

Your statement says you want an "exponential function so that the SUM is always 100%"

But on your sample table, you show a SUM of seven times than much, and a one month 'goal?' of 200,000 arriving in September.

Is your 200,000 goal the goal for a one month total for the last month of the year, or is it the goal for your total sales from January 1 to December 31 of that year?

Here's an example showing the results for an initial sales figure of 1000 (as in your example) and a goal of 200,000 over the year, with a constant 45.9% increase in monthly sales each month.

The charts show the monthly sales (blue) and the running total sales (green) needed to reach the 100% goal.

The formula shown below the table is entered in B3 and filled down to December.

Row 14 is a Footer row.

Formulas:

B14: SUM(B)

C14: SUM(C)

C1 contains the multiplier used to calculate the 'current month's goal from the previous month's goal. 1.459, equivalent to 145.9%, was reached by 'guess and check' calculations of the whole column.

The rest of column C calculates the percentage of the goal represented by each month's expected sales.

C2: B2/200000

Fill down to December

Column D calculates the cumulative percentage of the goal expected to be reached by the end of each month.

D2: SUM(C$2:C2)

Fill down to December.

Columns C and D are the data sources for the Line Chart shown beside the tables.

Regards,

Barry

"And D2: SUM(C$2:C2) is a strange but effective formula. It took me a while to get my head around it. Thanks!"

Not all that strange.

SUM(this cell:that cell) sums all values in the range of cells starting at 'this cell' and ending at 'that cell'

The $ operator locks 'this cell' to always reference C2 as the formula is copied into cells below that in column C.

Without the operator, the reference to 'that cell' increments to reference the cell in column C that is on the row containing each copy of the formula as it is filled down the column.

Is there a way for the table to calculate itself "from the end in mind" perspective i.e. the 200,000 and then calculate backwards?

Not in Numbers. This type of process requires reiterative calculation in which previous results are used to adjust the starting data—as was done manually in my calculations—comparing the result in B14 to the target value, then adjust the starting value in C1 in the direction to achieve a higher or lower result. A closer result could be achieved, but would require more digits after the decimal in C1

Reiterive calculations require feference to at least one cell whose value depends on the value in the cell whose formula is asking for that value, a self-reference that Numbers does not permit.

LibreOffice's CalC mode does include a "Solver" which does perform this type of operation.

Regards,

Barry