Calculate A Lot Of Averages Of Non-connected Values
Feb 20, 2008
Situation: I need to calculate a lot of averages of non-connected values. That is, they are separated by several rows (which also contain values). So far, I use the code-sample below (just to illustrate), which is not really elegant. Unfortunately, it seems that this technique comes to its limits when a high number of different non-connected values are combined into one string and get assigned to a formula- range (last line of code), which is where the program stops.
My question is: Is there a nice way to solve this? A way, which is not limited by the amount of data processed? The only way I could think of, would include a UDF, where a sum, sums all values up and then devides by the number of added sums ... but this would be relatively slow, and I have an awful lot of values to be averaged (100 sets * 6 rows * 200 values = 120.000 calculation- steps), as well as STDEV and a Standard Error of Mean (SEM) to calculate. Is there a better alternative?
Const intCells=9 'actually, this is no constant but a variable
Const i=4 'actually, this is no constant, but a nested for- loop-value
Const intLinesInBetween = 12 ' amount of lines, which separate my values
Dim j As Integer 'simple counter, runs through all cells
Dim rngUpperRow As Range 'Range of the upper row = normalized time vector
Dim rngCurrentRowAvg As Range 'current row into which avg formulas are inserted
Dim intCells As Integer ' amount of experiments in range............
I have 3 columns (B,D,F) that have 15 minute data intervals and I need to calculate the hourly averages for each column but here is the catch, I need to do this from July-1-2011 00:00:00 to Sept-31-2011, 24:00:00 (that's 26,217 data points and 6,554 =average() calculations......
IS there syntax I could use that would calculate the average every 4-cells so I could get the hourly average without having to type =average(XX:XX) 6,554 times?
I am wondering if there is a function in excel to calculate the average of multiple percentages. Currently I rely on the following formula to achieve this result and I am curious if there is a quicker way:
Try this formula. .....
Just today I learned there is a function to calculate the compounded return (FVSCHEDULE). It does part of what I am looking to do by taking away the PRODUCT function and the array, but does not really simplify things. Using FVSCHEDULE my function would look like ....
On another thread I found a solution to creating a weighted average given a pair of columns of numbers but I have column B consisting of values given as High, Medium and Low and the weighted column C of 1, 2 or 3.
How can I use the formula below to accomplish this, or is this maybe not the right way to created a weighted average?
The names are not static and can be moved, meaning that John can be 1, 2, 3, or 4 (all depending on how the name is chosen). I want to find the average on where John ranks. In this instance, he would be 2 [(1+3+2)/3].
My question is - How can I create a formula that will look for where John appears in the column (B1) and call the previous cell (A1)? This formula needs to be repeated on each column, added and averaged.
I have a first column, which contains random integers typically with gaps of between 10 to 50 blank cells. The data may have up to 5,000 rows in total.
In the next column along I would like a calculation which averages the integer in the first column out across all rows until another integer is reached in the first column.
E.g. In the first column, the first number is 60, then 9 blank cells till the next entry. In the second column the first 10 cells each contain the number 6 ( = 60/10).
I have a large sheet of daily river flows and precipitation amounts, over 40+ years.
I want to calculate the average flows in each month of each year (ie 11/1968, 12/1968, 1/1969 etc). Dates are in Col A, flows in Col D.
How to average a particular month over all years like so:
{=AVERAGE(IF(MONTH(A2:A15282)=1,D2:D15282))}
But I don't know how to average per month of each year, or how to make a formula that will allow me to quickly calculate the averages of 100s of months.
I am trying to calculate final grades with weighted averages. However for the final grade I need to drop the lowest of the test grade which come after the bold line to the right, making the total relative weight of Tests, the weight of 2 tests. How would I go about calculating that with a SumProduct formula? I do not need to drop grades from B4:K4, however from L4:N4 I need to drop the lowest grade for my final grade, which needs to be rounded to the nearest integer. The relative weight of each assignment is given in Row 1.
I need to do the following and can't figure it out. How do I create a running average that will only calculate the averages in % each month. Example: Opt 1 for Jan, Feb, Mar =1 each= 3 total = 100%; OPt 2 for Jan, Feb, Mar =1,0,1= 2 = 66%; Opt 3 for Jan, Feb, Mar = 0, 0, 1 = 1 total = 33%. My problems is I want monthly running average that shows the yearly percentage up to date but only for the months there is a value 1 or 0. How can this be done because the way I have it now, those % are being divided by 12 and that isn't the correct %
I have a workbook with several sheets , the 1st 31 being named Mar.01-07, Mar.02-07,etc I'd like to capture the averages(of cells with values) from cells B7:C24 of sheets Mar.01-07 thru Mar.31-07 and place the values in the sheet called "averages" with the average for B7 going to cell B7 in the "average " sheet, B8's average going to cell B8 in the "average" sheet and so on.
I have created an application for users that requires the user be connected to the network. I wanted to do a check to see if they are connected, and if the user is not connected they would get a message box telling them that they need to be connected to the network, then the workbook would close. Below is the code I have, but when a user is not connected they do not get my messagebox, instead they get the excel error message "excel run-time error '52': bad file name or number", and the debugger line that gets highlighted is in the function DirExists that checks the length of the directory of the file path: Len(Dir(strpath))
Option Explicit Function DirExists(strpath As String) As Boolean If Len(Dir(strpath)) = 0 Then 'this is where the debugger highlights DirExists = False Else DirExists = True End If End Function
Private Sub Workbook_Open() Dim strpath As String Dim strfile, strfile2 As String strpath = "\n530fs1PCLFileSharespcl_repositPricing_ToolsPAT" If Not DirExists(strpath) Then............................
I have an excel assignment that has about 4000 orders for wood. I am suposed to find the company that ordered the most wood and of what kind. I am really at a loss for how to do this and have combed over my book quite afew times trying to figure it out. However it has little if any excel info and nor does the ** that came with it outside of how to install data analysis tools. I figure I need to find a way to get excel to combine the numbers together for orders from the same company but after trying for afew hours I have gotten no where.
I have a workbook with several columns, some of which are for internal company use only.
Sometimes we need to send an updated list to business partners, but they don't need all columns.
Is it possible to make another workbook which takes only specific columns, and excludes the stray info at the bottom of the original workbook? See examples.
Alternatively: Would it be simpler to make an extra sheet in the original workbook, and somehow choose "Safe only this sheet to a new document" whenever someone requests the list?
I want a boolean function to return whether the PC is connected to the Internet. How to start creating this.
Reason:
I have code that checks an online database and downloads data after the user presses a button.If the host computer is not currently connected to the net, I would like to display a message of my choice instead of trying to run the code.To determine whether to run my message or not, I need a function that will return TRUE/FALSE if the host PC is net connected or not.
I am seeking some code that will check that the PC / Laptop has an online ethernet connection via it's NIC (Network interface card). I want it to output to the spread sheet to a Conditional cell (Connected / unconnected).
Is there any way to have a web query stay connected and get live data from a site while still being able to use excel at the same time?
Basically the site has information that changes by the second and i need to keep a running record of it, and when it hits the variables needed i want to be able to trigger a set of code.
I have a couple of points, and when i do a scatter plot with connected by smooth line plot, it seems like excel does a bezier splines interpolation. I am wondering am i able to extract the data plots from the data chart?
for example if i have
X - Y 0 - 5 180 - 0 360 - 5
I would like to have all datas of Y from X (0 - 360 degree every degree).
I now have XL 2007 but no longer work at the same place so do not have access to the external data source. I would like to view the SQL statements that I wrote back then but keep getting error that I am not connected to external data source and so cannot see my sql statements. I have MS Query 2007.
I have a question about using conditional formatting in excel (2010); I made a table with the following columns:
"Supplier Name" "Supplier Lead Time" "Internal Lead Time" "Total Lead Time". I made two different scenarios to show different supplier lead times and different internal lead times, and used the minimum function in the "Total Lead Time" column to find the smallest total lead time to select the best supplier.
The last thing I would want to do with this set of data, is plug in a formula that would somehow indicate which supplier corresponds to the shortest total lead time (which supplier has the smallest supplier lead time). I'd like to be able to use a formula that enters the name of the supplier in a designated cell, which I could indicate as the "Preferred Supplier" cell. If this is not possible perhaps there is a way to highlight the supplier's name with conditional formatting?
First I would like to say that I am not English nor very good to explain myself so hope the title is according to the forums rules. Now to my problem
I would like to to put in for instance the number 100 in cell A and then the number should appear in cell B. I would like to remove the number in cell A without the number in cell B to dissapear. Then add for instance 50 in cell A to get the number in cell B to add up to 150 and so on. How can I do this? I would like to add that cell B is already connected to a different cell. And I am using Vista.
I have a list where i enter invoice values lets say on column (A:A) and i want to calculate all the invoice values each time i enter the values on A:A for example; i entered $2500 on cell A1 and pressed enter, then i entered another figure on A1 let say $500 i want the total value to be shown such as $3000
What is the best way to check to be sure that a text box contains numeric values prior to performing calculations?
I have a form that has many textboxes which a user enters values into to perform calculations. I want to make sure that each necessary field contains a numeric value prior to performing the calcuations.
This is what I have so far but for some reason it keeps giving me the message box:
If WorksheetFunction.IsNumber(Me.specGrav_txt) = True And _ WorksheetFunction.IsNumber(Me.pAngle_box) = True And _ WorksheetFunction.IsNumber(Me.pLength_box) = True And _ WorksheetFunction.IsNumber(Me.pSize_box) = True And _ WorksheetFunction.IsNumber(Me.pQty_box) = True And _ pModTrap_btn.Value = True Then PrimMTcalc Else If WorksheetFunction.IsNumber(Me.specGrav_txt) = True And _ WorksheetFunction.IsNumber(Me.pAngle_box) = True And _ WorksheetFunction.IsNumber(Me.pLength_box) = True And _ WorksheetFunction.IsNumber(Me.pSize_box) = True And _ WorksheetFunction.IsNumber(Me.pQty_box) = True And _ pFullRnd_btn.Value = True Then PrimFRcalc Else MsgBox "There is missing data." & vbNewLine & _ "Please check to make sure that all runer data has been entered" & vbNewLine & _ "and the Specific Gravity field contains a numeric value."
I have been using Excel for a while but i'm kinda stuck at one point where i have to calculate the sum of the numbers corresponding to the month of the data.
Column 1 has all the dates in different months - in this workbook i've included only jan, feb and mar. The second column has the counts corresponding to the dates.
Now if a user inputs the month (1,2,3) number in F2, then we need to compute the sum of all the numbers from second column corresponding to the month in column 1 and display in cell F4.
In the chart I attached there are 20 columns and 7 rows. The calculations are as follows: K in first period - 8 (given). product per worker (y) - 2*K^(1/3) = 4. Total invesment - s*y - s (savings rate) is given as 0.1 and y was calculated in the previous line. s*y = 0.1*4 = 0.4
Depreciation on capital - deperciation rate is set at 2.5% (0.0025) so the depreciation is 0.0025*k = 0.0025*8 = 0.2. Net investment = Total investment (0.4) -Depreciation (0.2) = 0.2. Consumption per worker = product per worker (4) - Net investment (0.4) = 3.6
Growtn in product per worker - Can only start calculating from next period. It is (yt-yt-1)/yt-1. (Next year's product per worker minus this year's product per worker, the answer is divided by last year's product per worker).
log linear Regression. The thing is I do not know how to calculate elasticity of logarithmic values, slope, std. error in order to make forecasts. Is there a way solve it with Linest or any other formula. I am attaching a spreadsheet to better see.
