Hello all,

I'd like to calculate the average and mean of a range that starts at AR15 all the way down untill there is an empty cell. In between there can be cells that read #VALUE!. I want to skip them, because it results in #VALUE!.

I did the following: Code:

I'd like to calculate the average and mean of a range that starts at AR15 all the way down untill there is an empty cell. In between there can be cells that read #VALUE!. I want to skip them, because it results in #VALUE!.

I did the following: Code:

, but this results in #DIV/0! and only counts untill AR319 whereas I'd like it to count anything down untill hitting the first empty cell.

Can anyone help me out? Thanks a lot in advance!

Kind regards,

TwoAce

- Calculating Mean and Variance for Bivariate Prob. Dist.
- Conditional values when using text and numbers...
- Average and stdev from groups of data
- Macro Help to pick value from one sheet and put in new sheet automatically
- Calculating the Median, Mean, and GeoMean of dynamic range
- UDF Returning #VALUE! when Calculation Paused
- Calculate percentage and add or subtract result based on other cell value
- Calculate mean value in one workbook and copy/paste to another workbook
- Problem to calculate differences in cell values when columns change.
- Calculate Max and Min Value for only Visible Cells
- Match value to sheet name and return another v
- XL2000: Syntax Errors When Calculating Functions or Opening a Workbook
- VBA output mean and sd required
- Skip cells with #N/A N/A
- Count Intervals of 2 Numeric values in same Row and Return Count across Row
- Macro to to get values for specific date and copy to cells
- Count Intervals of 2 Consecutive Values in same Row and Return Count across Row
- Count Intervals of 2 Consecutive Values in same Row and Return Count across Row
- Macro to to get values for specific date and copy to cells
- How to keep excel from changing the cell formula values when copying and pasting.
- Simulate values for a given Mean and Standard deviation
- Calculating peak and off-peak hours while filling out employee's schedules
- Count Intervals of 2 Numeric values in same Row and Return Count across Row
- Selecting patient data based on multiple variables to calculate mean and SD

I trying to find (or alternatively write) functions that calculate mean and variance for bivariate probability distributions.

For example:

Skis

Boots 0 1 2

0 0.08 0.14 0.12

1 0.09 0.17 0.13

2 0.05 0.18 0.04

I wrote my own function for a single variable probability distribution, but this is more complicated since you need to sum rows or columns depending on the variable you are calculating for.

If I have to write this, I guess my first question would be how can I sum up either a row or column from a range and store the solution within an array?

i.e For column(i)

Sum all of the row values

Store in array(i)

Next column

Any suggestions on how to do this, where I can find something that already exists, or how to best approach this in general? The help of this forum is, as always, greatly apreciated.

-Zack

For instance, if I select a product from a drop menu and enter the dollar amount of the product, I want a commission amount to appear in another cell based on the product and amount.

Can this be done?

I have data such below for a year,

How do i calculate average and stdev from sample1 to sample4 on each date?.

Is there any functions or formulas to automatically calculated it ?

Ex: what is average and stdev from date 3/1/06 ?

I have tried to calculate by subtotals function, but it only can calculate

by colums.

Date sample1 sample2 sample3 sample 4

1/1/06 2 3 1 2

1/1/06 2 1 3 2

1/1/06 3 0 2 3

2/1/06 1 2 1 0

3/1/06 2 1 3 2

3/1/06 0 2 1 3

Thanks,

Charlie

Sub Add_sheet()

Dim wSht As Worksheet

Dim shtname As String

Dim myvalue As Double

Dim mypercent As Double

shtname = InputBox("enter the name")

For Each wSht In Worksheets

If wSht.Name = shtname Then

MsgBox "Sheet already Exists"

Exit Sub

End If

Next wSht

Sheets.Add.Name = shtname

Sheets(shtname).Move after:=Sheets(Sheets.Count)

Sheets("SCHED2_3").Range("A1:K21").Copy _

