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
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
ASKER
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
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
OK, I think I understand it now. I need some work to do first. I'll try to look into it further tonight.
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 :-)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Regards
ASKER
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.
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.
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.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
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.