SOLVED

# Help with a formula

Brass Contributor

# Help with a formula

Dears,

could you please assist regarding the below as i want to consume the amount in jan (164) with a given %35 till consume the whole amount, then in feb calculate 35% from 76 plus 35% from jan. etc.

 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total Total Bounced 164 76 61 968 603 519 489 1,046 1,038 504 129 121 5,718 Collection of Bounced 5,718

5 Replies

# Re: Help with a formula

You'll get a quicker and more accurate answer, and those who want to help don't have to invest time guessing the problem.

I recommend: Describe your project step by step and Cell by Cell, with the appropriate description.

Always inform about your Excel version, operating system, storage medium/hard drive, OneDrive, Sharepoint, etc.).

Don't forget that not every Excel user has a clue about every job and what you see he can't see.

Welcome to your Excel discussion space!

Thank you for your understanding and patience

# Re: Help with a formula

is there any formula to provide me with the result as per the below sample.

N.B: divided the amount of bounced over months with 35% till consume the whole amount.

- Office 365

 0.35 F G H I Jan Feb Mar Apr Bounced 100 200 300 Result Collection =F7*0.35 =(F7*0.35)+(G7*0.35) =(1-0.7)*F7+(G7*0.35)+(H7*0.35)

# Re: Help with a formula

As far as I could tell from your text and the translation, you want to distribute the bounced amount over several months with a fixed percentage each month until the entire amount is consumed. You can use the following approach in Excel:

Assuming your bounced amount is in cell B1, and you want to distribute it over months starting from column C:

In cell C2 (Jan), you can enter the formula:

=IF(B\$1*(1-SUM(\$C2:C2)/B\$1)<=0,0,B\$1*(1-SUM(\$C2:C2)/B\$1)*0.35)

Drag this formula across the cells corresponding to Feb, Mar, Apr, and so on.

Explanation:

• SUM(\$C2:C2): This part of the formula calculates the cumulative sum of the amounts allocated in the previous months.
• B\$1*(1-SUM(\$C2:C2)/B\$1): This calculates the remaining amount to be distributed for the current month.
• IF(B\$1*(1-SUM(\$C2:C2)/B\$1)<=0,0,B\$1*(1-SUM(\$C2:C2)/B\$1)*0.35): This checks if there is any remaining amount to be distributed. If yes, it distributes 35% of that remaining amount; otherwise, it puts 0.

Copy this formula to the cells corresponding to Feb, Mar, Apr, and so on. The 35% will be calculated based on the remaining amount in each month.

My answers are voluntary and without guarantee!

# Re: Help with a formula

I think I did something wrong.

best response confirmed by Hussein_Mohamed (Brass Contributor)
Solution

# Re: Help with a formula

Please see in the insert file.

1 best response

Accepted Solutions
best response confirmed by Hussein_Mohamed (Brass Contributor)
Solution

# Re: Help with a formula

Please see in the insert file.