Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 378
  • Last Modified:

Date percentage calculation and predicted value over time intervals

Hi ALL
Hoping for some insight / assistance on an excel file we have that has become somewhat burdensome.  The data in the file relates primarily to inspection schedules and their current or overdue status, there are several overdue status values which relate to the authorization required to continue operating.  Each overdue status is based on a percentage of it inspection frequency (or time between inspections).
What I am trying to achieve is to predict the inspection status at six month intervals through the end of 2017.  I can do this manually through a change in the target date in the selection dates tab, but that means I have to re-calculate all the formulas for each target date and lose the values from the previous target date.
My goal is to compare the target date status to the proposed inspection date and predict the status of an equipment without have to make changes to the existing formulas, of course that may not be possible but any help or direction would be much appreciated.
I have attached the file for your review
Thanks and Best Regards
 Experts-Exchange--Predicted-Draf.xlsx
0
ajegan
Asked:
ajegan
  • 5
  • 3
1 Solution
 
Nico BontenbalCommented:
I'm afraid I understand only half of your question. You want you automatically fill the table on row 22 of sheet "management summary ..". This is done manually by entering the dates in column K one by one, and then copying the values from somewhere to column C:J. Right?
What I don't understand is where to get the values from. Can you explain in detail what the manual steps are you perform now. Then you (with our help) could create a macro that does this for you by the press of a button.
This macro will loop over the values in column K starting on row 23 downward until an empty cell is found. For each value found it will enter the date in the Selection dates sheet, recalculate the workbook and copy the calculates values from somewhere to columns C:I on the current row.
0
 
ajeganAuthor Commented:
Hi Nicobo
 Not quite, the manual selection date (Traget Date) is entered into the cell A2 in 'Selection Dates' Tab, then I refresh all in the Pivot table and the results are then manually copied (at this time) and another slection date is manually entered repeating the process for each six month interval.
I use the copied results from the Pivot table to enter the status into whast called an ABCD report.  ABCD being the inspection status in this case.
The final values I am counting (on the six month interval basis come from column AM on the 'Base Data' Tab.  This value is automatically calculated each time I refresh the data with a new target date in the selection dates TAB.
Hope that explains a little better.
Thanks & Regards
0
 
Nico BontenbalCommented:
OK, I think I understand it now. I need some work to do first. I'll try to look into it further tonight.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Nico BontenbalCommented:
Well I didn't have much luck yet. I started refreshing the pivottable and then it got all black. Looks like you renamed some of the columns in the 'Base Data' tab. I tried to recreate the pivot but I'm not sure if I got it correct. Maybe you can upload a new version of the spreadsheet that doesn't have this problem. In the mean while I'll get some sleep. It's getting quite dark already on this part of the globe :-)
0
 
Nico BontenbalCommented:
I think what you need is the Scenario functionality of Access. See:
http://www.dummies.com/how-to/content/how-to-evaluate-scenarios-with-excel-2010s-scenari.html
for an explanation. (Please take offence of the domain name :-)  )

I made a few changes to your sheet to make this work. First I changed the Summary and Selection sheets so the the date can be entered in cell K20 on the summary sheet. Then I putted formula's in C20:I20 to calculate the number of items directly form the Base data. No need to refresh the pivot anymore. Then I created scenario's using the What-if button on the Data ribbon. I only added the first 5, you need to add the rest yourself. I named the cells in range C20:K20 so the scenario manager knows what's what. Then I clicked the Summary button on the Scenario Manager and this created the "Scenario PivotTable" tab. I think this what you are looking for. Please not you can't refresh the Scenario PivotTable. You need to recreate it when the scenario's or data changes.
Experts-Exchange--Predicted-Draf.zip
0
 
ajeganAuthor Commented:
Thanks Nicobo, this certainly looks like it will work for us.  Let me play around with it and get back with you.  I do appreciate your help so far and hopefully this solves the issue.  BTW... Suitably offended ;)) and thanks for the link as I have never used the scenario Manager before..  cool

Regards
0
 
ajeganAuthor Commented:
OK to close, apologies to Nicobo for not getting back sooner but have been travelling.

Note:  I was not abale to achieve what I wanted with scenario manager as it is limited to the number of cells you can use and I was looking at ~ 35k values x twice that I need to change in order to predict futrure values.
0
 
Nico BontenbalCommented:
You can probably work around this limitation by putting all your scenario's on a separate sheet. Next you'll create a lookup function somewhere that has the row number is get the value from as a reference. This enables you to change all the 70k values of your scenario by only changing one cell.
0
 
TracyVBA DeveloperCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now