Sheets(shtname).Range("A1")

myvalue = Worksheets("SCHED2_3").Range("D8").Value

mypercent = InputBox("Please enter percentage")

If mypercent = 0 Then Exit Sub

ActiveSheet.Range("D8").Value = myvalue * mypercent / 100

End Sub

The problem with this is that for some workbooks I want to copy the data to 10 sheets and for others more than 10.

I was thinking if there is any way that instead of asking the user for the percent value dynamically, we can index all the values on a single sheet and then write a macro which can automatically copy, create and calculate the formula for all the values.

Can we put a loop here?

e.g.

Sheet 1

Name % owed

ABC 89

XYZ 77.77

PQR 65

YYY 45

TTT 34

It should pick the value(89),calculate it and then paste the new calculated value in the copied sheet. It should keep on doing so till it reaches end.

Ex.

Column B Column G Column H 2 10 5 2 13 9 2 9 2 3 8 2 3 7 3 For Column B = 2

I need to Calculate for the range "G2:H4"

I need the Median, Mean, and Geo Mean values to paste in Columns N-P for each different Station Index. My code only calculates for the first Station ID

Here is the code for what I have so far

VB:Thanks in advance!!Median() Dim r As Long Dim stndx As String Dim i As Long Dim x As Integer Application.Calculation = xlCalculationManual r = Cells(Rows.Count, "A").End(xlUp).Row x = 2 stndx = Cells(r, 2).Value[ATTACH]47030[/ATTACH] For i = r To 8 Step -1 If Cells(i, 2).Value stndx Then Cells(x + 1, 11).Value = Cells(i, 1).Value Cells(x + 1, 12).Value = Cells(i, 2).Value Cells(x + 1, 13).Value = Cells(i, 4).Value Cells(x + 1, 14).Value = Application.Median(Range("G2:H" & i)) Cells(x + 1, 15).Value = Application.Average(Range("G2:H" & i)) End If Next i Application.Calculation = xlCalculationAutomatic End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Cameron

I have written a simple UDF that returns a random number based on another cell value. The problem I am having is when the user pauses the calculation of the UDF(s) or flips between spreasheets (and calculates on the other spreadhseet) - then the UDF returns a #VALUE! result. I presume this is because the code has not finished.

Is there a way I can bypass this (or force a calculation if the result is an error value?)

Many thanks

Aaron

i m new here and new to excel aswell. i m getting some problem in the formulation of a excel spreadsheet (please pardon my english).

i have a value in cell A1, a value in cell B1, now i want a 17% value in cell C1 and then in D1 i need excel to do a little calculation, i.e.

when i enter a value in cell A1, then the cell C1 should show the 17% of that value and then D1 should add both the cell A1 and C1.

else when i enter a value in cell B1, then the cell C1 should show the 17% of that value and then D1 should subtract C1 from B1

thanks

VB:many thanksSub ImportData() Dim wbkTemp As Workbook Dim wbktest As Workbook Dim prdq Set wbktest = Workbooks("monthly.xls") Set wbkTemp = Workbooks.Open(Filename:=Range("C2")) ' "C2" is a cell that contains the hourly.xls filename including directory and path prdq = Application.Average(Range("T7:T30")) MsgBox prdq Windows("macrotest.xls").Activate Range("B2").Select ActiveSheet.Paste Selection.PasteSpecial Paste:=prdq 'Windows("monthly.xls").Activate 'tried this too 'ActiveWorkbook.Worksheets("HRFCPP").Range("B2").PasteSpecial Paste:xlValues wbkTemp.Close False End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I have a problem where I have several columns from C to N with numerous number data going down the rows. I have then a column O call 'Diff' which calculates the difference in value between two cells.

Eg. Cell F4 value is 4, cell G4 value is 10. Cell O4 is then G4 minus F4 = 6 (i.e. the difference between the values in F and G).

