• 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 average of values in corresponding cells in a second tab

K

krepitch

New Member
Hi, all. This is a complicated situation that I've never created before, so I'm hoping I can get some advice here. It would certainly be appreciated!


On Tab A, I have a list of projects running vertically in Column C and a list of dates running horizontally across Row 5. The data in the cells tell me the number of days that have passed since the project was initiated. So, for example, cell EI19 is populated with "15" - the number of days that have passed since the project in Row 19 was begun. For another example, Cell EO46 is also populated with "15" - because the project in this row began at a later date than the project in Row 19.


On Tab B, I have the same row and column setup, but the data tell me the how much of the project has been completed. To use the same projects from above, in cell EI19, the data show that 4% of the project has been completed. EO46 says that 0% of that project has been completed.


So, now for the hard part. What I am trying to do is come up with a forecasting model that will tell me "On day number x, the average project will by y% complete."


Is there a way to tell Excel to find every instance of any number (of days passed) that appears in Tab A, then calculate the average of the corresponding cells in Tab B? For example, if I was to calculate the average percentage completion on Day 15 for the data above, it would look for every "15" in Tab A, see that that number appears in EI19 and EO46, then go to Tab B, and calculate the averages of the values in Tab B in those same cells (4% and 0% here).


I hope this makes sense. If I can clarify at all, please let me know. Thanks for your help! :)
 
Hi, kreptich!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


A couple of questions:

a) how many projects approximately?

b) the dates on row 5, how many columns? do the have a pattern (every day, each week, ...)?

c) what do you mean by average project? average of completion or weighted average depending on project length in days?

d) the day entered as a parameter for which is going to display the forecast, is to be counted from scheduled project start or from real project start?

e) would you please upload a sample file? with just a few rows and cols... refer to second green sticky post for uploading guidelines


Regards!
 
Hi ,


To add to what has already been posted , I am not able to understand why you have dates in the columns running up to EI and EO and beyond. Surely , if the project start date is mentioned , then the number of days since the project began can be calculated ? Unless you mean that even a project which is a year old , can have days passed as any value less than 365 ?


A second point is not really related to your question , but more a technicality ; suppose a project , which is expected to take 600 days is 2 % complete , while another 30-day project is 50 % complete ; what picture is the numeric average of 2 and 50 going to present ? Do you have a field which mentions the expected duration of each project ?


Narayan
 
Hi krepitch,


I think the more important part of the question is that what type of statistical tool you would like to use for forecasting because there are variety of methods (you can search for it on Google) but every method has some error associated with it.


So the question is of accuracy and what model/method best fits your Project Patterns. If they have any trends then you would have more choice, if not you will need to handle the odds/out of trend occurrence in your data. So the question will remain about accuracy when forecasting.


I suggest you first decide the forecasting model you want to follow and then revert to Excel Sheets.


Regards,

Faseeh
 
Hi, everyone. Thank you all for your fast responses! I will try to address them in this post.


SirJB7, I searched through the forums and found some ideas that I am still working with (particularly the Match and Index functions), but (obviously) am not very good at articulating my issue, so my search was limited.


However, I have posted a sample file to Dropbox at http://db.tt/DGHMVuX6. I am using Row 11 as an example. You can see on Tab A that the project was approved on 1/17, so the data on that sheet tells me that Day 1 (ie, one day after the project was approved) was 1/18. Because approval dates vary, Day 1 can be different for each project.


Tab B is set up to show me when funds were actually spent on each project. Looking at Row 11 again, I know that the first expenditure took place on 2/16. The table on Tab B then tells me that 34% of the estimated spending was completed on/by 2/16. As time passes, that percentage should grow to 100%.


Tab C is really what I am trying to get to. I really just need to compare each project by days passed since it was approved, not a calendar date (normalizing, you could call it). Thus, back to Row 11 again, you can see that on Day 1, 0% of the budget was spent, but that number hits 34% on Day 25. I just manually linked those cells, but obviously my goal is to find an elegant way to do this automatically. If I can fill in this table, I can then take an average percentage each day across all projects.


To answer your specific questions:


a. There will probably be about a thousand projects or so, but I may break the data down into smaller categories. Thus, I'm not sure whether formulas or VBA code would be best here.

b. There really is no pattern to the dates. I will probably go back and make sure that every calendar date appears, but the raw data I have right now is not very clean. I'm just trying to solve the formula issue right now while someone else cleans up the data source.

c. I phrased that poorly. By "average project" I meant "typical project." Sorry about that.

d. The parameter will be the approval date of the project. I work in a Finance role, so I will be referring to the points at which I authorize the projects because I don't have much control over when they actually begin (and those data are even messier).

e. I hope the sample file is helpful. :)


Narayank991, part of that is just that I have some unwieldy data. Once the data are cleaned, I will narrow this down to a more reasonable range. As to the second question, I, unfortunately, at least at this point, do not have projected completion dates. Most of our projects are of similar duration, though. At this point, I am comfortable using the project approval date as the basis for comparison for all projects, but you do raise an excellent point.


Faseeh, I agree that using an appropriate tool is important. What I am working on now is probably just a first step. At this point, I'm just hoping to have a simple curve that will show me, on average, how much (as a percentage) spending will likely be completed after a given number of days after approval of a project. I imagine it will be S-shaped, staying at zero for a while, then a period of fairly rapid spending, then a leveling off as the project is completed.


Thank you all so much for your support. This is really an amazing site...I'm off to look more into Match, Index, Offset, etc. I think that might be a better avenue than what I was thinking yesterday, but I'm open to all suggestions. :)
 
Hi, kreptich!


Thanks for your detailed explanation. Let me see if I got it right.


I downloaded your file but it refers to a file 'T:FP&AFinancial Planning and AnalysisDan-SusanaCBSCAPITALCapital ApprovalCapital ForecastingCapital Forecasting Model.xlsm', so I got nothing in sheet B.


So I worked with L11:AE11 (20 cells), both in sheet A (as original) and sheet B (replaced formulas by values entered manually), I renamed your sheet C as 'C Old' and duplicated sheet B as C (for formatting purposes only).


Give a look at this re-uploaded file and check if it works for you:

https://dl.dropbox.com/u/60558749/Calculate%20average%20of%20values%20in%20corresponding%20cells%20in%20a%20second%20tab%20-%20Forecasting%20Sample%20for%20Dropbox%20%28for%20krepitch%20at%20chandoo.org%29.xlsx


Take care of this:

a) you should only consider cells L11:AE11 which contains the formula used

b) there are 5 named dynamic ranges defined so as to make addresses easier for the formulas

- DateListA: sheet A, range L5:<EndOfData_right>

- ProjectListA: sheet A, range C6:<EndOfData_down>

- ProejctDataA: sheet A, range L5:<EndOfData_down&right>

- ProjectListB: sheet B, range C6:<EndOfData_down>

- ProejctDataB: sheet B, range L5:<EndOfData_down&right>


BTW, I agree with NARAYANK991 that having a field that reflects the expected duration of each progress would be strongly recommendable.


Just advise if any issue.


Regards!
 
Thank you, SirJB7. I have downloaded your file and will review it. It might take me a while, but I wanted to let you know that I am working on it. :)
 
Hi, krepitch!

Thank you very much for updating tracking information. Hope you succeed.

Regards!
 
SirJB7,


Just wanted to check in to thank you for your help. Your formula works beautifully and is quite elegant.


I broke it down step by step and learned a lot in doing so. Thank you again. :)
 
Hi, krepitch!

Glad you solved it. Thank you very much for your feedback and for your kind words too. Welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top