# Excel formula help

#### lusov

##### New Member
Hi,

I have a list of 70 clients with contract amounts and start dates on one tab. I need to make projection for 12 months in a way that for each customer no more than 15,000 monthly revenues is included in the forecast. For instance if the contract amount is 42,000 USD then I should account for 15,000 first month, 15,000 second and 12, 000 last month. Can you, please, help me with the formula?

#### yky

##### Well-known Member
Is something like this table what you are after? If so, enter the following formula in B1,

Code:
``=IF(A1>15000,15000,A1)``
and this one in C1,

Code:
``=IF(\$A1-SUM(\$B1:B1)>15000,15000,\$A1-SUM(\$B1:B1))``
Then, copy C1 and paste it down the column.

Now, copy B1 to the end of the column and paste down the row.

 98765 15000 15000 15000 15000 15000 15000 8765 0 32104 15000 15000 2104 0 0 0 0 0 66592 15000 15000 15000 15000 6592 0 0 0 15000 15000 0 0 0 0 0 0 0 15001 15000 1 0 0 0 0 0 0 14999 14999 0 0 0 0 0 0 0

<tbody>
</tbody>

#### lusov

##### New Member
Is something like this table what you are after? If so, enter the following formula in B1,

Code:
``=IF(A1>15000,15000,A1)``
and this one in C1,

Code:
``=IF(\$A1-SUM(\$B1:B1)>15000,15000,\$A1-SUM(\$B1:B1))``
Then, copy C1 and paste it down the column.

Now, copy B1 to the end of the column and paste down the row.

 98765 15000 15000 15000 15000 15000 15000 8765 0 32104 15000 15000 2104 0 0 0 0 0 66592 15000 15000 15000 15000 6592 0 0 0 15000 15000 0 0 0 0 0 0 0 15001 15000 1 0 0 0 0 0 0 14999 14999 0 0 0 0 0 0 0

<tbody>
</tbody>

Hi Thanks!

I have 70 clients and would prefer not having a table. Is it possible to do this through one formula?

#### yky

##### Well-known Member
Each row is a client.

1,082,335
Messages
5,364,686
Members
400,810
Latest member
elbashka

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...