Questions tagged [excel]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0
votes
1answer
9 views

How does Excel obtain the R² determination coefficient in an exponetial regression?

How does Excel obtain the R² determination coefficient in an exponetial regression? This may seem a silly question but Excel actually shows a R² coefficient for non-linear regressions. How does Excel ...
0
votes
1answer
41 views

I have a pandas dataframe and i need to clear all the special characters other than space

Input: import pandas as pd df=pd.read_excel("OCRFinal.xlsx") df['OCR_Text']=df['OCR_Text'].str.replace(r'\W+'," ") print(df['OCR_Text']) Output: The excel ...
1
vote
1answer
38 views

Comparing excel data sets in Pandas

Pretty new to Python, but as an SEO I'm looking at the benefits of using notebooks in my workflow. I've got two excel files which I've cleaned and imported into a new notebook using pandas. I'm ...
1
vote
0answers
14 views

Calculating success rate based on specific range

I have a question regarding measuring success rate through bar chart or scatter plot and I’m mainly using excel. Here is what the data looks like: There are 3 columns: 1)Date of entry 2)Date of ...
1
vote
1answer
21 views

How to group by ID, and output without repeating IDs? [closed]

Input UserID Profile 101 apple 102 banana 103 pie 101 banana 102 pine 103 berry Output ...
0
votes
0answers
18 views

How can I compare continuous and non-binary categorical data in Excel?

I'm trying to find the correlation between vehicle days on lot vs different categorical variables (color, make, model, etc). I know it's possible with binary data, but I haven't found a simple way to ...
1
vote
1answer
23 views

Relationship between two sets of dates

My wife has seizures a few times a month and takes medication for the seizures once a month. So I have two sets of dates - the dates of the seizures and the dates she got her medication. I'm trying ...
2
votes
2answers
79 views

Pandas datetime error when reading from excel file

I am trying to read an excel file that has two columns using pandas. This is how the data looks in excel file: ...
0
votes
3answers
42 views

What is the better to pre-process data, MS-Excel or Python/R [closed]

While handling data, many anomalies occur that might change the results. For example, outliers, missing values, etc. So in this context, what is the better tool to pre process data? A visual tool like ...
0
votes
0answers
14 views

Need help on this question

I am advised to use VBA to solve this task. However, I am not quite sure how do I go about it. Hence, any help will be very much appreciated! Everything shown on the screenshot should be fairly self-...
2
votes
0answers
88 views

Tools for reading data from large, irregular csv files (aka excel file hell)

