Free Microsoft Excel 2013 Quick Reference

Data Analysis Plugin, Addins, Function.. Please Help!


I am an analyst, I am using Pivot table, Filters & cond. formatting for data analysis in Excel . I would like to know that, What are the other feature/add-ins/Plug-in exist in excel which is useful for data analysis. Please Help. Thanks in advance.

Post your answer or comment

comments powered by Disqus

I need help creating a median formula.

i have 2 worksheets on the worksheetA the user enters a list of names. on worksheetB is the data for these names. the function needs to get all the names search to see if they match on worksheet b and then take the values and return an median

this is the function i had used to return the average


200 is the name of the data table a1:cr10650 encompasses the complete table. A3 is the column refrence, inputs is the table where the names are listed and b9/c26 is that complete table..


i was hoping this formula would replace the old one but it didnt work. basically i wanted this formula to see if there was a name in column a on the 1998 worksheet which matched the name on the inputs worksheet and if cs (Another field) matched c on the inputs worksheet and if it did to return b from 1998 and give me the median...

hope this is enough detail.. and not too much either...


[ This Message was edited by: Judo Tom on 2002-09-06 11:22 ]


I have a database where we analyze transaction data. The transaction fee that we charge changes from time to time. I tried to capture the change in the transaction fee over time with the following code:

Function getTransactionFee(TransactionDate, Total)

Select Case TransactionDate

Case Is >= DateValue("10/03/05"), Is = DateValue("12/05/05"), Is = DateValue("12/10/06"), Is

I have a sheet that looks like this:


I want to SUM only the values >0 to get 10; however, when I use the function: =SUMIF($A:$A,>0) it doesn't work.

I've also tried ">0" , ($A:$A,>0,$A:$A) , (A1:A6,>0) , etc...

Please help!

Hi Everyone,
This is really puzzling me so please help.

I want a function that sums two columns only if values in both columns are positive.

Sub Create_aggregate(Numerator As Range, Demoninator As Range)

Dim SumN As Integer
Dim DumD As Integer
Dim nRows As Integer
Dim dRows As Integer

SumD = 0
SumN = 0

nRows = NumeratorRows.Count
dRows = Denominator.Rows.Count

For i = 1 To nRows
If Numerator.Cells(i,1) * Denominator.Cells(i,1) > "0" Then
SumD = SumD + Numerator.Cells(i)
SumN = SumN + Denominator.Cells(i)
End If
Next i

Result = SumN / SumD

End Sub

This is what I have so far, can any one make a suggestion.
Num Den
25 65
65 0
35 32
-1 8
Result = (25+35)/(65+32)

Thanks so much



I have 10 columns, with several hundred rows of data. The last six columns are employee hours (by week). Each week I add an additional colmumn (after one year I will have 52). I have an average column, but I always have to go in and re-do the formula to reflect the most recent six weeks. Is there a way I can set up a formula so the average column always reflects the average of the most recent six weeks?

I'm thinking offset...Please help.

I have written a program to simply grab infor from one worksheet and transfer it to another and it works fine in and excel document that i start from scratch but when i paste the program in an excel sheet from work and try to run the program i keep gettin "run-time error '1004'" on all the "Range" functions.

Please Help Me Out on why it works in the test file but not my work file???

I press "F8" to run each line at a time.
Here is my code:
Sub test()
Dim i, x, y, z As Variant
Dim w As String
Dim chr(100) As String
Dim num1(100) As Integer
Dim num2(100) As Integer
Dim num3(100) As Integer
Sheets("TEST TSC").Select

I am unable to see the data analysis option under tools menu. I did as
mentioned at every other place regarding check-in the Analysis Add-in check
box. When I did that, the application did some installation but still i am
unable to see the 'data analysis' option drop down. Please suggest what
should i do now.


I have a problem with a couple of spreadsheets.

When I open the master sheet I get the prompt to update or not. If I choose
either the values do not get updated. If I open the other spreadsheet that
my formula is pulling the data from the values update.

Please Help

John Whitmer

Given the below scenario :-

Cell A1 contains Data Validation of List (ie. Taxi / Bus, Car, Motorcycle)
Cell A2 to contain Data Validation to check A1 is Taxi / Bus, otherwise, no data entry is allowed.