I have then a VBA code which hides and shows columns from C to N depending on a value in A3. Accordingly, as a result of this code, there are only ever two columns shown to the left of Column O (e.g. F and G, or M and N, or D and E depending on which columns and hidden and the remaining columns from C to N are hidden). Columns A and B always show. Also, the two columns adjacent to the left of column O are always consecutive, eg. C/D, never C/M.

What I want to achieve is for the Column O to calculate the difference in the 'two columns adjacent to the left of it', no matter which columns show after the VBA code is applied following changes to the value in A3.

Eg:

C4=4, D4=5, O4=1

when columns C and D show

OR

L9=10, M9=14, O9=4

when columns L and M show.

How can this be achieved (i.e. can the difference between the value in the cells in the two adjacent columns to the left of column O be calculated no matter what columns these are)? I imagine this would probably not be a simple formula which could be used if we knew which were the columns which would be next to column O to the left.

Best regards, Chris

I would like to calculate maximum and minimum value for a particular column when auto filter is applied and for only the visible cells.

Thanks in Advance

This is a bit tricky, so I've attached a sample workbook to explain what I mean.

I've got 5 cells in column B on Sheet1 with the following values: test1, test2, test3, test4, test5. I would like to concatenate these values and the word "Expenses", and then check these new values against all the sheets in the workbook to see if each of these values match each sheet name. For example, does the value "test1 Expenses" match a sheet name in the workbook?

If it doesn't, then check the next sheet. If a match is found, then copy the value in cell A1 on the found sheet back to Sheet1. To determine where on Sheet1 to place this value, match the found sheet name to the values in column B on Sheet1 + "Expenses", and paste the value 2 columns over and 2 rows down.

I know this sounds terribly complicated, so let me give an example of the process, given the attached workbook. The code should do the following:

(1) Concatenate the values in column B on Sheet 1 with the word "Expenses".

(2) Look through each sheet in the workbook and find a sheet matching each concatenated value.

(3) When a matching sheet name is found, copy the value in A1 on that sheet.

(4) Go back to Sheet1 and match the found sheet name to the concatenated values in column B; i.e., match the sheet name "test1 Expenses" to the concatenation of "test1" in column B and "Expenses".

(5) When this match has been found, paste the copied value 2 columns over and 2 rows down from the matching cell.

Hope this somehow makes sense! I know I should really be doing this project in Access, but I haven't learned it well enough yet and need to see this one through in Excel, so any help would be greatly appreciated. I would have used formulas instead of code, but the problem is that the referenced sheets may not always exist.

Better stop there.

Many thanks

Pete

When you recalculate values in a Microsoft Excel workbook that contains a custom function, the Microsoft Visual Basic Editor may be open and you may receive the following error ...

Im not sure if you read my previous thread, but I really need your help as I am a beginner to VBA. I have created some VBA code which calculates some 'IndexSim' values and Payoff values. Below is the part of the code that performs the calculation (all variables and functions have been defined properly):

VB:Please could someone advise me what code to write to output the mean and standard deviation of the IndexSim values.Redim IndexSim(1 To SampleSz) As Double Redim Payoff(1 To SampleSz) As Double 'Do the simulations. For Cnt = 1 To SampleSz IndexSim(Cnt) = gBmProcess(Kt, r, q, Vol, TMat - TNow) If IndexSim(Cnt) >= IndexBetValue Then Payoff(Cnt) = Range("D18").Value Else Payoff(Cnt) = Range("D19").Value End If Next CntIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Many thanks,

Suz

My problem centers around #N/A N/A. I need to calculate the mean and the STDEV of a cellrange. The cells in this range are being updated, linked to Bloomberg. For some companies, Bloomberg will not have the information and #N/A N/A will be shown. However, if in the range, one of the cells has #N/A N/A, the mean and STDEV can't be calculated.

This is the current formula: Code:

Does anyone know how to change the formula in order to skip these #N/A N/A cells?

Kind regards,

TwoAce

