Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Skip #VALUE! when calculating mean and stdev

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:
, 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


Post your answer or comment

comments powered by Disqus

Related Results

Hi All,

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

Hello. I'm trying to create a formula that is a bit above my level. I have a range of cells that use a drop menu. I'd like another cell to return a corresponding value when a selection is made. I want that value to depend on the amount entered in yet another cell.

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?

Hi everybody,

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

I have created a macro which copies the data from one sheet to another and rename the copied sheet as the name inputted by the user at the run time. It also calculates a formula for a specific cell on the copied sheets according to the user inputted value.

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.

I have a spreadsheet with Data in Columns A-H. Column B is an ID value that will repeat an unknown amount of times. For each Value in Column B I need to calculate the Median, Mean, and GeoMean for the corresponding range of "G_:H_"

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:
	
 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 Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks in advance!!
Cameron

Hi

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

HI ALL,

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

This is driving me crazy! It seems like it should be easy but I can't get the paste to work. I have daily means (by month) in one workbook. Hourly data is generated and saved each day in separate workbooks. I wan't my macro to open the hourly data workbook, calculate the mean and copy/paste it into my monthly summary workbook. My code works until the paste component. and I keep getting range errors. I've tried several different ways to paste but they all have range errors. The latest is Run-time 1004. PasteSpecial method of range class failed. My code is below and any help is greatly appreciated.

	VB:
	
 
Sub 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 Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
many thanks

Dear all,

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

Hi,

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

Hi

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

XL2000: Syntax Errors When Calculating Functions or Opening a Workbook

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

Dear all,

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:
	
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 Cnt 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Please could someone advise me what code to write to output the mean and standard deviation of the IndexSim values.

Many thanks,

Suz

Hello all,

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

Hi All,

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

Hello,
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

Hi All,

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

Hi All,

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

Hello,
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

Does anyone know how to stop excel from changing cell values when copying and pasting to a different location? I have tried using absolute but that doesn't seem to help.

I want to simulate values for a given mean and standard deviation. wat
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.

This is a bit complicated. I will try to explain the best I can. I would simply like to know where to start looking to solve this problem.

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!

Hi All,

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

Hi I'm fairly new to excel and can cope with the basics but struggle with more complex formulae. I wonder if anybody would be kind enough to take a look and help me!

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


No luck finding an answer? You could always try Google.