So I am trying to make a macro that will update monthly forecast data to what the actual production/consumption data was. The production/consumption numbers come in separate workbooks (i.e. "Jan14", "Feb14") and need to be linked to the 'Book' main file. The path needs to the Jan14 needs to come into the 'Book' file, not just the values. I would like to be able to just have a button above each month that will just automatically load that month's data when it becomes available.
i have a dynamic list of numbers....currently 10 numbers in the list.
how can i calculate the variance?
i have the upper limit (=MIN(1,(mean+half width)) i have the lower limit (=MAX(0,mean-half width) i have the mean (avg of all numbers) i have the t value (TINV(alpha, (n-1))) i have the half width (t value * SQRT of Var/N)
I am looking to calculate variance across a large data set and would like to know if a macro is possible to calculate for a specific unique cell ID. East, Central, or West and calculate variance across that region.
For instance, in my data set if I have something similar to below. How would I calculate variance in the different regions? Is it possible to automate this process? Also could the Analysis ToolPAk be used instead or in conjunction?
I have a 52 week rolling forecast that I would like to have displayed in each calendar month that it corresponds to. I have come up with a solution where it does lump the data together into months but it is not a smooth lumping of the weeks as some weeks cross over from one month to the next. Is there a way to lump each week into its respective month. My current solution places in some months 4 weeks worth of data and in other months 5 weeks worth of data. Attached is the spreadsheet that I am using. The tab "Weekly Sales" is the 52 week data which has specified the exact dates on the calendar that that week represents. The "Monthly Sales" tab has the 12 week data which has specified the exact dates on the calendar that that month represents. I've tried SUMPRODUCT but that is giving me 4 weeks in some months and 5 weeks in others.
See xls file. I have this chart and the data labels are percentages instead of the actual value. How did these get that way? Under Chart Options there is a autotext box that is there but if I uncheck it, the data labels go back to the actual dollar value and that autotext box goes away.
I am totally baffeled by this despite serach the help, the internet and this forum. I am sure it is something easy I am missing.
Can some one provide some color on this?
I cut this from a larger book with many tabs because I am not able to send out the entire thing (proprietary).
I am working on a spreadsheet for my farm. I have two sets of data I am working with. A very basic example is below:
What I am trying to do is find the actual median stud fee (i.e. if you had another column with $5000 listed 22 times, $6500 listed 34 times, etc. then you could find the median from there). Seems like an easy thing, but I'm not sure if an array formula is what I need to use or something simpler. I don't have a lot of data right now (only a page) but will have much more soon.
Trying to create a basic pivot table from a CSV file for our users. What I've done in the past is open my CSV and then take the option Insert > Pivot Table to create the pivot table from the open CSV/worksheet. My question here is .... how can I save this .XLS and refresh the CSV data within? Or do I need to use an external data source? I don't want to have to use any drivers. I'd love to be able to just point to a CSV or TXT file on our server and that be that.
For something else I have used Web Query strings to point to .php scripts that send back data in XML format. Is this an option?
Again, the goal is for me to create a pivot table and just have our user's open it up and have current/refreshed data within. We can run jobs overnight or on-demand that update the data source.
Column A has a time (no date) Column B VLooks-up a value from a separate sheet per country, so it pulls through a the variance [-11 to +13] from UTC (GMT) time dependent on country. All other data is irrelevant. Let's say Column C has following formula: A2+B2/24.
This works where the time result (new time) is on the same day, but as soon as it crosses over midnight, it buggers up.
What I'm needing to do is take a list of events (server time/GMT) and convert them to the local time from where the event was triggered based on source country.
I am trying to get my variance percentage to calculate correctly but I am struggling when it comes to one of the value being zero. It works fine for the below example when both the variance and last year figures a zero.
I'm trying to calculate the variance between planned date & time of arrival vs actual date & time of arrival.
I attach the workbook as am a bit useless at explaining myself....
What I've done is in H14 subtract the actual date of arrival (F14) from planned date of arrival (C14). This result is the only way I could think of dealing with crossing over midnight. As a result I14 should subtract the actual time of arrival (E14) from planned time of arrival (B14):
This method works well when the arrival was later than expected but doesn't work if the arrival was sooner than expected.
finding a way to compare the two budgets i.e 08/09 and 09/10 and if a new cost centre appears in 09/10 it will bring a yes in column G(New cc) and if the Cost Centre already exist in 08/09 it will bring a blank--but in both cases i want a variance in the next column H i.e 09/10 less 08/09.
I have the word "variance" appearing in some of the rows in column B and a value in column C which is in the same row as the word "variance"
for eg row B19 Variance C19 10
I need VBA code that where the value which is in line with the text "Variance" is greater than 5, the name of the worksheet is listed in the worksheet named "Variances"
See Example below
I need a formula to workout what is the most up to date firmware that should be installed on the machine. This will always be in column B. The second formula should work out the variance between the installed firmware and recomended. The formula cells will be normally held on a different summary sheet.
I am trying to do my homework for college and the below excel grid was given to us to complete. I do not understand where to get the information it is asking. the first grid is the numbers we are suppose to use to input in the other grids. We are suppose to put a formula in on the last to two columns on each grid but I do not even know where to start.
Using the 24hr time format in cell a1 i have a start time of 10:43 and in cell b1 i have an estimated time i think a job should take in this case 30 minutes and in cell c1 i have the actual time that job was finished in this case 11:07 and in cell d1 i have a variance between the two times which in this case would be saving me 6 minutes
I have a list of african countries and their C02 emissions from 1990 to 2010. The question I'm asked is, who will be the top 5 emitters in the year 2020 given the current trends. I have done a lookup command and compiled a list of the top 5 emitters. My concern is though i do not know how to get the 2020 forecast of the top 5 emitters rather than the current datas.
I am using the Forecast formula to give me a value from a Linear trend. However I need to limit the resulting value that is displayed within a min and max value. Background: This is to allow me to calculate the amount of bonus someone will earn depending on the Percentages of their target they achieve. The min bonus is 25% when 80% of target is achieved and max bonus is 100% when 125% or greater of target is achieved. So the Forecast formular works great using the existing values, but i need help limiting the results to between 25% and 100%. I was trying to use a sumif formula to copy the resulting cell but only if the value fell between the 25 and 100%. I've attached the spreadsheet as I think it will highlight what i'm trying to do better than I can explain it.
I try to predict some macro economic statistics but any attempt till now didn't make sense. the attached file. Note: when i used the FORECAST function the predicted values showed an unlogical drop while there seems to be a positive trend.
I would like to know a way to sum the future months dollars only once the month has passed to consider that amount only in my forecast. For eg. If I have a Vendor A contract from Jan - April for $1000/per month in total for $4000. My Forecast should only be Feb-April = $3000. So my total column should only display $3000. Once Feb has passed , the forecast should only be March-April i.e $2000. How to get rolling month sum of forecast once month has passed.
Attached is a sample spreadsheet with different vendors.