Dynamic Named Range "Results" spans 5 Columns and many Rows (starts at Row 19)

.. Each cell

houses Numeric single-digit or double-digit values.

I require a Formula to calculate the INTERVALS (the number of Rows between

the LAST instance and the PREVIOUS instance in a column) between each

individual occurrence of any designated PAIR of Numeric values (single-digit

/ double-digit) in the same Row of the Named Range "Results" and return each

calculated INTERVAL result to a separate Column on the same Row of a New

Sheet - starting with the most recent ( the LAST) occurrence.

For instance, each time 80 and 87 appear together in the same Row, return the

INTERVAL by calculating the number of Rows between the LAST instance and the

PREVIOUS instance in a column - locate when both Numeric values LAST appeared

together and Count back to their PREVIOUS appearance together to get the

required Count; i.e. count from the Row ABOVE LAST appearance to the Row

BEFORE PREVIOUS appearance.

The results are returned to a chart / matrix layout: I have the criterion

vertically and horizontally and they are referenced using the horizontal and

vertical cell address that houses each criterion, and the results are

returned across the Row of the intercept of the vertical and horizontal

criterion. At some point both criterion values being referenced will be the

same, can the Formula return empty text "" when this occurs?

Example Chart / Matrix Layout:

Cell Ref. A2 and B1 criterion 80 and 80

Cell Ref. A3 and B1 criterion 81 and 80

Cell Ref. A4 and B1 criterion 82 and 80

Criteria B1 houses 80

A2 houses 80

A3 houses 81

A4 houses 82

A5 houses 83

Thanks

Sam

--

Message posted via http://www.officekb.com

This is a follow up post to "Macro better solution than cell formula with

date?" Bob, thank you, your code did exactly what I asked for but I have

found a need for more processes and my modifications to your code resulted

in errors and unexpected results. In the example below Col B is the current

day stock price (from ESQuotesdotcom). Date Bought, C2,3,4, are filled by

user. D and F are, except D1 and F1, cell formulas to calculate the day

bought plus 1. E and G are empty. I have twenty pairs of Date/Price

columns, so in this example, AP AQ should be the last 2 Cols., and 50 total

Rows available for future entries. The actual Date/Price Cols in my

worksheet start on AL and end on BW. Row 1 has the current day and

subsequent days already filled in D1,F1 and on and are cell formulas

calculated off of a base date entered by the user, say in cell C1. The

worksheet would be opened and updated daily. What I have tried to get a

macro to do (failed miserably) is - - If the day the worksheet is opened

is the current day (D1, F1, H1, etc.), get the values in Col B and place

them in the proper cells in Col E and continue this same process when opened

on subsequent days. Hope this is not too much info. Seems like a simple

process but beyond my capabilites. Open to suggestions and definately to

solutions! Thanks for any responses in advance.

A B C D

E F G

Symbol Price Date Date Bought Plus 1

Day

Bought Date

Price Date Price

Row 1 Current Day (4/22/05) 4/23/05

4/24/05

Row 2 EFFI $16 4/12/05 4/13/05

4/14/05

Row 3 SHOE $7 4/2/05 4/4/05

4/5/05

Row 4 MSFT $89 4/19/05 4/20/05

4/21/05

Dynamic Named Range "Results" spans 6 Columns and many Rows - starts at Row 2,

headings in Row 1.

One cell houses 6 Alpha-Numeric single-digit (entered with leading zero) or

double-digit values entered like 08-20-21-40-60-61 per row. Each value will

only appear once in any row and the values are listed in ascending order.

I require a Formula to calculate the INTERVALS (the number of Rows between

the LAST instance and the PREVIOUS instance in a column) of each individual

occurrence of any designated PAIR of values (single-digit/ double-digit) in

the same Row of the Named Range "Results" . The results of each calculated

INTERVAL of a designated PAIR returned across the same Row of the New Sheet

(i.e. each Row houses the Intervals for ONLY one designated pair of values) -

