Moving Average Input Range Display
Mar 30, 2007
I am using the built in moving average function to calculate the moving average of a set of numbers. There are a few things that i would like to do.
First i would like to have the last result displayed in a single cell. Then next to that cell i would like to have a cell that would specify the period of the moving average. I would like to be able to change the period in that cell and have that change it in the actual function. And finally i would like to have the moving average in a chart that would also change its period once that is changed in the respective cell. I realize that this might need some VB coding which i am currently learning.
View 14 Replies
ADVERTISEMENT
Jan 9, 2013
I have a few questions here.
1) Can a userform procedure be place in the midst a larger macro, so that it can get input from the user before running the rest of the macro? ie If the user leave the input field blank, the macro will exit will a msgbox.
2) I have a range(A1:A3) that i want my userform to display. This range is dynamic. The user will require to input the corresponding country next to it. how do i code the this? I will need to use a listboxes and textboxes right?
for example: userform shows city on the left, user will need to input the country on the right.
A1 - California (user input on the right)
A2 - Shanghai (user input on the right)
A3 - Mumbai (user input on the right)
View 1 Replies
View Related
Mar 13, 2013
I have searched and trying to manipulate various formulas with no success...
I am entering an AverageIF formula into cell J15
I want to calculate the average of the folllowing ranges cells J5:J8,J10:J13
However, I want the cell J15 to display blank if cell J14=0
On reflection I am unsure an AverageIF formula is even correct.
View 5 Replies
View Related
Mar 4, 2008
i have a spreadsheet model that i have inherited that needs some rigour. currently, users are inputting the bare minimum and then running away with calculation errors all over the page. I want to be able to "force" them to fill in certain fields before allowing them to move on. Eventually, I will do this via a User Form but until then, I need something to help my lovely users along in the right direction.
So, if a user inputs a project name (say G15), they will have to also enter Project Type (I15) and Start Date (K15).
while I am here, I want to be able to have the Project Status show as Potential if the Start Date is in the future. I already have validation set to limit the user choices to "Current,Completed,Suspended,Potential,Cancelled". I am not sure how to get the validation list to be contingent on the start date.
and lastly, does anybody have a little piece of code that will make the Start Date show as the day that the Project Status was changed to Current? So, even if the Start Date was initially a few weeks away, if Status is changed to Current tomorrow, the Start Date should be overwritten with tomorrows date.
View 9 Replies
View Related
Dec 8, 2006
this User Defined Function (UDF) would operate on any specified data (ignoring blanks) over a range. Inputs to the UDF are range and period.
View 9 Replies
View Related
Aug 29, 2008
I have obtained a function (from this site at Exponential Moving Average) which is supposed to help calculate simple mathematical values but it's not working on spreadsheet. assist with taking a look at this as I have attached the spreadsheet?
View 6 Replies
View Related
Jul 7, 2009
I new for a forum I have a small problem I just stock and canīt see what to do. So i need to move data from 4 columns B,C,D,E to F,G,H,I depending on input in column J, like if in J2 i enter letted M (or any from column A) i need to move data from B12,C13,D13,E13 to F2,G2,H2,I2 and so on. I dont have experiens in programming in exel or using formulas.
View 14 Replies
View Related
Apr 12, 2008
In the table below I'm trying to work how trend is being produced but I can't figure it out.
I have found how to find the moving average =AVERAGE(C2:C5)
I've tried a few methods using the trend function but I really don't understand what I'm doing. how its being calculated by looking at the table?
View 9 Replies
View Related
Sep 1, 2008
I have a set of data in column A consisting of over 1200 numeric values. The problem is that there are some blank cells in this column:
Colums A data:120, blank, 135, blank, etc
I need to calaculate the average for the first 25 data and then claculate the average for the next 25 data set dropping the first 5 and adding new consecutive 5 data ignoring all the bank cells!, so if the first statemnet is : averagre (a1:a24), the second statmnet should read (a6:a:29) not counting the blank cells. I don't know what array formula can be used to do this job for me.
View 9 Replies
View Related
Jun 17, 2014
I am a production manager for a homebuilder and I am trying to create an Excel spreadsheet that will allow me to input a date in a column and have a second column dependant on the date and move as I change the date.
Since no work is done on weekends, I'd like the output column to skip weekends and move the input to the next available weekday. I have conditionally formatted the date to be a red fill as a visual cue but I'm not sure where to go from here.
View 7 Replies
View Related
Jul 8, 2013
I am trying to do a moving average and running into an issue with the count part of the formula.
Column C (C2 to C54) has the numbers I need to average.
Average is based on last 5 weeks.
My issue is that C23 is blank as we had no data for that week.
Currently the formula is counting 25 entries between C2:C27, there should be 26.
My average should be 31 but I am getting 33.
This is the formula I am using :
=AVERAGE(OFFSET(C2,COUNT($C$2:$C$54)-5,0,5,1))
I have tried using different Counta and ISnumber still not working
I would rather use a formulas then VB code.
View 5 Replies
View Related
Nov 25, 2006
I have done quite a bit of looking on Google and looked over the posts in this forum, however, I can't find an example Excel worksheet for a linear weighted moving average.
The data set I am applying this to has 180 data points and the linear weights should extend back over the last 30 points.
View 9 Replies
View Related
Apr 4, 2013
I have a worksheet that I will be allowing other to access so that I can get them to input certain data. I will be protecting the sheet to avoid having them inadvertantly overwrite any of my formulae. So, I have a matrix on the left for them to input data into with calculations to the right that they cannot change. Can I prevent them from moving cells within the input matrix
View 1 Replies
View Related
Mar 15, 2014
I would like to calculate the moving average for the first ten rows in a column where each new entry is added to the cell above the last entry.
For example
A5, 2
A6, 3
A7, 4
A8, 5
A9, 6
A10, 7
A11, 8
A12, 9
A13, 10
A14, 11
A15, 12
Therefore in the current list the average is AVERAGE(A5:A14)=6.5.
The next entry added to the list will be in cell A4 making the list look like this
A4, 1
A5, 2
A6, 3
A7, 4
A8, 5
A9, 6
A10, 7
A11, 8
A12, 9
A13, 10
A14, 11
A15, 12
The average of the first 10 cells is now AVERAGE(A4.A13) = 5.5
I would like to enter one formula in say cell A1 that will calculate the moving average in the first ten cells each time a new entry is added. Naturally as needed, new rows are added below cell A1. I hope my problem is clear.
as I have been unable to solve it myself. I have tried using offset but have been unable to resolve the issue.
View 2 Replies
View Related
Aug 15, 2007
I have a column of data that contains various blank cells where no data was measured. In the adjacent column I want to take the moving average of the last 4 data points including the most recent entry. My problem is i do not know how to handle blank cells where there was no data. I need it to average the last four in the column where data acutally exists. I am ok with using helper cells if needed and I am not worried about the first four results at this time.
Example data
A..................B
15
50
25
20................55
Blank............55
30................31.25
35................27.5
blank............27.5
blank............27.5
15................25
10................22.5
15................18.75
40................20
blank.............20
View 10 Replies
View Related
Dec 2, 2012
The following code works fine if the data is sorted from oldest to newest. I need for it to work when the data is sorted from newest to oldest. For this to work, I would need a change that works something like this: "total1 = total1 + close_(a, 1) * a" would be "total1 = total1 + close_(a, 1) * b" with "b" incremented in the opposite direction of "a". I code very infrequently & have tried a lot of different things, searched the Internet, etc. all to no avail.
For instance, with "close" data in column "I", the data sorted as follows works with the formula
"=WMA(OFFSET(I2,0,0,9,1) )" to correctly return "791.50", but the opposite sort returns the wrong number.
The correct calculation is: (807.19*9+798.38*8+793.81*7+793.06*6+776.28*5+769.48*4+773.2*3+789.01*2+793.87*1)/(9+8+7+6+5+4+3+2+1).
DateClose
11/12/2012793.87
11/13/2012789.01
11/14/2012773.2
11/15/2012769.48
11/16/2012776.28
11/19/2012793.06
11/20/2012793.81
11/21/2012798.38
11/23/2012807.18
Code:
Public Function WMA(close_)
total1 = 0
n = WorksheetFunction.Count(close_)
divisor = (n * (n + 1)) / 2
[Code] ..........
View 1 Replies
View Related
May 4, 2008
how do I perform calculations on the last x non-blank instances in a data range?
for example, let's say I have a spreadsheet of 5 baseball players' batting averages (rows are team game number played, columns are at bats and hits for each player). I want to see how each player has performed in their last 10 games played, but some players have not played in every game. If I just use the sum function for the last 10 cells, I won't get the correct information for any player that has missed one or more of the last 10 games.
View 8 Replies
View Related
May 19, 2008
Currently i have a spreadsheet with realtime data feeds from Bloomberg (or reuters). What i would like to do is:
a. Fill a vector/column of data values every minute until we have 30 observations i.e. from 9.00am till 9.30pm
b. This will then allow me to calculate a moving average of the last 30 (1 minute) observations.
c. At 9.31am, the 9.00am value drops out of the column and is replaced by the observation at 9.31am. This results in a constantly updating column of the last 30 minute observations and will allow me to have a realtime moving average.
View 2 Replies
View Related
Jul 5, 2006
I have a worksheet that has all weekday dates in column 1 and values in column 2. I want to create a 30-day moving average based on the last (non-zero) value in the column 2.
Since every month has a different amount of days, I want it to search the date that has the last value (since I don't get a chance to update it daily) and go back thirsty days from that date and give an average of all the column 2 values skipping and values that are null or zero.
View 13 Replies
View Related
May 1, 2014
I'm using the code below to create multiple sheets in a given workbook.
[Code] ......
The code works except for this line:
[Code] ......
What I'm trying to achieve, is to dispaly an 'Input Box' to allow the user to enter a date which will populate cell B3 in each of the created sheets. However, when I run this, although I can display the 'Input Box', when I enter the data, and click 'Ok', I receive a 'Run time' error highlighting this line as the cause.
View 4 Replies
View Related
Mar 30, 2009
I am looking for is VB code to firstly have a message box with an input box on (that bit i can do). The text and number put into the message box is then used to search the table for the information that relates to it.
The ideal would be for the code to find the data and then copy it either to the clipboard or to a section of the workbook.
The tables I am searching can be put into which ever format is easiest. Below is an example table;
A B C D E
1 A.1 Side Road Correctly Left
2 A.2 Hit in Rear Left
3 A.3 No known Police ref
4 A.4 Vandalism
View 9 Replies
View Related
Feb 24, 2010
I would like to have some kind of input mask on my input boxes, the input box is for currency IN GBP (Ģ)
The program itself obviously has no use for the Ģ sign, but I would like it to display in the input box as a mask.
Is this possible?
I should mention I am using:
Application.InputBox
View 9 Replies
View Related
Sep 17, 2012
On this chart the X-axis are dates and the y-axis are daily scores. The chart shows each day's data point and computes a trend line. How can I dynamically calculate and display the average score as a single value somewhere on the chart? I could calculate the average in a cell outside the chart object but would like it to be part of the chart. I do not want to show the average as a separate horizontal line within the chart grid.
View 2 Replies
View Related
Oct 4, 2009
Can anyone tell me if its possible to input some ones initials into a cell and when you hit enter, their full name is displayed instead of the initials:
ie input FB and the same cell displays Fred Bloggs?
View 6 Replies
View Related
Aug 16, 2012
I'm writing a macro that will automatically change the display in a column of cells.
The input in the cell would be a decimal value, (e.g. 1, 1.25, 1.5, 1.75, 2). As of now, I think the only input options are whole numbers and 1/4, 1/2, 3/4 fractions.
After inputting the decimal value, the cell should update to display the value as the following string format :
Input: 1.25
Displays: 1-1/4"
The purpose being to enable fast data entry while displaying in the desired format.
Here is where I know to begin for the automatic update:
Code:
Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
'
'
'Code
'
'
Application.EnableEvents = True
End Sub
View 6 Replies
View Related
Jun 19, 2008
I have a dynamic Named Ranges called ChartData containing 4 values from 4 cells on the row selected. I have a chart using this Named Ranges so that the value displayed are always referring to the current row. This works fine. I created a second Named Ranges ChartAvg which is =AVERAGE(ChartData) that I project to use in the Chart. However, I need to have 4 time the same value (average) if I want to display a line in the chart.
My problem is: I don't want (and can't) enter the Named Ranges ChartAvg in 4 cells to use in the chart. I wish to create a 3rd Named Ranges called ChartAvgData that would be used in the Chart. So now, to my question : How to create a Named Ranges that is a list of (4) values, somethine like: {ChartAvg,ChartAvg,ChartAvg,ChartAvg}
This is not working, whereas {1,2,3,4} (for example) works fine.
View 8 Replies
View Related
Jul 17, 2013
I am trying to display number of lines which depends on value pass by user. Actually user is passing some value. on which some group of lines has to be display.
eg
for 1 value 9 lines
for 2 value first 9 lines + another 9 lines
for 3 value first 18lines + 9 lines
and so on till 52.(this 9 lines are set of some column and rows)
thus cant use macros..
View 1 Replies
View Related
May 3, 2013
I have a pivot that includes customer data and I need to create an average for each row based on the first populated column for each customer. After one month of units appears for the customer, I need to calculate the average going forward, and if there are blanks after that they should be treated as 0 in the average. Below is an example of my description of the pivot.
Jan Feb Mar Apr
Customer #1 1 1 3
Customer #2 1 3
Customer #3 2 2
So, the average for customer #1 would be calculated from Column B to Column E, and factor column D as a 0 in that calculation. Customer 2's average would start in Column C and go to Column E, and factor column E as a 0 in that calculation. Customer 3's average would start in column D to column E.
View 5 Replies
View Related
Feb 25, 2010
Can i put into the cells B38-AF38, that would display the sum of B20:AF20 + B24:AF24 + B28:AF28 + B32:AF32 +B36:AF36 in a h:mm format.
When imputing the data into a cell in a h:mm format, I need it to display in that cell and formula bar the h:mm format without having to put an apostrophe in front of "h" or ":mm" value. Once complete, this form is going to go to other trainers to use on their computers and I dont think they would remember to keep putting an apostrophe before the data.
Example:
B20= 1:15 (1h15m) NOT 1:15:00 AM
B24= :30 (30min)
B28= :45 (45min)
B32= 1:45 (1h45m) NOT 1:45:00 AM
B38= 4:15 (4h15min)
I have attached a copy of the form for better clarification.
It doesnt seem to be complicated, but it's certainly a lot more than I thought.
View 4 Replies
View Related
Feb 11, 2013
I have a column in my pivot table with values that are formatted as percents. I am trying to make the grand total reflect the average of all values in the column, but it keeps showing a sum of all values.
Example: the values in the % column are 90%, 100%, and 110%. I want it to show 100% (the average), but it is showing 300% (the sum)
View 1 Replies
View Related