How can I set the data validation using 'IF' function. Please help.

Given the below scenario :-

Cell A1 contains Data Validation of List (ie. Taxi / Bus, Car, Motorcycle)
Cell A2 to contain Data Validation to check A1 is Taxi / Bus, otherwise, no data entry is allowed.

How can I set the data validation using 'IF' function. Please help


I need to get the comments of a cell (say 'A1') from sheet1 of "workbook1" to cell "D1" of sheet1 of "WorkBook2"

i have got the logic of getting the data from one WorkBook to another, as below (but need for getting cell comments)

Address = Cells(1, 1).Address
Cells(1, 4) = GetData(FilePath, FileName, SheetName, Address)

Private Function GetData(Path, File, Sheet, Address)
Dim Data$
Dim TestGetData
Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & _
Range(Address).Range("A1").Address(, , xlR1C1)
TestGetData = ExecuteExcel4Macro(Data)
GetData = TestGetData
End Function


Please help for getting cell comments instead of cell data.
Thanks in advance !!

Please see the attached worksheet. I am trying to use the data analysis tool and using the moving avarage feature to focast future revenue. However, I am getting N/A values for some cells. One other thing is in the graph, I would like to have a forecasing line starts from June and goes all the way to Dec. according to the data analysis values. Can someone please help? Thanks a lot for your cooperation.

Hi so I've tested and the email function is working great, it emails with the attachment and it's copied in the body.

However the data is not copying to a consolidated workbook "destination.xls" what am I doing wrong, please help . I also would like it built so that each time its copied over it selects the next empty row.

    Dim Sendrng As Range
    Dim wSs As Worksheet, wDs As Worksheet
    Dim lr As Long
    On Error GoTo StopMacro

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    'Note: if the selection is one cell it will send the whole worksheet
    Set Sendrng = Selection

    'Create the mail and send it
    With Sendrng

        ActiveWorkbook.EnvelopeVisible = True
        With .Parent.MailEnvelope

            ' Set the optional introduction field thats adds
            ' some header text to the email body.
            .Introduction = ""

            ' In the "With .Item" part you can add more options
            With .Item
                .To = ""
                .Subject = "Subject Line"
                .Attachments.Add ActiveWorkbook.FullName
            'copying data to work list
            Set wSs = Workbooks("Origin.xls").Sheets("Sheet1")
            Set wDs = Workbooks("T:destination.xls").Sheets("Sheet1")
            lr = wDs.Cells(Rows.Count, 6).End(xlUp).Row + 1
            wDs.Cells(lr, 1) = wSs.Cells(7, "C")
            wDs.Cells(lr, 2) = wSs.Cells(10, "C")
            wDs.Cells(lr, 3) = wSs.Cells(16, "C")
            wDs.Cells(lr, 4) = wSs.Cells(16, "G")
            wDs.Cells(lr, 5) = wSs.Cells(13, "C")
            End With
        End With
    End With

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    ActiveWorkbook.EnvelopeVisible = False
End Sub


I want to use the moving average function in the data analysis tab.

However, I have read the help and it hasn't helped.

I don't understand what I need to put in the dialgue boxes.

Can someone help please?

I have uploaded what I need which is just the forecast for P4,5,6 from the historic data using the moving average function and if possible an idiot's explaination as to how to do it

Pls help...trying to use multiple regression model, but excel states that I need to check the box on my add-ins options "Data Analysis Tool pak-VBA". After doing so (Checkmark in Data Analysis Tool Pak-VBA) I tried to run the regression model again and still got the same message:

"In order to run multiple regression you need to have the Data Analysis ToolPak - VBA loaded. From the excel menu , please go to Tools, Add-Ins, and check the Analysis ToolPak - VBA box."

I'm sure that the box is checked, but I keep getting this same message. What can I do to resolve this?

-Thanks in advance

Hi all,

I have a MS Excel spreadsheet that contains data as such:

A1 - 01T2001
A2 - 02T7001
A3 - 01T2001
A4 - 99T1001
A5 - 99T1001
A6 - 02T7001
A7 - 93T0120
A9999999 - 02T7001
A1000000 - 93T0120

