Excel formula help

lusov

New Member
Joined
May 17, 2017
Messages
5
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
Joined
Jun 7, 2011
Messages
1,477
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.

9876515000150001500015000150001500087650
321041500015000210400000
66592150001500015000150006592000
15000150000000000
15001150001000000
14999149990000000

<tbody>
</tbody>
 

lusov

New Member
Joined
May 17, 2017
Messages
5
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.

9876515000150001500015000150001500087650
321041500015000210400000
66592150001500015000150006592000
15000150000000000
15001150001000000
14999149990000000

<tbody>
</tbody>

Hi Thanks!

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

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top