We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Date percentage calculation and predicted value over time intervals

Anthony
Anthony asked
on
Medium Priority
419 Views
Last Modified: 2012-05-11
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
Comment
Watch Question

CERTIFIED EXPERT

Commented:
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.

Author

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
CERTIFIED EXPERT

Commented:
OK, I think I understand it now. I need some work to do first. I'll try to look into it further tonight.
CERTIFIED EXPERT

Commented:
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 :-)
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

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

Author

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.
CERTIFIED EXPERT

Commented:
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.
TracyVBA Developer
CERTIFIED EXPERT

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.