• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculate Cumulative Percentage from a range upto a certain value and return corresponding value

R

rajkenya1

Member
Dear all


In the attached file, in cell h6 I am trying to put in a formula which gives me a result of cell d6 if the total percentage is equal to cell c2 and continues giving me a result until the total of cell c2 is reached.


In simple English. I have a total number of residents of 113 as shown in cell d23 and in coloumn E the % the residents make of the total. These residents turnover at the rate of 30% each year. The 30% is variable. What I am trying to achieve is that when the turnover is due the first of the 30% in the table will go and be replaced by new ones. The calculation should total upto the cumulative and then look at the corresponding number of that percentage in coloum d and return it upto the 30% only.


Is someone able to help.
 

Attachments

  • Cumulative Value Range.xlsx
    51.8 KB · Views: 3
Can You please confirm is this what you are looking for....
 

Attachments

  • Cumulative Value Range.xlsx
    51.4 KB · Views: 3
Hi Swapnil.

Thank you for your reply. Not quite. While it does give me a cumulative value upto the 30% it should give me the value from coloumn D which is the no of residents upto the 30%. Remember the 30% is variable so if i change it to 20% then it should give me results only upto the same. I have manually inserted the numbers is coloumn I. Is there a formulae that can do it. See uploaded file
 

Attachments

  • Cumulative Value Range v1.xlsx
    52.9 KB · Views: 7
I have made necessary changes , At the same time I suggest that your % column should contain data at least till 2 decimal point to reflect the correct picture.
 

Attachments

  • Cumulative Value Range.xlsx
    51.3 KB · Views: 2
Hi Swapnil


Thank you again for your help. I think we are nearly there with this and I thank you sincerely for your help.


However see the attached file again. If I change my variable to 40% then its actually returning numbers beyond that 40% (giving me 55.5% which equates to 63 people). It should give me actually only 40% equating to approximately 45 residents. So there is probably a formulae in between somewhere which rounds up. Hope you understand what I am saying. Sorry to bother you and thanks for your help again.


See attached file
 

Attachments

  • Cumulative Value Range v2.xlsx
    52.7 KB · Views: 1
Done. You may have a fraction of variation as Headcount I cant show in decimals.
 

Attachments

  • Cumulative Value Range.xlsx
    51.6 KB · Views: 5
Hi Swapnil

One last question. I am trying to use the same fornula for the bond value, but its giving me a higher than what it should be value. See coloumn I
 

Attachments

  • Cumulative Value Range v4.xlsx
    53.6 KB · Views: 4
=IF(SUM($F$4:F4)/$F$22>$C$2,"",IF(SUM($F$5:F5)/$F$22>$C$2,ROUND($C$2*$F$22,0)-SUM($H4:H$5),F5))

Replace the part marked in Bold with $F4:F$5
 
Hi Swapnil


I need some other help on the spreadsheet I am trying to build. In the attached spread sheet from column I to M i have the number of years from 2015 to 2020. The turnover ratio is different in each year. The expectancy is 3 years after turnover the same will turnover again.


So for example, in 2015 with a turnover of 25% a total of 28 will turn over. These will then turnover again after 3 years based on expectancy years. What I need is a formula that will pick these in the relevant year and also say in 2016 my turnover is again 30% but this time it will not pick the ones in 2015 as they have already been picked up so it will look at 30% below those that have been picked up and if there is any balance from the 2015 it will add these too. Is it possible to fomularise this.


If you have a number I can call you let me know if you need a better explanation.


Thanks, hope you can help. I have done some manual calculations in the spreadsheet to show you what I am trying to achieve.
 

Attachments

  • Cumulative Value Range v5.xlsx
    53.9 KB · Views: 2
Hi Rajenkya,

Sorry for interruption, the first part of question is clear, if it had been 4 years for expectancy, the next time data would have been repeated in Col M and N Respectively, but what do you want to add. can you put an example of this. Thanks.
 
Hi Faseeh

How are you. You are right so it expectancy was 4 then the data picked from 2015 would be in 2019 etc. In the attachment i have shown the data from 2015 to come in 2018 based on 3 years manually. Can it be formularised.
 
Please check ... This is based on 3 years expectancy .. please let me know If you want to keep that a variable as well
 

Attachments

  • Cumulative Value Range v5.xlsx
    54.2 KB · Views: 1
have made necessary changes to accommodate the expectancy changes .. so based on the the change in expectancy input it'll adjust the o/p.
 

Attachments

  • Cumulative Value Range v5.1.xlsx
    56.1 KB · Views: 11
You must log in or register to reply here.
Back
Top