Bhargav Parikh
New Member
File attached
Debtors Sales Ratio Using Countback Method
- Thread starter Bhargav Parikh
- Start date
NARAYANK991
Excel Ninja
Hi Bhargav ,
The link as you have given it is not valid ; a typical Google Docs link will be :
https://docs.google.com/file/d/0B0KMpuzr3MTVWVMwb3BtVWlKejQ/edit
where the link is displayed after you have given access to edit the file to anyone who has the link. For this to happen , after uploading the file to Google Docs , click on Share , and then give access to anyone who has the link. If you want , you can change the View only option to Edit.
Narayan
The link as you have given it is not valid ; a typical Google Docs link will be :
https://docs.google.com/file/d/0B0KMpuzr3MTVWVMwb3BtVWlKejQ/edit
where the link is displayed after you have given access to edit the file to anyone who has the link. For this to happen , after uploading the file to Google Docs , click on Share , and then give access to anyone who has the link. If you want , you can change the View only option to Edit.
Narayan
Bhargav Parikh
New Member
https://docs.google.com/spreadsheet/ccc?key=0Am1DC9Dj3uWedGR4X0txZGF5d3JhZkhTbXdnVVhTQWc
dear narayana,
the link has been changed to public one and one having the link can change the same.
dear narayana,
the link has been changed to public one and one having the link can change the same.
Bhargav Parikh
New Member
thanks faseeh for spending your valuable time.
The thing is i have calculated this for 1 month.
i want to formulae whereby i can stretch this formulae across columns( months).
The complicated things is that cumulative value needs to be started counting in descending order from the month in question for which the ratio is to be calculated.
The thing is i have calculated this for 1 month.
i want to formulae whereby i can stretch this formulae across columns( months).
The complicated things is that cumulative value needs to be started counting in descending order from the month in question for which the ratio is to be calculated.
NARAYANK991
Excel Ninja
Hi Bhargav ,
Four points :
1. I would have thought that since your data starts from January 2011 , the data itself would be structured so that data for Jan-2011 is in column C , and data for the following months stretches out to the right ; this would make it easy to define named ranges , which would be dynamic so as to include fresh data as and when it is entered.
2. The formulae would then anchor on column C , and thereafter go forward e.g. the formula in H19 is =SUM($C6:H6) ; what exactly does this indicate ? Mathematically , it is the sum of the previous 6 months Sales figures ; but what is its significance ? Shouldn't this actually anchor at V6 , so that you get the Cumulative Sales starting from Jan-2011 ?
3. You have used some formulae in column , whose significance is again not very clear ; what is the purpose of using the MATCH function in the formulae from C43 through C51 ?
4. You have used the following formula in C55 , and copied it downwards :
=(C43+((C30-OFFSET(B19,0,C43))/OFFSET(B6,0,(C43+1))))*30
If you want this to be copied across to the other columns , which of the above addresses should change , and which should not ? What is the significance of this formula ?
Instead of letting us try and figure out what you want done by looking at the formulae , can you explain what you want done , so that we can figure out the required formulae ?
Narayan
Four points :
1. I would have thought that since your data starts from January 2011 , the data itself would be structured so that data for Jan-2011 is in column C , and data for the following months stretches out to the right ; this would make it easy to define named ranges , which would be dynamic so as to include fresh data as and when it is entered.
2. The formulae would then anchor on column C , and thereafter go forward e.g. the formula in H19 is =SUM($C6:H6) ; what exactly does this indicate ? Mathematically , it is the sum of the previous 6 months Sales figures ; but what is its significance ? Shouldn't this actually anchor at V6 , so that you get the Cumulative Sales starting from Jan-2011 ?
3. You have used some formulae in column , whose significance is again not very clear ; what is the purpose of using the MATCH function in the formulae from C43 through C51 ?
4. You have used the following formula in C55 , and copied it downwards :
=(C43+((C30-OFFSET(B19,0,C43))/OFFSET(B6,0,(C43+1))))*30
If you want this to be copied across to the other columns , which of the above addresses should change , and which should not ? What is the significance of this formula ?
Instead of letting us try and figure out what you want done by looking at the formulae , can you explain what you want done , so that we can figure out the required formulae ?
Narayan