starting with the most recent (the LAST) occurrence.

For instance, each time 60 and 61 appear together in the same Row, return the

INTERVAL by calculating the number of Rows between the LAST instance and the

PREVIOUS instance in a column - locate when both values LAST appeared

together and Count back to their PREVIOUS appearance together to get the

required Count; i.e. count from the Row ABOVE LAST appearance to the Row

BEFORE PREVIOUS appearance.

The results are returned to a Table layout: I have the criterion vertically,

and the results are returned across the Row of each vertical criterion.

Sample Layout Results Table:

Column A Row4 to many Rows holds consecutive Crtieria eg: 60-61

Column E to across many Columns will hold the calculated individual Intervals

for each consecutive criteria pair:

Col A Row4 (2 consecutive criteria per Row) 60-61 Col E Row4 Return count of

Intervals across Row

Col A Row5 (2 consecutive criteria per Row) 61-62 Col E Row5 Return count of

Intervals across Row

Thanks

Sam

--

Message posted via OfficeKB.com

http://www.officekb.com/Uwe/Forums.a...tions/200511/1

Dynamic Named Range "Results" spans 6 Columns and many Rows - starts at Row 2,

headings in Row 1.

One cell houses 6 Alpha-Numeric single-digit (entered with leading zero) or

double-digit values entered like 08-20-21-40-60-61 per row. Each value will

only appear once in any row and the values are listed in ascending order.

I require a Formula to calculate the INTERVALS (the number of Rows between

the LAST instance and the PREVIOUS instance in a column) of each individual

occurrence of any designated PAIR of values (single-digit/ double-digit) in

the same Row of the Named Range "Results" . The results of each calculated

INTERVAL of a designated PAIR returned across the same Row of the New Sheet

(i.e. each Row houses the Intervals for ONLY one designated pair of values) -

starting with the most recent (the LAST) occurrence.

For instance, each time 60 and 61 appear together in the same Row, return the

INTERVAL by calculating the number of Rows between the LAST instance and the

PREVIOUS instance in a column - locate when both values LAST appeared

together and Count back to their PREVIOUS appearance together to get the

required Count; i.e. count from the Row ABOVE LAST appearance to the Row

BEFORE PREVIOUS appearance.

The results are returned to a Table layout: I have the criterion vertically,

and the results are returned across the Row of each vertical criterion.

Sample Layout Results Table:

Column A Row4 to many Rows holds consecutive Crtieria eg: 60-61

Column E to across many Columns will hold the calculated individual Intervals

for each consecutive criteria pair:

Col A Row4 (2 consecutive criteria per Row) 60-61 Col E Row4 Return count of

Intervals across Row

Col A Row5 (2 consecutive criteria per Row) 61-62 Col E Row5 Return count of

Intervals across Row

Thanks

Sam

--

Message posted via OfficeKB.com

http://www.officekb.com/Uwe/Forums.a...tions/200511/1

This is a follow up post to "Macro better solution than cell formula with

date?" Bob, thank you, your code did exactly what I asked for but I have

found a need for more processes and my modifications to your code resulted

in errors and unexpected results. In the example below Col B is the current

day stock price (from ESQuotesdotcom). Date Bought, C2,3,4, are filled by

user. D and F are, except D1 and F1, cell formulas to calculate the day

bought plus 1. E and G are empty. I have twenty pairs of Date/Price

columns, so in this example, AP AQ should be the last 2 Cols., and 50 total

Rows available for future entries. The actual Date/Price Cols in my

worksheet start on AL and end on BW. Row 1 has the current day and

subsequent days already filled in D1,F1 and on and are cell formulas

calculated off of a base date entered by the user, say in cell C1. The

worksheet would be opened and updated daily. What I have tried to get a

macro to do (failed miserably) is - - If the day the worksheet is opened

is the current day (D1, F1, H1, etc.), get the values in Col B and place

them in the proper cells in Col E and continue this same process when opened