There are duplicates in the data. Please help me with an Excel functions to identify the duplicates. I received a VB code from a friend but there are over 1 million rows on my spreadsheet, and it took more than a day but the scribe is still running. I want to try using a COUNTIF or FIND function to see if it couldspeed upthe process. Any help is greatly appreciated!


PS. The code I receive from the friend is

Option Explicit

Public Sub Main()

Dim strColumn As String
strColumn = InputBox("Please enter the column that contains the duplicates.", , "A")
If strColumn = "" Then Exit Sub

Range(strColumn & "1").Select
ActiveCell.Sort Key1:=Range(strColumn & "1"), Order1:=xlAscending
If ActiveCell.Value = ActiveCell.Offset(1, 0).Value Then
Rows(ActiveCell.Row).Delete Shift:=xlUp
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell.Value = ""
Range(strColumn & "1").Select
End Sub

I have excel 2007 and am trying to add data analysis. I have already gone
thru the route of excel options and adding in the data analysis addin.. It
shows that it is active but it does not show up on the tool bar (any of them
including the addin toolbar) 2007 is different than the older version. can
anyone help me?


I am trying to create an equation that would automatically pick up the last
known data point in a range. For example, I have all the months starting from
Row 3 Column C, and in Row 3 Column 1, I need to create a function that would
pick up the most recent data point and place it in that cell, C1. So, if
there was something in March, it would replace February, if there was
something in July, it would replace June in C1. Please help. Thanks!

in excel, is there a place called data analysis under tools? im an exchange student in sweden andthe work that teacher gave me is half swedish and half english.. im looking for histogram there..please help

I'm looking for a statistical data analysis tool (excel addin)
I have used StatTools previously.....any suggestions are welcome

hello all
i am doing a survey to find out why employees are not filling timesheet.i have designed a questionnaire and distributed in a company n i have got 77 responses out of 150.
Now I am clueless about the data analysis part.
I have designed it in a scale of 1-5.
Now how to form hypothesis and what statistical test can i do based on my attached excel sheet.? my professor wants me to project my findings in chart.

I dont have any statistical software. I have only to Microsoft EXCEL.
Please help me. I am a student and this is a part of my summer intern project.
I have yo submit it next monday. pls help me...........

Hi, I'm having trouble with the Substitute function.

I want users of my woorkbook to enter their initials eg. PA in one cells and then excel will automatically substitute PA in another cell for Patrick Anderson.

This an example of what I have now.

Patrick Anderson(In cell A1)
PA (In cell B1)
=SUBSTITUTE(A2,B1,A1) (In cell C1)

Therefore, when I enter PA in A2, C1 automatically becomes Patrick Anderson. Now this formula works, my problem is that I dont have one or two names. I have like forty names.

Can anyone please give me the function I need to do this? Maybe it is not Substitute I should be using. Please help, I've tried everything to no avail.

I have two sets of data. One set contains about 10,000 rows of information. The other set contains about 1000 rows of data.

I know that for sure those 1000 rows are in the other set of data, but i want to be able to pick out those 1000 rows from the first set.

What is the best way to do that.

To explain in detail, the first set of data with 10,000 rows contains two columns, a unique number and a reference. The second set of data contains just the reference, so i want to be able to find the 1000 rows of data from the second set in the first set, and obtain the unique number for them.

Please help if you can.

I'm using Excel 2003 on Windows XP Pro. Thanks

Here is my dilemna:

I have 4 columns of data. Each column represents a segment of a general ledger account number.
The first column has about 54 rows, which represents the Fund. (5 characters)

The second column is the Account, and there are 71 of those. (6 characters)

The third column is the Program and there are 39. (3 characters)

Lastly there is the Project column and there are 9 of them. (2 characters)

So in essence I would like a formula that will generate all the accounts for me in this format:

54x71x39x9= 348,894 general ledger accounts

In addition, in the column following each of the columns is a short description. I would like to do the same thing above with the descriptions in the following format:

Fund descr-Account name-Program-Project

I have been trying to do this using the Concatenate function but to no avail. Can someone please help me?


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