Questions tagged [spreadsheet]

Questions about spreadsheet software in general and interoperability between spreadsheet programs.

Filter by
Sorted by
Tagged with
0
votes
0answers
5 views

Listing and formatting tagged data in Google Sheets / Excel [migrated]

I'm creating a list of texts with some tags associated to them in Google Sheets, each text has a "tags" column where the tags are separated by commas. I want to have a second sheet where the ...
0
votes
1answer
26 views

How to swap rows by just swapping the first cells?

Say I have this table: Occasionally I need to swap and add rows to reorganize my table. In this example, I put the row Cat at the bottom, and insert row Horse: And then later decide to move row ...
3
votes
2answers
138 views

How to combine two rows in a spreadsheet into a single row?

I want the third row to be a combination of the first two row. The first two rows can be of varying length and will be different length from each other. How can this be done?
0
votes
4answers
117 views

Add a text comment in a numeric cell with Excel

Is there a way with Excel, to put a comment with a numeric cell, like the cell A3 here: such that formulas like =SUM(A1:A5) still work? Here the wanted result would be 19. Here it doesn't work ...
0
votes
1answer
32 views

Convert csv-like file (with custom separators) to ods, odf, xls, or xlsx

I have a csv-like file with complicated data and would like to open it in LibreOffice Calc, Microsoft Excel, or another spreadsheet program. What I mean by csv-like: Fields are separated by the ...
0
votes
1answer
32 views

How would I copy an Excel sheet that has formulas with “Table2[Date]..” to a new sheet?

I created a sheet that uses several tables. There are many formulas that reference different rows of different tables on the same sheet. The sheet was for the month of Feb. I want to copy the format ...
0
votes
1answer
31 views

Trying to find the largest value from the given data, per day and then try and map the largest data with date and time onto a graph

Scenario: A machine is processing a number of items randomly throughout the day, every day i.e. 54x items are produced at 17:00 and then 207x items are processed at 18:00 etc (Screenshot provided) ...
-7
votes
1answer
48 views

URGENT =IF(C3>37.6,“high”), IF(C3<36.6,“low”),IF(AND(C3>36.7,C3<37.5,“normal”))) is this formula right

The second half of the formula doesn't work If body temperature of patient (t) < 36,6, it is low, from 36,7 to 37,5 - normal, greater than 37,6 - high. This is the information to create the ...
0
votes
0answers
25 views

How can I Insert new columns without my formula changing?