on subsequent days. Hope this is not too much info. Seems like a simple

process but beyond my capabilites. Open to suggestions and definately to

solutions! Thanks for any responses in advance.

A B C D

E F G

Symbol Price Date Date Bought Plus 1

Day

Bought Date

Price Date Price

Row 1 Current Day (4/22/05) 4/23/05

4/24/05

Row 2 EFFI $16 4/12/05 4/13/05

4/14/05

Row 3 SHOE $7 4/2/05 4/4/05

4/5/05

Row 4 MSFT $89 4/19/05 4/20/05

4/21/05

is the procedure to do this? is there any free excels addins available

to do this.

For example, My portfolio return(mean) is 12% and Risk(SD) is 18% . Now

I want to simulate values for next 30-40 years Returns. How to do this?

is there any free add-ins that wud generate these yearly returns if we

provide the population mean and standard deviation.

I need a way of tracking several 'types' of hours including calculating peak and off peak hours when scheduling employee's time sheets. these are the criterias.

- off peak hours are between monday (9am) through wed (7pm)

- peak hours are wed (7pm onward) - sunday (end of day)

- if an employee works over 8 hours, it's only counted as 7.5 hours of pay (subtracting lunch/dinner)

i need a way of entering times into excel so that these hours will be calculated in some shape or form for any given month for several employees. this is for a sales company and therefore, individuals working more during peak times will have a higher quota and vice versa (to be worked out elsewhere on this sheet). when i first tried to tackle this, i ended up with a lot of extra columns that held values for each day per employee. this ended up very messy. are there some forumlas that can help keep track of this data and help minimize extra columns? hope this all makes some sense. thanks in advance!

Dynamic Named Range "Results" spans 5 Columns and many Rows (starts at Row 19)

.. Each cell

houses Numeric single-digit or double-digit values.

I require a Formula to calculate the INTERVALS (the number of Rows between

the LAST instance and the PREVIOUS instance in a column) between each

individual occurrence of any designated PAIR of Numeric values (single-digit

/ double-digit) in the same Row of the Named Range "Results" and return each

calculated INTERVAL result to a separate Column on the same Row of a New

Sheet - starting with the most recent ( the LAST) occurrence.

For instance, each time 80 and 87 appear together in the same Row, return the

INTERVAL by calculating the number of Rows between the LAST instance and the

PREVIOUS instance in a column - locate when both Numeric values LAST appeared

together and Count back to their PREVIOUS appearance together to get the

required Count; i.e. count from the Row ABOVE LAST appearance to the Row

BEFORE PREVIOUS appearance.

The results are returned to a chart / matrix layout: I have the criterion

vertically and horizontally and they are referenced using the horizontal and

vertical cell address that houses each criterion, and the results are

returned across the Row of the intercept of the vertical and horizontal

criterion. At some point both criterion values being referenced will be the

same, can the Formula return empty text "" when this occurs?

Example Chart / Matrix Layout:

Cell Ref. A2 and B1 criterion 80 and 80

Cell Ref. A3 and B1 criterion 81 and 80

Cell Ref. A4 and B1 criterion 82 and 80

Criteria B1 houses 80

A2 houses 80

A3 houses 81

A4 houses 82

A5 houses 83

Thanks

Sam

--

Message posted via http://www.officekb.com

Looking at the example workbook I've attached, I have thousands of patients with weight changes recorded on different drugs.

1.) I want to instantly calculate the change in weight in column E but leaving it blank if there is data missing in column D or E. (Simply taking column D from column C throws out a stupid result if column C is blank!).

2.) I would also like to write a formula which calculates the mean change in body weight for each individual drug (column B) AND for males and females (column A) for each of the drugs. I'm struggling with all these variables! If column E is empty because data is unavailable, I want this ommitted from the calculation of mean.

Similarly I would like to calculate the standard deviation for each drug broken down into male and female patients and ignoring blank squares in column E.

Many thanks

Andrew