Forum Discussion
Help with a formula
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
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) 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.