I have a csv file with 1000 columns and 50 rows that was collected over a few months. Embedded randomly in the file are ~1000 small datasets with semi-standard formats (some columns differ, so does ...
0
votes
0answers
6 views

Macro Excel visual basics referring to continual worksheets

Good Afternoon everyone, Currently I am dealing with the issue of a macro that always refers to the one excel worksheet in which I have recored the macro. I have around 300 worksheets that I would ...
1
vote
1answer
37 views

Starting search from rightmost column and moving left pandas

I'm very new to pandas and I'm trying to implement the code to search for something on an excel spreadsheet. Basically, the most recent price can be found on the furthest right column named ...
0
votes
1answer
120 views

VlookUp: how to loop it? [closed]

I am not an experienced user of Excel because I usually use python but I am obliged to use it this time. This is the big picture of my case. The problem is: I have a sheet with a column with some ...
2
votes
1answer
23 views

Adding Group Average Line to Bar Chart

I am trying to create a PivotChart in the form of a bar chart and I would like to add a group average line on the chart. I have this (sample) table in my PowerPivot Data Model: Group averages are A: ...
1
vote
1answer
1k views

Changing Timestamp format for Date-Time in Excel/Pandas/Python?

I have a excel data with time stamp format like this "2019-06-10T14:05:00+05:30" [YYYY-MM-DDTHH:MM:SS], I want it to be converted into "2019-06-10 14:05" [YYYY-MM-DD HH:MM], Is it possible to convert ...
2
votes
5answers
81 views

30 Years Of Excel Test Data

I am a CS intern at an industrial company that has 30 years of excel files that need to be analyzed. Looking at the data, only a fraction of the files need to be looked at and used. After those files ...
1
vote
0answers
50 views

Cleaning Excel Data in Merged Cells

I need to use an Excel file that have many merged cells with no specific order. I guess that the file creator thought that it would be more readable to merge the cells that have the same value. ...
1
vote
1answer
29 views

Different t-test results from R and LibreCalc

while I was trying to apply one-tailed t-test to two vector of data on R, I just wanted to apply same thing on librecalc also and then I noticed that p-values are different. Here is a summary what I ...
0
votes
4answers
75 views

How should we manage large database

We currently manage a large volume of economic data in Excel in my organisation. All of the data is downloaded from different online databases into Excel spreadsheets (one for each data frequency ...
0
votes
0answers
17 views

Analyze additions and subtractions to/from a data set

I have a data set in the following form: ...
1
vote
1answer
90 views

Importing Excel format data into R/R Studio and using glmnet package?

I have no problem importing Excel formatted data into R/R Studio and use all other R packages that I use. But, when I want to use the glmnet package to develop a regularization model, I invariably ...
1
vote
0answers
6k views

Pandas Excel writer update existing excel file [closed]

How do I update an excel file and not overwrite it? ...
4
votes
3answers
17k views

How to Write Multiple Data Frames in an Excel Sheet

I have multiple data frames with same column names. I want to write them together to an excel sheet stacked vertically on top of each other. And between each, there will be a text occupying a row. ...
0
votes
1answer
358 views

Categorical data into numeric in excel

I have a large dataset and I would like to convert these categorical data into numeric in binary form to perform k means clustering in R. However, I get an error in value. This is the formula that I ...
3
votes
2answers
96 views

Expectations of the skill set of a Data Scientist [closed]

Although there are more powerful tools than Excel which a data scientist should know how to use, would it be expected that someone indicating they are a Data Scientist know how to use Excel?
1
vote
0answers
25 views

Excel All Possible Combinations for MacOS

Is there a formula in Excel to calculate all possible combinations for multiple lists for MacOS. For instance, I am trying to draft a fantasy team and I have the following combinations: ...
3
votes
3answers
377 views

How to predict whether or not a customer will renew

I have a dataset of customer contracts that specify a start date and if applicable an end date. Each month a customer is up for renewal. Below is an example of how the data is organized in excel: <...
1
vote
1answer
52 views

Drop NA Values with SAS

I have an xlsx file that I wish to delete the NA values in SAS. I'm new to ...
1
vote
1answer
488 views

Calculate weighted mean for two columns and hundreds of rows?

I'm trying to teach myself basics of R and I couldn't find the answer: Say, I have a csv file and I want to calculate weighted mean for each subject such that I have a mean mu = 0.015*1030+0.16*26930+...
1
vote
1answer
40 views

Problem when exporting scored labels to excel

I have done a regression using Azure ML Studio and it ok. The r squared is 95% on the test sample. When predicting, the results look ok when visualizing the data. The results appear as expected, with ...
-1
votes
1answer
174 views

How to add values to new row to a csv in python

So what I'm trying to do is iterate over a (grayscale?) image and look at each pixel and write that pixel's grayscale value to the second row(the first row is the pixel number). Code isn't doing ...
8
votes
1answer
344 views

How far can one go with excel? [closed]

in my business we handle all analytics through Excel. This includes mostly scheduling, production planning and accounting operations. We currently are looking into adding a bit of predictive modelling ...
1
vote
1answer
63 views

How can discard “peaks” values from a chart?

I have this chart where you can see that some points are far away from the "trend" of the curve. The chart represents $5,000$ values where each value corresponds to a specific hour of a day, month ...
0
votes
2answers
22k views

Search and Replace in specific column of csv with python

I created a program that search and replaces over an entire csv file but I need to make so it is column specific. Is there a way to only search via a column. Here is my code, I am pretty new to python ...
2
votes
0answers
52 views

Orange “Scatter plot” widget to excel, best way

So I have chosen "Orange" for my research as a main app for clustering ant it's analysis. Excel 2016 is primary app used for dashboard (via "pivot table"). Problem - how to add graphs from "scatter ...
0
votes
1answer
303 views

How can I transpose a high dimensional dataset?

I have a (.csv) file with more than 35,000 rows and 100 columns, where the rows represent the attributes and columns represent the instances. In Excel the maximum number of columns is 16,384. ...
1
vote
1answer
51 views

How to manipulate this column of less than/greater than?

I have a dataset in which one column is as given in the picture. What will be the best way to handle such a column?
0
votes
1answer
2k views

Compound Annual Growth Rate Function in R Statistics

I need to calculate the compound annual growth rate for two periods of time: 2014 & 2009. I'm currently working with Excel, I have two columns with data for each year (One column with only data of ...
1
vote
2answers
53 views

Find value and return row in excel spreadsheet with over 1 mil rows [closed]

I have an excel spreadsheet that has over 1 million rows and about 120 columns. I frequently need to find a value or a part of a value (value is name or number) and return the row where the value is ...
0
votes
1answer
285 views

No result for scored labels in Azure ML Web Service

I am trying to predict scored labels using regression. But when I am about to get the result from Azure ML Web Service in Excel 2016, there is no result appeared in the scored label column. How should ...
1
vote
2answers
84 views

Discovering cross category sales using transactions history (Clustering?)

I have a set of sales transaction data containing more than 1 item purchased. Every item sold has a category. I would like figure out which categories are most commonly ordered together. The data is ...
2
votes
1answer
35 views

MS Access issue [closed]

I have a database that I send to an excel sheet that auto populates some field in a report with calculations that I need. There are several users and the excel sheet cannot be open at multiple times ...
1
vote
1answer
80 views

Excel file merge with different headers but same data

I need to merge data from 1000s of excel files provided by different operations managers on productivity and other reports. The excel files have similarity of data but the headers are all custom since ...
3
votes
1answer
228 views

logistic regression coefs to excel formulation

I have done a Logistic regression in r with 3 variables involving 1 interation: lm(predicthead~vol+weekday*head, mydata) weekday is a factor - 7 days of the week....
10
votes
6answers
2k views

Is Excel sufficient for data science?

I'm in the process of preparing to teach an introductory course on data science using the R programming language. My audience is undergraduate students majoring in business subjects. A typical ...
-1
votes
1answer
49 views

Excel formula question

I have a rather complex formula that I need to build. Basically I get a list of data every month, and there is a metric for the first operator of the day. The first column comes in the date and time ...
4
votes
1answer
106 views

How to generate bulk graphics using R

I have a dataset of Key Performance Indicator (KPI) and for each KPI I have a current level of achivement and 2 targets : Target1 and Target2. How can I automatically generate one graphic for each ...
1
vote
2answers
915 views

What are recommended ways\tools for processing large data from Excel Files?

A Very Happy New Year! I'm currently working on an analytics project with large volumes of data stored in excel files (about 50GB in 1000 files). The files use a custom formatting to store date-time ...
1
vote
1answer
169 views

Best practice for computing week-over-week change in MAU metric

When we calculate week over week change for a metric like "Monthly active users", we often refer to a calculation of SUM(This week) - SUM(Last week)/SUM(Last week), which means we will run total of ...