User Profile
cool2021
Iron Contributor
Joined 5 years ago
User Widgets
Recent Discussions
Re: ANOVA table issue with non numeric data
Just format your data so that all cells...including blank ones...are numeric format. And don't forget to have your data so that variable (eg. months of year) are column headings and your data points (eg # customers) are listed under each column heading.5.1KViews0likes0CommentsRe: One between one within ANOVA
Hi Colin. You need to organize your data so that your columns are the variables and then within each column you have your measure (eg # of customers) data points on which you are performing the F-Test and ANOVA using the Excel Data Analysis Toolpak. It is an Excel Add-In.1.1KViews0likes2CommentsCount number of records - QueryTable.CommandText property (Excel) - VBA and Power Query
Why will the following SQL statement not work re: count number of records and group by ProductName: Set qtQtrResults = _ Workbooks(1).Worksheets(1).QueryTables(1) With qtQtrResults .CommandType = xlCmdSQL .CommandText = "SELECT ProductName, Count(*) FROM Products GROUP BY ProductName" .Refresh End With576Views0likes0CommentsRe: Excel 365 sheet view not working in the desktop app
MLSA2022 , no problem. It's based on my experience: use Excel (either Desktop or Web) to connect to data in the Cloud, and, then, each individual user can do whatever they want on their data without altering the source file.2.1KViews0likes0CommentsRe: Excel 365 sheet view not working in the desktop app
LaurLaura I am not sure the nature of your data. However, as a rule of thumb, if you have a raw datafile shared amongst several people in the Cloud, it should never be in a spreadsheet. Instead, it should be in a raw format like a .csv or .txt datafile stored either in OneDrive or on a Cloud server. Then, whether it be from Excel Desktop OR Excel 365, any user can access the source, shared datafile and get it into their Excel in several ways before doing their filtering and sorting: 1) PowerQuery, 2) SQL 3) Import. Raw data -- should never be stored in OneDrive in an Excel file format to begin with.2.2KViews0likes8CommentsRe: Flag Outliers in an Excel Datafile Before Export
Martin_Weiss that is okay. I figured it out. I used a VBA Macro to create the new column of data with a flag (eg. High Outlier, Normal, Low Outlier) for each record without having any formulas in the spreadsheet cells. Then, I could just export to .csv.1.2KViews0likes0CommentsFlag Outliers in an Excel Datafile Before Export
How do I create a new column of data in an Excel datafile flagging outlier values and then export that datafile so I can do more advanced modeling or analysis in SAS or SPSS? BUT....just using the conditional formatting colour scaling in Excel will not work since colour scales formats do not export into a .csv or .txt file that I would then import into SAS. I need to export a datafile from Excel - either Web or Desktop - so I can bring it into SAS or SPSS to do some further analysis. But, before I do, I need to have a new column of data in my Excel file that flags records that are outliers - unusually high or low data values - based on one column of $ Average Order Amount data in my file.1.4KViews0likes4Comments
Recent Blog Articles
No content to show