In cell B2 I have a formula that states =SUM(E2:AAC2). And In B3 I have =SUM(E3:AAC3) (AAC is simply to indicate that I'm trying to sum along the row indefinitely) and this repeats many subsequent ...
1
vote
0answers
19 views

How to quickly select items that meet a criterion on excel

I have two excel spreadsheets. One has hundreds of names and emails (the names aren't sorted properly and include people who will not be receiving an email), and another with hundreds of names that ...
2
votes
1answer
225 views

Both '.' and ',' for decimal separator in LibreOffice Calc?

I have read How to set decimal separator in Libre Office Calc? but I haven't found an option for this: Is it possible to set both . and , as decimal separator in LibreOffice Calc? In France it's , ...
0
votes
0answers
10 views

IF used to search a Range in SUMIFS in Google Sheets

This is my first post so.. Let's begin. I am trying to total salaries contingent on data in two ranges. My first formula used 3 SUMIFS to check the range of pay, salary/hourly, full-time/part-time ...
1
vote
0answers
10 views

finding difference in two columns of a spread sheet?

lets say I have 3 columns A B C 1 2 2 3 4 5 and i will go on entering numbers in A and B, in C I need to have the difference between col B and col A, can I write a single formula for ...
0
votes
1answer
21 views

matching names from one Excel Spreadsheet to another

I have two separate spreadsheets, both with several hundreds of employees names. My task at the moment is to go through one of the spreadsheets to see if any employees are matching on both, which will ...
0
votes
0answers
25 views

How do i make adjacent cell as dropdown with values from a defined named range? Please refer image

I have a Dropdown on cell C11 (options - Google, Samsung, Apple, iPad). If I select let say 'Samsung' my expected output on D11 is Dropdown with values from 'Samsung Category' named range Please ...
0
votes
2answers
69 views

Data validation based on another cell and two column

I have a list of Food and each food has a color. Food may have multiple colors. For example, below, Banana can be Blue, Green, and Yellow, Orange can only be Green On another cell, the user can ...
0
votes
2answers
60 views

Multiplying Arrays of Uneven Length based on criteria and Summing the result

I have two tables in my spreadsheet, a Name Table: and a Category Table: From the above two, how do I create a formula that lets me calculate cost by category in a single cell as shown below:
1
vote
0answers
8 views

How do I turn off autoformat OpenCalc / OpenOffice Spreadsheets

Without context and background: Read the title, How do I turn off AutoCorrect in OpenCalc, specifically time, preferably turn off all AutoFormat. I am using a spreadsheet to track how fast my online ...
0
votes
4answers
158 views

Display column header containing highest value in a spreadsheet

So, I have a list of names and values, linked here: Ideally, I want to locate the highest value in the spreadsheet, and then display the name of the person with the highest value overall. So far, ...
0
votes
0answers
100 views

How to sync two columns and update the following cells in the rows?

Say I have two files, which have first columns are identical: Note, that only the first columns are synced. The second columns are different. Is there a way to make a rule between two files that, ...
0
votes
0answers
22 views

Wps spreadsheet function issues

I tried to convert from a kelvin temperature in celsius with this function: =CONVERT(L3;"kel";"cel") and works well only with this type of example numbers: 300 or 300.7888436480150. But if i ...
0
votes
1answer
39 views
1
vote
1answer
27 views

How to fit a polynomial trendline such that only the leading two coefficients are non-zero in Excel?

I have data on the displacement of an object over time and a cubic trendline fits it very well. I know that the initial displacement is 0 so I can tick the "set intercept" box. However, I also know ...
0
votes
1answer
36 views

Excel spreadsheet is showing all 65536 rows?

There's an important spreadsheet that I use in work with around 5000 rows of data, and is updated daily. Someone has formatted the entire columns to have full borders, thus extending the spreadsheet ...
0
votes
2answers
52 views

MS Excel - Add / Subtracting time in 0000 format

I am trying to subtract time on Excel. My company uses a 24hr time format (0500,0600, 1250, 1300 etc.) I'm trying to calculate the time difference between 2 cells: | A1 | B1 | |------|------| | ...
-1
votes
1answer
36 views

How to automatically update all the relevant cells in Excel when a new row is added

So I have a spreadsheet used for calculating let's say the taxes on something which I purchased. It has a column with the sales tax calculation, state tax, etc. and all each of these columns reference ...
1
vote
0answers
34 views

What is the VBA equivalent of using an array formula to calculate the max non-blank value of a range?

I have the following array formula in Excel: {=MAX((C9:C44<>"")*(ROW(C9:C44)))} that calculates and returns the row number of the last non-blank cell in a range. I could reference the result in ...
0
votes
1answer
44 views

How to automatically multiple match/dropdown on Excel

I am trying to generate a report for the assets within the company. I am essentially trying to create an automatic drop down list. I have done some research on using INDEX and AGGREGATE methods but I ...
-1
votes
1answer
39 views

How can I OCR the text from specific regions of a screenshot, then output that to a usable format?

I'm trying to automate logging of uh, ranked games in Street Fighter of all things - Yes, nerdy, I know. I have a spreadsheet In that I manually put in the following for each ranked game: The ...
0
votes
1answer
68 views

How to sum values in a spreadsheet based on matching criteria in another sheet?

I have a spreadsheet where I track income and expenses. The spreadsheet has two different sheets which I am trying to craft a query between (example tables below). Transactions: Categories: The ...
0
votes
2answers
24 views

How do I apply =If to an existing formula?

I have this formula currently: =INDEX(C2:C6,MATCH(MAX(B2:B6),B2:B6,0)) I want to apply this formula if the criteria is something specific in column A. Any ideas how to do it? Perhaps my initial ...
0
votes
0answers
20 views

Filtering long lists in OpenOffice Calc

I have 3 keyword lists separated into columns: Column A, Column B, and Column C Column A is the primary list, containing 100s of keywords. Columns B and C are secondary lists, containing many, but ...
2
votes
0answers
37 views

Chromebook - Sort and Filter in Google Sheets while Offline

I have spreadsheets stored offline on a Chromebook that I would like to sort and filter without needing to go online. They were created by downloading from Google Sheets, but when I open them locally ...
0
votes
1answer
53 views

Adding Values of a specific label/tag in a spreadsheet

I am designing a spreadsheet for an accounting framework where I record all the transactions. Say there are 3 categories of transactions: A, B, C. I want to record the transaction, label my ...
0
votes
0answers
11 views

Using Apple Numbers for iOS, how can I get the daily average with some dates have muliple entries?

Date Company Earnings Hours *Earn/Hr Mls *Earn/Ml Runs 11/23 Quicky $102 4hr 0m $25.50 53 $1.92 6 11/23 Slowboys $20 1hr 0m $20.00 9 $2.22 1 11/22 Quicky ...
0
votes
0answers
26 views

Can't replace filtered rows/constants

I have a table which has Questions (Column A) and then multiple possible answers associated with each question (Column B). This means that for most questions there are a few blank rows in Column A as ...
0
votes
3answers
657 views

Merge multiple columns into one

In Excel, I'd like to merge/combine the data of multiple, nonconsecutive columns into one (excluding the headers, preferably on another sheet). Example: I have a sheet containing rows of names which ...
0
votes
0answers
36 views

Preserving columns when converting PDF to Excel Sheet

I have a PDF of data which I receive on a monthly basis. The PDF is about 100 pages. The problem is when I convert the PDF to Excel, I lose the formatting of the columns and the data becomes too messy ...
1
vote
1answer
23 views

Blank function bar

I used a function to show the result of a calculation, but is it possible to hide such function from the function bar? Example: I used C4 and D4 to get the result on E4, when I click on E4, I see ...
0
votes
1answer
25 views

How to calculate number of paychecks in a semi-monthly basis?

I know I can easily count it using a calendar, still I wonder, how would a formula tell me the answer if usually people are paid by the 5th and 20th of the month. Example: Date of hire: 02/06/19 Last ...
0
votes
0answers
109 views

Why don't Excel and Google Sheets allow filtering a non-contiguous column range?

A quick search around the web shows that there is a quite common question about both Excel and Google Sheets: is it possible to create filters for a non-contiguous range of columns? Suppose you have ...
0
votes
0answers
42 views

MS Excel - Separate texts by delimiter into specific columns

In MS Excel, I need to separate text by comma, extract three things from right and put them in three different columns i.e. city, State and Country. Problem: In the original text which is the full ...
1
vote
2answers
204 views

MS Excel - Separate text based on keyword

I need to separate text based on a keyword to different columns. the original text is in one column but I want to split the intended subtext into different columns. The problem in this case is I get ...
0
votes
1answer
26 views

Convert Multiple Cell Rows Into Single Cells

I am merging two Excel databases and need to ensure that some prices are the same, however, they are stored in two different ways. To facilitate easy comparison of the tables I need to convert a ...
0
votes
1answer
103 views

Sorting a list of electronic components?

In a spreadsheet the bill of materials of electronic components lists the components by their values, for example, 1R0 (for 1 Ohm), 1k0 (for 1 kilo-Ohm), or 22p (for 22 pico-Farad), 1n0 (for 1 nano-...
1
vote
1answer
42 views

Open a spreadsheet with broken formulas

I am using code the generate a spreadsheet, including a formula for a cell on certain rows. When I do this, there is something wrong with the formula, and when I try and open the spreadsheet, Excel ...
2
votes
1answer
120 views

Devising Excel Spreadsheet Formulas

I'm using Excel for the accounting purposes of a group venture, to keep track of our expenses, costs, payments, and balances. Details of each expense are stored in columns A:J Total costs and ...
1
vote
2answers
42 views

How can I sort out a list of numbers according to certain characters in the middle of these numbers?

So, at my job, we want to divide the work of checking certain items according to their registered numbers. Here's an example of these numbers: 0022499-80.2017 0200781-98.2016 0021322-13.2016 Items ...
0
votes
0answers
64 views

How Do I repeat a calculation until I cross a threshold in Google Sheets

I have a Google Sheet I'm using to evaluate financial goals. In this sheet, I have 8 cells. The first two cells are used to enter parameters that are used by the formulas in the other cells. The ...
0
votes
1answer
26 views

How to sum a column UP TO “this” cell?

I'm using Google Sheets but hoping for others' sake a general answer might work here. See the screenshot below. When I insert a row below the column containing "$37.50" and I enter a number in the new ...

1
2 3 4 5
12