Hi,

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.

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.

- Complex MEDIAN function PLEASE HELP!!!
- Select Case in a VBA function - please help!
- Trouble with SUMIF function...Please Help!!!
- Custom SUM function please help!
- Average/Offset Function...Please help!
- PLEASE HELP, IT DOESNT MAKE SENSE!!!!
- Data analysis option not visible
- Please Help!! Values Not Updating from linked source
- Data Validation with IF
- Data Validation Depending on Another Cell
- Get comment of cell from one workbook to another workbook
- Forecasting Issues
- Email Excel Function but data won't copy to other workbook?? Please help!!!
- Data analysis help
- Need help on Data Analysis Tool Pak-VBA
- Please Help with Excel Functions
- Data analysis addin in Excel 2007
- Function Needed! Please Help!
- Data Analysis
- Suggestions for good statistical data analysis tools (excel addins)?
- Data Analysis & Chart Of Questionnaire Results
- Please Help With Substitution Function!
- PLEASE HELP! Comparing Two Sets Of Data for Validation
- Concatenate Problem- Please help!!!!

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

=(DAVERAGE('2000'!$A$1:$CR$10650,$A3,Inputs!$B$9:$C$26))

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

=MEDIAN(IF(('1998'!$A$2:$A$10649=Inputs!$B$9:$B$26)*('1998'!$CS$2:$CS$10649=Inputs!$C$9:$C$26),''1998'!$B$2:$B$10649))

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

PLEASE HELP

[ 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

2

3

-3

4

-9

1

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!

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

L

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.

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

Range("A").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.

Alok

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

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.

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.

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.

Sub Picture23_Click() 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 = "user@user.com" .Subject = "Subject Line" .Attachments.Add ActiveWorkbook.FullName .send '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 StopMacro: 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

"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

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!

Chris

PS. The code I receive from the friend is

Option Explicit

Public Sub Main()

Range("A1").Select

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

Do

If ActiveCell.Value = ActiveCell.Offset(1, 0).Value Then

Rows(ActiveCell.Row).Delete Shift:=xlUp

Else

ActiveCell.Offset(1, 0).Select

End If

Loop Until ActiveCell.Value = ""

Range(strColumn & "1").Select

End Sub

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!

I have used StatTools previously.....any suggestions are welcome

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

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

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:

XXXXX.XXXXXX.XXX.XX

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?

Thanks

Gil

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