Help with a formula

[Hussein_Mohamed]

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

[#2]

@Hussein_Mohamed 

With your permission, I recommend to provide more information.

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.

In this link you will find some more information about it:

Welcome to your Excel discussion space!

Thank you for your understanding and patience

[#3]

@NikolinoDE 

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)

[#4]

@Hussein_Mohamed 

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.

Remember to adjust cell references based on your actual data layout.

My answers are voluntary and without guarantee!

Hope this will help you.

[#5]

@NikolinoDE 

I think I did something wrong.

[#6 · Accepted solution]

@Hussein_Mohamed 

Please see in the insert file.

Hope this will help you.