Questions tagged [spreadsheet]
Questions about spreadsheet software in general and interoperability between spreadsheet programs.
693
questions
0
votes
2
answers
64
views
How to shorten Excel OR function for many conditions?
As shown in the data, I want to determine whether a student passed (failing if s/he gets less than 40% in any subject). I can do this using many OR's inside IF.
My formula: =if(or(b3/b$2<0.4,c3/c$...
0
votes
1
answer
14
views
Clear direct formatting does not work, how do I treat the cell with a date in it as date and no as text
A cell that contains a date, 02/30/2020, is treated as text by my formula datedif from another cell whereas other cells with similar dates are treated as dates. Deleting the content of the cell does ...
- 132
0
votes
2
answers
64
views
How do I create a formula that will count all the "x" in a list, then give me a total for amount paid based on amount of "x"?
I have a list aside for a staff party where we have a running total of amount paid at the bottom, and are indicating who is attending by placing an X next to their name. I would like it to be so that ...
- 1
0
votes
2
answers
54
views
Get unique IDs with date 1 or date 2. For IDs with both date, obtain row with most recent of the 2 dates
I have been trying to retrieve the row in the following sheet (sheet1) where the dates are matching dates in sheet2 without duplicating the IDs in Sheet1 (Column B)
Sheet1
Sheet2
In other words, I ...
0
votes
1
answer
181
views
How can I evenly auto-distribute values across 12 columns in a spreadsheet so that their sums are as close to equal as possible?
I'm trying to distribute a bunch of annual recurring business costs across the span of 12 months so as to make each month's total expenditure as close to each other as possible. Doing this manually is ...
0
votes
1
answer
28
views
Is there a formula for retrieving data from multiple cells on a different spreadsheet and adding to one single cell on another spreadsheet?
I am working on a project in which I need to retrieve the Parent Items for a list of scrap material. The problem is that most of this material is used in multiple Items. When I run a separate report ...
- 13
1
vote
1
answer
64
views
replace comma separated values in a sheet uses SORT formula to get sorted results from another sheet
I have a sheet called "Form Responses 1" connected with a Google Form, and I made another sheet/tab and used this formula:
=SORT('Form Responses 1'!A2:M, 4, TRUE)
to sort the responses with ...
0
votes
1
answer
68
views
Can I have excel automatically swap text in a column from one word to another without using another column?
I'm fairly new to Excel so excuse my noobish question. I am working on a roster spreadsheet and have a bunch of shop codes, essentially abbreviations of the shop name in a column, and I have been ...
- 1
0
votes
3
answers
91
views
Return First Column Header of Instance of String
I have a collection of speakers who have spoken various dates.
Date,1/2/2023,1/2/2022,1/2/2021,1/2/2020,1/2/2019,1/2/2018
1st-Speaker,Jim,John,Fred,Sam,Scott,Riley
2nd-Speaker,...
- 113
1
vote
1
answer
91
views
How can I stop libre office sheets jumping when scrolling wide columns?
Long time sufferer of Windows, moved to Linux Mint around a month ago and I am still getting used to the changes. One very annoying thing that I have Googled, looked at the questions here marked libre ...
- 59
0
votes
0
answers
299
views
Copy output from Google Colaboratory and Paste in Excel
Given a Colab notebook with the following cell
url = 'https://files.grouplens.org/datasets/movielens/ml-latest-small.zip'
extract_zip(download_url(url, '.'), '.')
movies_path = './ml-latest-small/...
- 661
0
votes
1
answer
38
views
Find stdev of values in col B where col A matches criteria
I have a sheet where column A is dates and column B is values. I want to find the stdev of values in B where the date in A is within the last 30 days.
I am able to do sums with a sumifs which works on ...
- 101
0
votes
1
answer
99
views
Can I use Excel to solve a quadratic based on a table of x and y values? (without using a graph)
I have the following data sets where the top bold row are my y-values and the following rows (%) are my x-values. Each row is a different set of x-values. I'd like to plug in 80% for x for each set ...
- 1
2
votes
1
answer
138
views
How to set a whole row equal to a value, using ARRAYFORMULA() ? (in Google Sheets)
I would like to set the contents of all cells in a particular row to have the same value, sort of like having a banner that runs across the entire width of the spreadsheet. I'm working in Google ...
- 23
1
vote
1
answer
290
views
Turn cell references in Excel hyperlinks into the text in the referenced cells
I have a column of bare URLs in Excel. Say it's Column A. I want to replace each URL with a friendly text link, like "here." Suppose A1 contains the link https://www.website.com/content/...
- 23
0
votes
2
answers
43
views
Excel opens at top of sheet only
How can I get Excel (using Office 365) to open where I left off (usually the bottom) and not the top of the sheet? I have a large spreadsheet and usually need to add new information at the bottom. ...
- 1
0
votes
0
answers
35
views
How can I extract two or three "parallel" XPaths on a web page to/in a format that can be imported into a spreadsheet?
How can I extract two or three "parallel" XPaths on a web page to/in a format that can be imported into a spreadsheet?
Here is an example: on https://www.amazon.co.uk/s?rh=n%3A20606777031&...
- 726
2
votes
1
answer
80
views
Spreadsheet: View the entire (long) text of selected cell
I am not using spreadsheets very often and I am not sure whether this is a feature of common software (I'm using libreoffice calc).
I have a spreadsheet in which 50 to 200 word texts are entered in ...
- 1,030
2
votes
1
answer
1k
views
convert excel spreadsheet with format to markdown
I want to convert a spreadsheet table with basic formatting (bold, script) to MD (markdown) format.
I know howto convert a table to Markdown but it losses the basic format and i need to preserve it.
...
- 428
0
votes
1
answer
63
views
Sequential numbering of codes
I have a sheet where as I enter details of a product, an internal 4 character code is generated...typically 4 letters (e.g. ABCD or BADC). This is just based on simple VLOOKUP and CONCAT functions. ...
- 1
1
vote
1
answer
68
views
reference range in different sheet with Indirect column and row
I'd like to reference a range in a separate worksheet using indirect relative row and column notation, something like this:
=INDIRECT("'dataset'!R[0]C1:R[0]C4", FALSE)
instead of
=dataset!a1:...
- 113
4
votes
1
answer
107
views
How to stop Excel from combining Conditional Formatting rules into 1 broken rule
I understand that Conditional Formatting is a horrendous tool, much like Excel itself, but it's the only realistic option so I'm stuck with using it.
However, I'm unable to grasp how painfully stupid ...
- 144
1
vote
1
answer
78
views
What's the mathematical, =SUM() equivalent of the =NPV() formula, in Excel or Google Sheets?
Is there an Excel/Google Sheet SUM formula equivalent to the NPV formula, ie. one that produces an exact same answer given the same input figures?
The question might be similar to these ones but the ...
- 77
0
votes
1
answer
42
views
Concatenate data (matrices) in separate sheets (Gnumeric)
I need to "concatenate" data from two sheets into a third sheet. I need to do this in Gnumeric, but presumably it should be doable with other spreadsheet programs as well.
Sheet 1:
| A ...
- 781
1
vote
1
answer
65
views
Drag to fill duplicates values
I want to create a sequence of dates that populate a column.
A
2023-12-31
2023-12-30
2023-12-29
...
Format Cell is set to Date.
Calculation Options is set to Automatic
Enable fill handle and ...
- 363
0
votes
1
answer
79
views
Find next cell within column that is a number, starting from reference cell in another sheet
I think this sounds simple but I'm having a bit of trouble figuring it out.
I have 2 sheets, the Sample Sheet attached and an Input Sheet.
In the Input Sheet, I input one of the green shaded values (...
0
votes
0
answers
12
views
Spreadsheet Formula
I have a spreadsheet. I want to create separate spreadsheets for each of the person listed on the column C that pulls new rows from the Master Sheet, only when their name appears in column C. So the ...
- 1
1
vote
1
answer
53
views
Custom Information Message for user based on another cell value
I am busy with an optimization spreadsheet which needs to display an information message, maybe similar to the Input Message for data validation (but I am open to suggestions). This message will be ...
- 21
0
votes
0
answers
16
views
Dynamically changing content of a cell dropdown for validation because of a huge list
I have a huge lookup list of items, each with a conversion factor that I wish to look up.
The lookup list is too big to be practical for a validation dropdown - this is how it looks:
Here's the ...
- 188
-1
votes
1
answer
40
views
Spreadsheet: store multiple entries for one "field" per row
I have a spreadsheet that inventories our sheet music collection with multiple fields like title, composer, etc:
Octavo #
Title - original language
Composer
Arranger
Editor
Poet
Translator
Voicing
...
0
votes
1
answer
50
views
Print Excel Spreadsheet using Template?
So Let's say I have a spreadsheet
No
Name
Class
Gender
1
Jack
Science
Male
2
Rose
Science
Female
3
Kevin
Social
Male
4
Olive
Social
Female
and I have template
No
Name
Gender
1
2
3
4
How to ...
- 1
1
vote
0
answers
62
views
How to draw empty cell values in LibreOffice Calc
my problem is the following (didn't find info how to do it in the official documentation) - recorded ~108k ping results to LibreOffice table with columns
DATE (letter A),
LATENCY (letter B),
STATUS (...
- 177
0
votes
1
answer
36
views
Microsoft excel formula to connect codes of smaller areas with those of the bigger ones and sum up their values
I am working with geographic information and I have bigger areas called Regso that concist of several smaller areas called Deso. The data I have is for the smaller Deso areas but I would like to ...
- 103
0
votes
1
answer
28
views
Check row for a specific string and get the value of a column cell where the initial parameter was located
I am trying to create a calendar checker that checks if a value is detected on a specific date and return the month it was detected at:
In the image I am looking for the string "FN" ...
- 1
0
votes
0
answers
44
views
How can I combine two Excel formulas into one?
In a spreadsheet, I have five columns (total) and four of them have the components of the students' grade. The fifth has the final grade.
Now, I need to combine two formulas so that when the four ...
- 13
8
votes
4
answers
5k
views
Formula to see if a series of numbers is increasing in value?
I have a spread sheet with thousands of sets of numbers. That is too say, there are ten columns and each column has a number - and there are thousands of rows of these numbers.
What I want to do is ...
- 225
0
votes
1
answer
33
views
Excel - The most spending occurred around this date
I feel like as a human I can understand this, but I can't figure out how to get it into an Excel formula.
Given the dates and amounts spent on those dates below, how can I find a date on which the ...
- 103
0
votes
1
answer
35
views
How can I copy a larger number of cells into a smaller number of cells that are already feeding a graph?
I have two 10-cell columns that currently feed a graph (one for the x-axis, the other for the y-axis). I have now doubled the discretization of my dataset, so instead of the original 10 data points, I ...
-1
votes
1
answer
263
views
Excel: How to count how many times a consecutive text value appears
I'm making a work schedule and I have to pay attention to a specific rule that, no worker should be scheduled to work more than 6 days in a row.
I got this spreadsheet where I make those schedules but ...
-1
votes
3
answers
1k
views
Excel: How To Transpose Every Random amount of N Rows From One Column To Multiple Columns?
I have a set of multiple choices questions with ABCD as answers, all answers are stacked on a column, but not every question has the same amount of answers.
Instead of transposing every nth value, my ...
1
vote
2
answers
286
views
How can I find which cells in a column (or at least the first one) are greater than a threshold in a LibreOffice spreadsheet.?
I have a LibreOffice spreadsheet (actually a CSV file open in spreadsheet).
It has several columns.
Something like this:
1,3,4
2,4,6
1,3,4
10,3,4
2,4,5
0,3,4
-10,3,4
I want to find in a column (say ...
- 824
0
votes
1
answer
307
views
How to highlight all cells that contain formulas
In LibreOffice Calc, I want to be able to quickly distinguish between cells that contain input data and formulas. Enabling "View → Value Highlighting" changes the font color in cells such ...
- 209
0
votes
1
answer
318
views
Automating a repeating pattern across multiple worksheets
TL;DR: I need to automate a cyclical pattern that repeats over intervals that span blocks of cells across multiple sheets.
So... I have a spreadsheet that I maintain for our work group that we use for ...
- 400
0
votes
2
answers
39
views
Automation between a google spreadsheet and the downloaded version. Possible?
I use an iMac and google spreadsheets.
When I want to synchronise my spreadsheets on my drive with the spreadsheets on my imac, I tend to download them. The problem I have is the downloaded ...
- 11
3
votes
2
answers
354
views
How can I convert time values with a letter suffix like (30m, 2h, 30s) into an actual time format?
I have decimal values followed by a letter to indicate the time unit (hours/minutes) values like the following:
60m
1.5h
15m
20h
How can I convert these to a numeric value of minutes or time value? ...
2
votes
3
answers
307
views
Parse dates like "20220928" in Excel
I have an input column which contains an id + datetime:
id12345!20220926_100911
My goal is to get the date and I have already successfully extracted the date into text with a formula involving LEFT, ...
- 1,748
1
vote
2
answers
79
views
More advanced filtering with text in Excel
I would like to filter columns in Excel with use of Text Filters based on multiple criteria in text search. Is it possible?
]
]
how I would like to filter it based on words "medium" and &...
- 11
0
votes
1
answer
29
views
Adding column description of one spreadsheet sheet in another spreadsheet of same excel
I have an excel sheet having 4 different spreadsheets. I need description of column header of three spreadsheets in 4th spread sheet.
Example:
Suppose I have sheet 1 with 6 headers for 6 columns, ...
- 11
1
vote
0
answers
72
views
Creating a auto-populated population spreadsheet file in Google Sheets
Not sure if this is the proper place to post this, but worth worth a try. I am using Google Sheets to create a series of files for worldbuilding for a bunch of stories. What I am trying to do is have ...
Markitect
1
vote
3
answers
343
views
Copy cells in spreadsheet by entering coordinates
I have a large spreadsheet with 15346 rows
I enter a formula in B2, and want to copy it to B3:B15346. (I don't want a copy of the value in B2, I want an adjusted formula in each cell)
Is there a way ...
- 1,212