Questions tagged [formulas]

Formulas used in spreadsheets edited by web applications like Excel Online, Google Sheets, among others.

Filter by
Sorted by
Tagged with
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 ...
reepjk23's user avatar
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 ...
ZenLife18's user avatar
-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: ...
Ellie's user avatar
  • 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....
user319422's user avatar
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 ...
Elaine's user avatar
  • 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 ...
Charlie's user avatar
  • 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 ...
pfnanc's user avatar
  • 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 ...
pfnanc's user avatar
  • 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 ...
G0vnah's user avatar
  • 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 ...
Saeed Neamati's user avatar
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 ...
Louis S's user avatar
  • 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 ...
jim groninger's user avatar
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/...
Сильный Могучий's user avatar
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 ...
Linkinpark's user avatar
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 ...
Louis Cerato's user avatar
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 ...
Dennis 's user avatar
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(...
whoisit's user avatar
  • 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,...
Nick Marker's user avatar
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 ...
user avatar
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 ...
LWC's user avatar
  • 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 ...
Raayven's user avatar
  • 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, ...
Lunar's user avatar
  • 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, ...
Steve's user avatar
  • 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 ...
whatsnewsisyphus's user avatar
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 ...
user avatar
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?
Gennaro Arguzzi's user avatar
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 ...
Elaine's user avatar
  • 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 ...
Ylahris's user avatar
  • 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 ...
Michelle's user avatar
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 ...
Sanjay Devani's user avatar
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 ...
Lisa Masquelier's user avatar
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 ...
Patrick Hennessey's user avatar
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 ...
Patrick Hennessey's user avatar
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 ...
Ylahris's user avatar
  • 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&...
MaestroGGP's user avatar
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 ...
Kevin Schulz's user avatar
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 ...
Charlie's user avatar
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 ...
Vexel's user avatar
  • 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 ...
Riley's user avatar
  • 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 ...
kittydoor's user avatar
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 ...
Mima 's user avatar
  • 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 ...
fred's user avatar
  • 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 ...
Steve's user avatar
  • 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 ...
Jim's user avatar
  • 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. ...
Kyle O'Brien's user avatar
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)...
jefolicr's user avatar
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, '...
XCATHADOR's user avatar
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 ...
Elaine's user avatar
  • 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 ...
Elaine's user avatar
  • 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 ...
bernicla's user avatar

1
2 3 4 5
37