Questions tagged [formulas]
Formulas used in spreadsheets edited by web applications like Excel Online, Google Sheets, among others.
1,827
questions
0
votes
0
answers
32
views
Is there a cell formula or macro that I can use to reorganize data on subsequent rows and transpose it to subsequent columns?
I have a large spreadsheet (~1500 rows) that I am trying to manipulate the format of. I have created an example of what my data looks like and shown it here in the first image. I would like it to be ...
- 9
0
votes
1
answer
23
views
How do i utilize IF functions (or others) in order to create a simple username/password in google sheets?
On cell A3, there is a username input (i.e max or dave), on cell B3, there is a pin input (i.e 1234 if its max, or 8640 if its dave), how would you go about creating a formula in order to satisfy ...
-1
votes
2
answers
53
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: ...
- 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....
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 ...
- 19
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 ...
- 11
-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 ...
- 9
0
votes
1
answer
39
views
Rolling Average based on string order with multiple conditions in the same column in 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 ...
- 9
0
votes
1
answer
29
views
Does Google Sheets have a function to insert X number of Y characters within a cell? [duplicate]
I currently have a spreadsheet which requires a variable number of line breaks within a cell based on input data from another cell.
Long story short, I currently am using a brute force method to ...
- 3
0
votes
1
answer
21
views
Stuck at understanding how duplicate prevention formula works in Google Sheets
Many blog posts and educational sites tell us to use =countif($A$2:A2,A2)=1 in the selected range.
However, they don't explain it. And for me it doesn't work and since I can't understand it I can't ...
- 1,889
0
votes
1
answer
16
views
Automatic way to select a range for a formula based on the date
I am creating a calculator where the user describes a list of tasks in Activities!B2:B, and in C2:C they rate 0-5. To separate each day, I've used a date for each task in A2:A (stylistically separated ...
- 15
1
vote
0
answers
23
views
ArrayFormula Trouble pulling data for just a year or just last 15 days No understanding how to do [closed]
Google sheet to see my problem
https://docs.google.com/spreadsheets/d/1e3sjPNDJ3crJVQIn9L-lKllXP0wvc9NC22cnjxCveEs/edit?usp=sharing
See separate tab (help)
See all Yellow Boxes........they have the ...
1
vote
1
answer
61
views
How to combine ARRAYFORMULA + QUERY?
"Please advise on how to correctly input the formulas to get the desired result as shown in the picture.
Task:
https://docs.google.com/spreadsheets/d/14tpPliM_3b7RIikSimvdzLjkFq3CexPKbtOEuD4aIKU/...
0
votes
1
answer
21
views
Using created filter to filter specific value base on other cells value
I can't seem to figure this out. I am wonder if it is possible to create a filter for a column, and then filter that column based on another cells value. So I have created a filter for A column which ...
- 101
0
votes
0
answers
11
views
Data validation with 2 conditions [migrated]
I am looking for a conditional formatting formula that validates two conditions from another sheet.
I have Sheet1 and Sheet2: Sheet2 has daily records of workers such as time in and off, Sheet2 ...
0
votes
1
answer
44
views
How to extract date and time in Google Sheets [closed]
I'm trying to extract the time and date from column A.
In cell A1 for example is written: October 25, 2023 at 07:23PM
In cell A2 for example is written: October 26, 2023 at 01:01AM
I'm trying to ...
- 3
0
votes
0
answers
16
views
How to fill a matrix pattern using autofill? [duplicate]
In a google sheet, I have some numbers in column A and in row 1.
I want to fill the matrix as follows:
A
B
C
1
f(B1, A2)
f(B2, A2)
f(B3, A2)
2
f(B1, A3)
f(B2, A3)
f(B3, A3)
3
f(B1, A4)
f(B2, A4)
f(...
- 101
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,...
- 11
0
votes
0
answers
31
views
How can I return values in one cell based on a range of values in another cell? [duplicate]
I am creating a spreadsheet for a racing league and would like driver points to be automatically calculated based on finishing position.
Each finishing position from 1st to 10th has an associated ...
Matthew Newton
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 ...
- 1,206
1
vote
0
answers
36
views
Lookup a specific series of cells by typing in a number? [duplicate]
I help run an RP server on discord, and we use google sheets for many of our mechanics. A new mechanic I am looking to implement is a fun little thing where they can perform an action to be given a ...
- 11
1
vote
1
answer
96
views
How to count wins, losses and ties based on multiple conditions
I'm very new to Google Sheets.
I have a sheet where I record the outcomes of games played.
Each row represents a single game
Column A, Map, stores the name of the map that was played.
Column D, ...
- 13
1
vote
1
answer
57
views
If a cell is left blank, how can I get the formula result be blank
I am currently looking for a little help with Google Sheets.
I currently have three columns, B 'DATE' - C 'TIME' - D 'Dropdown list - BUY, Sell, Proforma'
So far, I have managed to do what I needed, ...
- 11
-1
votes
2
answers
42
views
Google Apps Script for gradebook sharing: Create duplicates of current sheet, one per row of data, with importrange of the source and header rows
The goal is to create a workaround to the typical problem of not having per-row privileges. I want students to only see their row of the grading document. Typical solution is to share another sheet ...
0
votes
1
answer
38
views
Return averageifs of durations formatted to display only minutes
I have a column of durations formatted as "hh:mm:ss".
I use AVERAGEIFS to return the average of the durations that also match a particular date.
For example, I have the following formula in ...
user317487
0
votes
1
answer
28
views
Selecting only rows corresponding to Thursday in a column of dates
I've the following dates in a column and I'd like to select only the rows corresponding to Thursday in Google Sheets. Could you tell me how to get this result, please?
- 202
0
votes
1
answer
49
views
Formula to return count of duplicate dates based on multiple criteria
In Google Sheets, I have a column of dates in A and checkboxes in column B (Redeemed).
I want a formula to count, for each unique date, how many times it appears in the column where the following two ...
- 19
0
votes
1
answer
40
views
How to test one cell of numerical data against many numerical filters?
I have 50-digit long binary IDs (whose numbers correspond to qualitative data of a two-consonant long string) that I want to filter based on partial matches to what I call filter IDs, which are also ...
- 181
0
votes
1
answer
50
views
Google Sheets time in one cell cost in another
In Google Sheets, I want to be able to calculate a dollar amount based on the time of day entered.
I'd like to enter the time in one cell and have the formula in another cell calculate the cost.
These ...
- 1
1
vote
1
answer
78
views
Clear cell value if cell in adjacent column is blank
In Google Sheets I have two data columns, S and T.
What formula would clear the value in T if the value in S is not RUN?
Sample Data
Before
S
T
1
...
2
votes
1
answer
58
views
Looking up two criteria in a table, one vertical, one horizontal and finding a value
I have a large table of data.
Column A is names
Columns C-I are monthly commissions. (Column C is March 2023, Column D is April 2023, etc)
Columns C-I are going to change regularly as we are pulling ...
0
votes
1
answer
142
views
Insert space after every character of a text string
I'm trying to create a formula that takes a text string of unknown length and returns it after inserting a space between every character.
For example, if A1="FOO" then the formula should ...
1
vote
2
answers
57
views
Formula that looks for any matching substrings between two cells
In Google Sheets, I'm trying to compose a formula that compares the substrings in two cells.
Substrings in each cell are separated by a space.
The formula should return TRUE if any substring in one ...
0
votes
1
answer
23
views
I have multiple unequally sized rows that I want to combine into a single matrix. How can this be done?
As stated by the title, I have various rows which do not contain equal amounts of data. Furthermore, the size of each row changes per user input on another sheet. I want to combine all these rows into ...
- 181
0
votes
1
answer
32
views
Stuck with a formula for generating boolean strings
I'm trying to make a formula on Google Sheets that would help me utilize all added keywords from column B into a sourcing boolean string that would include OR and "". (Example "Keyword1&...
1
vote
2
answers
42
views
Runner's time generates error when divided by distance run regardless of the number format
I coach XC and want to divide my runners' times by the distance they run to get an "average mile time." I enter my times in column C and put =C3/1.3 in column D, but I get this:
Error
...
- 13
0
votes
2
answers
34
views
How to filter combined search outputs with OR logic
I have a working Google Sheets filter formula that combines different searches of the same range.
In the formula below, I'm searching column AA in the REFS worksheet.
I enter search terms into any or ...
- 1
1
vote
0
answers
23
views
How to convert a Splitting formula from Excel to Sheets (given that Textsplit is missing)
I'm attempting to copy the function of the following Excel sheet into Google Sheets and running into a snag.
https://1drv.ms/x/s!ApTGA-odt1HWhzGjuVHPn9bJyr5G?e=ZxtUCi
It takes the list from A2 chops ...
- 13
1
vote
0
answers
20
views
Calculating multiple means and then putting them in a graph but more complicated [closed]
Me and my sister love playing the sims and we want to analyze how much each painting you can make will earn.
We made a spreadsheet containing the type of painting, the cost of the painting, the mood ...
- 11
1
vote
0
answers
22
views
How do I calculate the total amount per category by multiplying amounts with distinct multipliers? [closed]
I know how to program in python/go/bash, but I'm a super noob when it comes to excel or google sheets. Sometimes they are super helpful, but I often find it difficult to figure out what's an easy ...
- 19
1
vote
0
answers
26
views
Dynamic data, COUNTIFS formula value 0 [closed]
I have started creating my first dinamic report, but I bumped into some problems.
I have a source sheet with project group data in Column Z and month-year data in Column AO. My goal is to generate a ...
- 11
1
vote
0
answers
116
views
Data validation range with ROW() in google sheets [closed]
Here is a link to a test spreadsheet:
https://docs.google.com/spreadsheets/d/10pofQ3TJM1FvBEPdvDq73-dm7hafhNwAbqVkfekofS8/edit#gid=0
The dropdown menus in column C are dependent on the selection in ...
- 135
1
vote
1
answer
98
views
IF YES then copy another cell [duplicate]
I have been using Google Sheets and getting things done slowly. I am using an extension called Autocrat and have been doing ok with it. (Not sure if there is a better alternative.)
My current problem ...
- 11
0
votes
0
answers
15
views
How to link a dropdown list to specific phrases?
I'm creating a dropdown list of names that are matching to the company. For example, this is the list:
Name
Company
Name
Corresponding
Macfee-John
Macfee
Macfee- Jane
Macfee
Aventis- Doe
Aventis
...
- 1
1
vote
1
answer
24
views
Workplace scheduling spreadsheet help, find (partial) duplicate in range
I work at the front desk of a tutoring and test prep center whose master schedule is a simple Google Sheets file with the schedule for each of our M-Th and Sat. sessions sharing the same spreadsheet. ...
- 13
1
vote
2
answers
111
views
How do I paste formulas from a plain text file into a Google Sheet?
When I copy a formula from a text file into a Google Sheet, it shows up as plain text in the sheet rather than being used as a formula.
For example if I copy "=2+3" (not including the quotes)...
- 21
0
votes
1
answer
23
views
I moved a chart to a seperate sheet, do I have to reference the original sheet in front of every cell reference?
The name of the sheet I moved the chart from is Systm. Can I get rid of the redundancy?
=ARRAYFORMULA(COUNT(IF(({
'Systm'!$K$294:$K$339, 'Systm'!$BE$294:$BE$339}= 4 )*({
'Systm'!$L$294:$L$339, '...
- 99
0
votes
1
answer
40
views
QUERY function to return records with multiple criteria
I have a dataset on Google Sheets similar to below:
Name
Team
Account
Date
Jennifer
East
Meta
5/1/2023
Steve
East
Alphabet
4/1/2023
Claire
West
Google
4/23/2023
Roger
West
Apple
6/23/2023
Bob
...
- 19
1
vote
1
answer
32
views
Google Sheets QUERY function to return records with multiple filter criteria based on cell reference
I have a dataset on Google Sheets similar to below.
Name
Team
Account
Date
Jennifer
East
Meta
5/1/2023
Steve
East
Alphabet
4/1/2023
Claire
West
Google
4/23/2023
Roger
West
Apple
6/23/2023
Bob
...
- 19
1
vote
1
answer
45
views
How do I add values from different tabs into one table efficiently?
This is likely an easy feat for those how to work Sheets but I have no idea how to do it myself. I am looking at a quarterly report of airline complaints, where the source publishes one csv per ...
- 13