Questions tagged [formulas]
Formulas used in spreadsheets edited by web applications like Excel Online, Google Sheets, among others.
2,026
questions
0
votes
1
answer
683
views
Google Sheets - Filtering Chart Data with Checkboxes
In Google Sheets, I'm making a 'workbook' that keeps track of inventory in a shop. There are 3 sheets in the workbook.
The first one is the raw data which is 'Item Name', 'Initial Stock', 'Stock Used',...
CommunityBot
- 1
0
votes
2
answers
51
views
How to work over rows from all other sheets in Google Sheets?
I have a 'summary' sheet and 12 other sheets named 'Jan20', 'Feb20', etc.
In the summary sheet I have this formula (to calculate Uber costs):
=FILTER('Jan20'!F2:F, REGEXMATCH('Jan20'!D2:D, "UBER"))
...
CommunityBot
- 1
1
vote
1
answer
2k
views
Google Sheets - Split row into multiple rows
Current:
-------------------------------------------------------------------
| Amy | Adam | Bob| Cliff | Dave | David | Derek |...| Zachary
------------------------------------------------------------...
CommunityBot
- 1
1
vote
1
answer
1k
views
Arrayformula for running MAX of separate column
I'm trying to use ARRAYFORMULA in Google Sheets to get the max figures from a separate column only up to the row being calculated each time but can't for the life of me figure out how. It sounds more ...
CommunityBot
- 1
-1
votes
2
answers
51
views
Formula that calculates the difference between numbers
I'm trying to create a formula that would be able to calculate the difference between the numbers and writes it exactly as shown in this image: https://i.postimg.cc/W4cZDgD2/image.png
Full Example: ...
- 3,316
1
vote
1
answer
23
views
Countifs in between ranges
I'm struggling to pull below conditions together. I'd like to have a formula that will count values between 1 and 5 only when cell U13 contains the text 1-5.
=COUNTIFS(U13,"1-5")
=COUNTIFS(K2:K22,"&...
CommunityBot
- 1
0
votes
1
answer
24
views
Filtering by flight times in Google Sheets
I have flight delays ranging from hours ahead of schedule to delays of all durations. I need to countif any delay that is longer than 20 minutes. The values in the column take on this format: "-0:...
CommunityBot
- 1
1
vote
2
answers
154
views
Function to search columns and tally data
I am wondering if I can write a function to search a column in my spreadsheet for a specific name, then return x number of values within the row(s) that contain the name. For example...
M4 holds the ...
CommunityBot
- 1
1
vote
3
answers
32
views
How would I SUM column values while excluding any that precede the text string "reset"?
I have a spreadsheet where the hours of wear for pieces of equipment are logged:
Data is logged in new rows as additional wear-hours are put on the equipment.
From time to time, equipment is replaced,...
CommunityBot
- 1
2
votes
1
answer
371
views
When I insert a row, indirect does not update
I have a summary sheet and I would like that every time I add a new sheet everything gets referenced to the summary sheet. I have used indirect to do this, the only flaw is that once I add a row, the ...
CommunityBot
- 1
0
votes
1
answer
2k
views
Average value using different sheets on Google Sheets
I have a data set that's separated by year (so, the first year is Sheet1, the second year is Sheet2, and so on. There are 18 years in total). I want to average the data from different years. At first, ...
CommunityBot
- 1
2
votes
1
answer
1k
views
Conditional Formatting across a range of cells with a formula in Google Sheets
I have a calendar put into Google Sheets with the data put in as dates such as "9/24/2018".
I have a separate sheet with a data set that has rows that classes end such as "9/24/2018".
I want to ...
CommunityBot
- 1
0
votes
1
answer
82
views
How to filter an spreadsheet using filters + drop down feature in google sheets
I've created an spreadsheet and I'd like to filter the information in the rows using a drop down filter. I've managed to create the drop down menu in A3 but now I'd like to show the information in the ...
CommunityBot
- 1
2
votes
2
answers
170
views
How to make sequential list of months?
I want to fill column names with a sequential list of the last day of each month. For example, 3/31/2018, 4/30/2018, 5/31/2018, etc. Is there a formula that can do this? If cell B1 = 3/31/2018, I ...
CommunityBot
- 1
0
votes
1
answer
48
views
Google Sheets formula
I'm using Google Sheets make a spreadsheet with the name in column a, the company in column b and in column f, I need their email.
I have 3 companies, and they have slightly different email formats. ...
CommunityBot
- 1
1
vote
3
answers
2k
views
How to create a formula in Google Sheets that can sum specific cells based on multiple criteria
I am wanting to create a formula that will look at a dozen different cells and sum to the total value of those cells but some cells will not be numbers and I want to exclude those cells in the sum.
...
CommunityBot
- 1
0
votes
1
answer
127
views
Query Problem: Function QUERY parameter 2: NO_COLUMN: Col64
I have been using this formula in a document for quite some time and have had no problems with it. Now however when I opened it up, it suddenly stopped working. When I revert the document to an old ...
CommunityBot
- 1
3
votes
1
answer
3k
views
Format telephone numbers, keeping only digits
I have a spreadsheet that has phone numbers in the cells that are typed out like this:
+1(313)313-3333
and I need these numbers to all be plain and look like this 3133133333 (with no 1 in front).
...
CommunityBot
- 1
0
votes
1
answer
165
views
Find whitespace and replace with string from formula in Google Sheet
I have list of cities and I want to create URL for each of them in Google Sheet.
Scenario is most of the part of the URL is same only at the end city name is different so I created a formula
=...
CommunityBot
- 1
1
vote
1
answer
22
views
Jump to cell address mentioned in other cell
A1 shows address of last cell un a column (e.g. A572 or A577). Every time address will change if adding new row (there can be blank rows in between of rows with data).
I need a link in B1 to click and ...
CommunityBot
- 1
3
votes
1
answer
1k
views
What ticker symbol to use for the crude oil price?
Which ticker symbol can I use for the crude oil price for the GOOGLEFINANCE function?
Nasdaq Brent oil is "BZ:NMX"
https://www.nasdaq.com/market-activity/commodities/bz:nmx
Nasdaq Crude oil ...
CommunityBot
- 1
2
votes
5
answers
14k
views
Identifying a merged cell in Google Sheets without using a custom function
I am using Google Sheets and I am trying to find a way to identify merged cells without using a custom function.
For example, I need to determine (using only the built-in functions) whether any ...
- 11
3
votes
2
answers
2k
views
How can social hashtags be extracted from a cell?
Example:
A2 -> What a beautiful day! #sunday #summer
B3 -> #news Stay tuned for our next contest! #something
Desired output on B2 ->#sunday #summer
Desired output on B3 ->#news #...
1
vote
1
answer
47
views
How to categorize data according to the first n characters of its number?
Sorry for the confusing title, my problem is this:
I have a table of items, like this:
A
B
C
1
Number
Name
Category
2
100-150-135
Mike
-
3
150-150-133
Joe
-
In the same document, I have another ...
CommunityBot
- 1
1
vote
2
answers
65
views
Show each n-th column, hide others in Google Sheets
I have several big tables, and I know that each 4th column is interesting for me, i.e B,F,J,N, etc (The total number of columns is different in different documents)
Is there an easy way (some sort of ...
CommunityBot
- 1
0
votes
1
answer
19
views
How do I use REGEXTRACT to return grouped numbers from a string where the number of groups varies?
In Google Sheets, I'm trying to return numbers from within a text string.
The numbers are values of the property named either item_id or item_ids.
I've tried the REGEXTRACT function but I'm struggling....
- 3,316
1
vote
1
answer
35
views
Need To Randomize Selections From A List But Ensure Uniqueness
I'm building a tool to randomize setup for a very large modular card game. There are several lists from which I need to select 2 to 6 non-duplicate entries, and each list is on a separate tab from the ...
CommunityBot
- 1
0
votes
2
answers
806
views
Conditionally formatting duplicate values in Google sheets with exclusion criteria?
I'm currently working in a Google sheet where I use the following formula to catch duplicate values with conditional formatting:
=countif($B:$B,B2)>1
While this works on its own, I find that it ...
CommunityBot
- 1
0
votes
2
answers
30
views
How to get data from table to another when you can't re-order columns
Having trouble putting this into words, reading countless threads describing issues similar to mine, but not this exact one. So here's a super simplified version of what I need done.
I have numbers ...
CommunityBot
- 1
1
vote
1
answer
8
views
im trying to get a group of cells and there values repeat along a row. i have values entered from b5:bl5 and i need it to just keep repeating
Google sheets- entered values in B5:BL5 and i need it to repeat across row 5 all the way to og5. I need it to do it automatically. I have tried a scrip that doesn't work cause i don't know what I'm ...
- 3,316
0
votes
1
answer
28
views
SUM cost if Product ID is unique and matches multiple criteria
I have data in a sheet similar to the image. There are rows with duplicate Product ID information and I want to calculate the total cost of a unique Product that matches multiple criteria.
In this ...
- 3,316
0
votes
0
answers
22
views
Getting just month and year from a date [closed]
I have a data sheet in Google Sheets that has a date time column that I need to isolate just the month and year from in a mm/yyyy format. The original column looks like this:
27/09/2021 10:13
I used ...
- 1
0
votes
2
answers
298
views
How do I advance the column instead of the row when I copy/paste a formula?
The following formula references a cell in a separate sheet:
=('Form Responses 1'!B3)
If I copy that formula to the row below, the formula changes to:
=('Form Responses 1'!B4)
The cells I want to ...
CommunityBot
- 1
0
votes
1
answer
374
views
Extract Data from 2 columns if it matches condition
This is how my main data sheet looks like
And for any item that has customization, for e.g. - ORGANIC, I want to extract/copy just those data, the meal name & total units to another sheet.
And ...
CommunityBot
- 1
1
vote
1
answer
150
views
Can I populate Google Sheets cells adjacent to a target cell?
I have a maintenance tracker in Google Sheets for lighting fixtures. One column is a type of fixture "B", the column adjacent is the problem "C". I'd like to find specific fixtures by name and copy ...
CommunityBot
- 1
0
votes
1
answer
133
views
Conditional formatting with multiple names in list
Col A2:C15 contain a list of names. I need to enter those names in various combinations into one of 15 other cells on formatted like:
H3 = John, Steve, Bob, Dan
Is there a way to use conditional ...
CommunityBot
- 1
1
vote
1
answer
411
views
Google Sheets Query: Order By and accented chars
In a Google Sheet, if we have a column with accented character strings, the sort order of menu option "Data -> Order sheet by column" correctly ignores the accents (ordered column A in ...
CommunityBot
- 1
1
vote
2
answers
120
views
How to return a value from a row from the last column a text appears
I am having problems returning the correct value i need.
I enter data on this table.
And i want to return some values on the other table.
In that second table i want to return the last date his name ...
CommunityBot
- 1
0
votes
1
answer
293
views
Google sheet - highlihghting cell if in the same row other cell not empty
I'm trying to make a worksheet where I log shift hours.
I made columns with dates and to each date a row of hours
I want the date text to be green if in the same row a cell is not empty..
The code I ...
CommunityBot
- 1
0
votes
1
answer
1k
views
Generate "cards" with info in Google Sheets
I have a sheet for an event and I need to generate identification cards like in the image for the pieces on the contest, around 1000 pieces, so no way to manually do it.
I did a similar thing years ...
CommunityBot
- 1
-1
votes
1
answer
28
views
All Time Rolling Average based on string order with multiple conditions in the same column - Google Sheets
I'm trying to calculate the average $ amounts per category (X & Y) and their sub-categories (A,B,C,D,E) on a rolling basis based on past data that's on a string format. There are 2 types of ...
- 27
4
votes
3
answers
19k
views
Making all cells in a column links from the values within the cell? [closed]
I have a column of cells in a Google Spreadsheet with values such as:
512
2123
2342
3532
What I would like to do is convert all these into a hyperlink and keep the value as the link text:
http://...
- 44.6k
0
votes
2
answers
165
views
Is it necessary to use ArrayFormula to get the SUMIF function to work properly in Google Sheets?
I am trying to find the sum of values in a column based on the month in an adjacent column. For example, Sheet 1 has Three columns, one for student remark, one for payment date, and one for amount. ...
CommunityBot
- 1
0
votes
1
answer
19
views
Can the formula of a cell be conditional based on the dropdown selection of another cell?
Is there a way to create a formula where D9 is included/excluded from the formula based on the answer to the dropdown selection of D13 (Financing Repairs? Yes/No)?
Spreadsheet
I've spent hours ...
CommunityBot
- 1
0
votes
1
answer
464
views
Google sheets join arrays
What I'm trying to do is to join few arrays of dates.
For start, I have start date and amount of months I want to generate.
Which I did by using =ARRAYFORMULA(DATE(YEAR(A2), MONTH(A2) + SEQUENCE(C2, ...
CommunityBot
- 1
0
votes
2
answers
38
views
"IF" Functionality Inquiry
So I am trying to figure out how to whenever it's a "Y" and "INCOMING", then it will equal a dollar amount.
So as if a + b = 1 then equals $35
=COUNTIFS(F3:F100,"Y",B3:B100, "INCOMING")
I did it a ...
CommunityBot
- 1
1
vote
3
answers
34
views
'Large' 'If' Formula Help
I am looking for a formula I can put into Google Sheets that finds the 1st, 2nd, and 3rd largest values in column B, but only for rows that also contain the number 1 in column A. Problem is, column A ...
CommunityBot
- 1
0
votes
1
answer
150
views
How to make formula based off a withholding table?
I have been attempting to make a formula to easily calculate federal income tax rates. The picture attached is the withholding table that I've transferred onto a sheet. These are the thresholds I am ...
CommunityBot
- 1
0
votes
1
answer
25
views
Does it help to close a Google Sheet when it's slowed down due to "Calculating formulas"?
It's known that "Calculating formulas" in Google Sheet disaster is a problem in general, but I have a specific question - does it help at all if I or other concurrent users exit the sheet or ...
CommunityBot
- 1
0
votes
1
answer
477
views
Conditional Formatting in Google Sheets doesn't work in certain cells with custom formula
Custom Formula doesn't work on certain cells.
A little background, I am making a timezone sheet for a discord where someone input the time they wanted and checks who is and is not available at that ...
CommunityBot
- 1