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

View 4 Replies


ADVERTISEMENT

Calculate Large Strings Of Averages

Oct 6, 2011

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?

Example of set-up:

29-Sep-11
23:00:00
0.820412164= AVERAGE(B8735:B8738)
0.762574274 =average(D8735:D8738)
84.98389991 =average(F8735:F8738)
29-Sep-11 23:15:00 0.819168591
0.762781167

[Code]...

View 6 Replies View Related

Function To Calculate Averages Of Percentages

Jan 29, 2010

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

View 9 Replies View Related

Calculate Weighted Averages Using High, Medium, Low

Dec 31, 2008

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?

=SUMPRODUCT(B2:B11,C2:C11)

View 7 Replies View Related

Calculate Averages Based On Where Names Are Positioned

Aug 21, 2009

Code:.....

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.

View 5 Replies View Related

Calculate Averages The Integer In First Column Out Across All Rows

Dec 22, 2011

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).

View 1 Replies View Related

Calculate Averages Of Individual Months Over 40 Years

Jan 5, 2012

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.

View 6 Replies View Related

Calculate Final Grades With Weighted Averages?

Mar 19, 2013

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.

View 3 Replies View Related

How Do I Create A Running Average That Will Only Calculate The Averages In % Each Month

Mar 20, 2008

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 %

View 9 Replies View Related

Capture The Averages (of Cells With Values)

Feb 15, 2007

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.

View 9 Replies View Related

Check If Connected To Network

Jan 11, 2008

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

View 2 Replies View Related

Sum Up Numbers Connected To Repeating Names?

Nov 3, 2008

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.

View 3 Replies View Related

Selective Columns In Connected Workbooks

Mar 20, 2014

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?

View 1 Replies View Related

Macros Connected To Hidden Sheet

Feb 14, 2014

I have a macro which copy text from a sheet (sheet-1) and paste it into another sheet.

I want sheet-1 to be hidden, or even better, very-hidden, but when I hide sheet-1 the macro run fails. Respons Run-time error '1004'...

Is it a solution around this 'problem' or does sheet-1 always has to be visible ?

View 3 Replies View Related

Boolean Function To Return Whether PC Is Connected To Internet?

Jun 6, 2013

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.

View 11 Replies View Related

Slow Workbook When Connected To Local Network

Apr 26, 2009

I have Excel 2003 SP3 on Vista Home Basic fully patched. My code runs very slowly until I pull out the ethernet cable then it runs quickly.

View 9 Replies View Related

Online Status - Conditional Cell (Connected / Unconnected)

Mar 4, 2012

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).

View 2 Replies View Related

Web Query Stay Connected And Get Live Data From A Site?

May 7, 2009

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.

View 9 Replies View Related

Extract Scatter Plot With Connected By Smooth Lines Points

Jul 25, 2012

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).

View 9 Replies View Related

Excel 2007 :: Viewing SQL Statements Without Being Connected External Date Source

Jun 3, 2012

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.

View 1 Replies View Related

Calculate Values Cumulatively Over And Above Previous Values

Apr 17, 2007

REFER TO MY ATTACHMENT.

Can I get a simple ONE LINE formula to calculate the below combination

Basic Cost = 100 (A) (my Material cost)

ADD : 10% for Freight (B)

A+B = C

ADD : 10 % for Overheads on 110 (On C) = D

C+D = E

ADD : 15% towards Margin on 121 (on E) = F

E+F = G

WHERE G = 139.15

I require this because my %s varies according to products.

View 6 Replies View Related

Excel 2010 :: Formula To Indicate Alphabetical Data Connected To Numeric Data

Jan 8, 2014

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?

View 2 Replies View Related

How To Make Macro To Lock Certain Cells Connected To Another Cells Value Then Copy Daily

Mar 29, 2014

am making Excel for private use, i need the following makro to be active, here is the idea because i didnt yet work with makros at all:

If A1 = False
then Range B1:D1 will be locked cells

If A1 = True
then Range B1:D1 will be unlocked cells

Those rules apply to 1 day in the year.

the range A1:D1 will be copied about 400 so the makro should be active to each day separatly.

Check the picture attached for example of one day

View 1 Replies View Related

Increasing A Value In A Cell By Adding A New Number To A Connected Cell?

Mar 28, 2009

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.

View 5 Replies View Related

AUTOMATIC CALCULATE VALUES

Feb 14, 2007

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

View 9 Replies View Related

Calculate Textbox Values

Nov 25, 2007

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."

End If
End If

View 9 Replies View Related

Calculate Sum Of Values Based On Month

May 21, 2014

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.

View 3 Replies View Related

Calculate Values Based On Category?

Jan 30, 2014

I have 2 tables:

IDTypeValue
1A3
2A3
3A4
4B4
5B3
6C2
7A4
8A3


And

TypeTotal
A
B
C

I need to "total" column to automatically display the sum of the values for the categories A, B and C. Is there a formula I can use for this?

View 2 Replies View Related

Calculate According To The Values Of Adjacent Boxes

Nov 15, 2009

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).

View 3 Replies View Related

Calculate Elasticity Of Logarithmic Values?

Feb 4, 2014

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.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved