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
1 answer
8 views

Google Sheets formula to format substrings of column concatenation?

If I have a spreadsheet with columns for author, year, title, publication, etc, could I add a column that concatenates and formats those as a citation for a bibliography? It would be something like ...
user avatar
  • 255
1 vote
1 answer
13 views

Regexextract to get a text in the middle of string

SAMPLE SHEET I have data in a column in this format: 1 P01 - Product Name here - PAID IN FULL 2 P02 - Product2 Name here - PARTIALLY PAID 20 1 P03 - Product2 Name here I've been trying to ...
user avatar
  • 191
1 vote
1 answer
8 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 ...
user avatar
0 votes
1 answer
19 views

How to find the next lowest available number from a list on Google Sheets

I'm looking to find a formula which I can use, to show, the next lowest available number for a range of Asset Tag 1 - 500 As an example if 1 has been used the next available number will be 2. However, ...
user avatar
0 votes
1 answer
20 views

Combine an array formula with query function

I want to know if there is a way that I can combine any of these 3 formulas. Sample Sheet I have in col. A3, this extracts the info in Data tab, and splits into rows: =arrayformula(substitute(...
user avatar
  • 191
1 vote
0 answers
12 views

Why 2 columns of the same value give different total?

Sample Data in Result Sheet I have a formula in A2 that extracts data & splits it into unit & product (A & B). I used the SUMIF formula to give the the total of each separate set of ...
user avatar
  • 191
0 votes
1 answer
19 views

Why is COUNTIF returning a 0? It should be returning a value of 8

I have a fairly simple countif function that counts how many items are 90 days old or more. =COUNTIF(PROCFilter!H2:H,">=90") But all this is returning is a value of 0, when I know that ...
user avatar
2 votes
1 answer
254 views

Text result of JOIN is longer than the limit of 50000 characters

Sample Sheet I want to split a column of data, where there are multiple lines in a single cell. I want these split into rows with a formula. I've got the JOIN formula set up for that and it works ...
user avatar
  • 191
0 votes
1 answer
24 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 ...
user avatar
  • 1
0 votes
1 answer
22 views

Is there a way to return more than one row from multiple matches in Google Sheets?

I'm trying to find a way to fill two columns in one table with two rows of data from another table based on matched criteria. To be more specific, A3:D contains the data I'd like to pull from. I'm ...
user avatar
0 votes
1 answer
42 views

Google Sheets: Making a function to find a number and impute it once

I am trying to make it possible to find the highest number under 30, and than place it in another cell. Then that cell will not change when another number becomes higher. For example if the highest ...
user avatar
  • 1
1 vote
1 answer
29 views

How to assign cell values based on options from another list

I am sure there is a simple formula for this but I have not been able to find a solution anywhere. I am trying to fill a cell based on a list in a different tab. In my main sheet, each row has a ...
user avatar
  • 11
2 votes
1 answer
21 views

Show sorted elements based on priority

can you please help? I would like to autopopulate column C with values in column B sorted by the priority in column A. Column A: Project names manually ordered by priority. Most important in top. ...
user avatar
  • 23
0 votes
0 answers
24 views

Google Sheets - Autosort Script issues

I am trying to sort data descending based upon column B (2) which has different numbers in each cell. EG: B8 = 99, B14 = 70, etc. I'm intending for it to autosort and do it onEdit. I'm not good at ...
user avatar
  • 1
0 votes
1 answer
32 views

Is there a way to create a list of values from a certain column based on referenced criteria in two rows?

I'm trying to find a formula that will fill in two columns with data from another sheet based on values from a dropdown that correspond to header rows from the original dataset. I have Sheet1 that ...
user avatar
0 votes
0 answers
8 views

Sum by Date but Ignore Timestamp

I am trying to auto sum day by day as data is automatically added to the sheets doc via Google Forms but keep getting errors and I think the time stamps are throwing the calculation off, so I'm trying ...
user avatar
1 vote
0 answers
13 views

Formula to automatically sort range of data into groups in different columns by colour?

I am an educator trying to find a way to automatically group students into broad groups of 'below' 'at' and 'above'. My data is more specific (i.e. numerical value between 0 and 30) and I would like ...
user avatar
0 votes
0 answers
42 views

Sheets: Collaborators inserting their names as values into selected cells

Previous post marked closed due to being unfocused, so I pared it down. Not sure if this is now good enough, if not please can someone comment to explain so I can correct it? I'm trying to find a way ...
user avatar
  • 1
0 votes
1 answer
15 views

Counting different types of text and producing specific text outcome

So I'm trying to create a tracker where I can gauge a pass/warning/fail status for each category. Under "Report Status", I wanted a function that can put out the text "Fail" if one ...
user avatar
1 vote
1 answer
41 views

How to make a formula expand to new rows when new data is added?

I've got a set of data, a column of numbers in this case, that I want to automatically calculate moving averages for when new data is entered. Currently I'm doing this by using =TRIMMEAN($A$2:A2, 0.15)...
user avatar
0 votes
1 answer
22 views

How to create a linked sheet that checks a box for a day if found and inserts text from another column

I'm trying to create a sheet in Google Sheets for my team that shows me what runners have run on what day of the week. Their activities are automatically imported via Zapier with dates from Strava ...
user avatar
0 votes
1 answer
22 views

Flattening the values (not formulae) of a summary sheet

I work at a company that owns a lot of film gear that they bring on freelance shoots. I'm trying to create a simple, streamlined way for them to notate what's brought on each shoot so that they don't ...
user avatar
3 votes
1 answer
126 views

Formula for matrix (dates in descending order)

As a sequel to this question Formula for matrix (dates in ascending order) how would the formula (=IF($B2="Apple",A2-SORT(FILTER(A:A,B:B="Pear", A:A<$A2),1,FALSE),"") ...
user avatar
  • 199
3 votes
2 answers
127 views

Formula for matrix (dates in ascending order)

Firstly, apologies for this question but I couldn't really wrap my head on how to do this (ultimate aim is to put it in app scripts) Let's say I have a table that is updated daily showing the fruit ...
user avatar
  • 199
0 votes
0 answers
18 views

How to sum a column of variable length, starting at the first cell below the nearest blank cell

Can anybody help with this please? I need to sum a column of figures. The number of rows in the column will be variable, but will be a contiguous range, with no gaps. The range for the sum will start ...
user avatar
0 votes
1 answer
18 views

Google Sheets - Is there a way to filter values but keep them in the respective rows they came from?

I'm looking for a way to filter values based on another column but keep them in the same rows they came from. Here's an example I created manually of the correct output: The goal here is to have a ...
user avatar
1 vote
2 answers
32 views

Google sheet, from 2 lists to flattened table

I have two independent columns with data. How could I use that data to then produce a flattened table like this:
user avatar
  • 113
1 vote
2 answers
30 views

Google sheets function to filter out lowest associated cells

I was wondering if there's a way to use a function (maybe Filter?) in order to sort this data such that column K only contains one of each number, and the highest test associated with it in column L. ...
user avatar
0 votes
1 answer
32 views

Match value of cell if in between range and text match

Desired outcome: I would like to return a value in column C (column D is the values I'd like the formula to return) - the value in column C should return on the criteria it matches the product name in ...
user avatar
0 votes
0 answers
21 views

Why does my formula stop working after 1000 rows in Google Sheets?

I am working in Google Sheets and have the following formula: =ArrayFormula(IF(ROWS(A$5:A5)>$C$2,"",INDEX('Annual Schedule'!$A$1:J$1852,SMALL(IF(('Annual Schedule'!$A$1:J$1852>=$A$2)*('...
user avatar
  • 1
0 votes
2 answers
29 views

COUNTIFS in Google Sheets

I have been battling to get the COUNTIFS formula right for the below. I need to count the number of events per day. How many times did the event happen between 2021-04-20 14:24:54 and 2021-04-20 19:16:...
user avatar
0 votes
1 answer
38 views

What formula can I use in order to calculate Z-Score within a specific rolling range?

Doing some sports statistical analysis for fun. Wonder if any of you guys can help me. This is the sheet I'm working with. I'm sure you can see how I'm already experimenting with this in the Z-Score ...
user avatar
  • 3
1 vote
1 answer
20 views

Simplify formula that checks student year level and mark to a letter grade

I am writing a Google Sheet that checks a student's year level, then assigns a letter grade based on their mark in an assessment. If there is no mark, it is designed to leave the cell blank. The ...
user avatar
0 votes
1 answer
17 views

How to make column go reversibly blank with custom validation and conditional format rules

I was able to use scripts editor to make some conditional formatting. Would like the option of covering up the answer column, column B reversibly. Would love some help with a script or how to do this. ...
user avatar
0 votes
1 answer
15 views

Extending a formula (VLOOKUP) to the whole column

I created a VLOOKUP formula inside a Google Sheet, I have 130000 lines and need to extend the formula to the end of the column.
user avatar
  • 1
0 votes
1 answer
24 views

How to add month in Sheets cell that corresponds with formula in below adjacent cell?

I am making a cash flow projection worksheet in Google Sheets. I am using this formula to give me sequential weeks (greatly relieving the tedious entry) =ARRAYFORMULA(JOIN("-",TEXT({"5/...
user avatar
0 votes
1 answer
23 views

Formula to get the data from a source and use it while leaving some defined patterns as spaces

I'm trying to create a formula so that Column E will output the Employee names on Column A. I've provided an image below as my example. As you can see, there is a pattern of 4 vacant after every ...
user avatar
0 votes
1 answer
23 views

Have imported spreadsheet from Excel to Sheets but this formula doesn't work

I have text in three columns and need to know how many times a particular name crops up =SUM(COUNTIF(INDIRECT({"d6:d37","I6:I37","n6:n37"}),"peter")) any ideas?
user avatar
0 votes
2 answers
34 views

How do I automatically record finish position?

I've created a spreadsheet to keep track of the progress that my friends are making in a competition we're doing. I would like google sheets to automatically update which one of us comes in 1st, 2nd, ...
user avatar
1 vote
1 answer
29 views

SWITCH statement in Google Sheets Doesn't Compare Strings Properly?

I have a very simple SWITCH statement in Google Sheets: =switch( D2,"",B2, D2,"A","A", D2,"B","B", "Default" ) If I put ...
user avatar
0 votes
1 answer
33 views

Create a dependent drop-down menu for an entire column

The aim: For my work, I would like to create a google spreadsheet where we can document our errors in processing the order transactions. For this I need two drop-down menus; the first shows the ...
user avatar
0 votes
2 answers
33 views

Google Sheets: Sum till particular date

I have time-series data that follows no predictable entries. For example as below. Date Units 01 Jan 2022 1 05 Jan 2022 2 08 Jan 2022 1 30 Jan 2022 1 04 Feb 2022 1 10 Feb 2022 1 13 Feb 2022 2 ...
user avatar
1 vote
1 answer
15 views

SUM Function Works Fine in One Sub-Sheet(?) but Not in the Other?

I have a SUM function that works fine in one sub-sheet(the tabs default titled sheet1, sheet2, etc. on the bottom of the screen) but decides not to work in the other. This is what the function looks ...
user avatar
0 votes
0 answers
57 views

Help with SUMIFS formula with horizontal and vertical criteria + REGEXMATCH

I need some help trying to sum totals for different events based on horizontal and vertical criteria. I've tried doing different combinations of SUMIFS + INDEX + MATCH formulas, including transposing ...
user avatar
0 votes
1 answer
27 views

"Multiply" (Concatenate) Text Columns in Google Sheets

Simple question that I can't seem to find a solution for. I have text data in two columns like Name Num a 1 b 2 and I would like to produce a single formula if possible to output a 3rd column ...
user avatar
  • 1
0 votes
1 answer
41 views

Google Sheets - countif with dynamic range

In sheet1 have a column with a list of IDs, each with a set of statuses: ID Apr May Jun 1 Load Load Complete 2 Load Complete NA 3 Load Load Load In a separate sheet, sheet2 I ...
user avatar
  • 101
0 votes
0 answers
6 views

I need help how to calculate estimated hours spent per article by the total number of working hours and total numbers of articles completed

This is what my sheet looks like. I need to get the average mins spent by the editor per article. I have the total number of working hours and the total number of articles completed. I hope someone ...
user avatar
0 votes
1 answer
20 views

How can i make a formula to determine payment types

I am having trouble nesting and using multiple criteria. I want a column to determine if payment type was CASH, CHECK or WAITING depending on if the cells for Check# and Payment are containing data. ...
user avatar
0 votes
0 answers
14 views

How to trace cells from a formula when the formula uses cells form a different sheet?

I have a complicated budget spreadsheet which I update regularly. I have one sheet which is essentially just my bank ledger where I categorize all transactions & purchases. And then I have another ...
user avatar
  • 1
0 votes
1 answer
28 views

How do I find the appearance rate of one list of cells in another list of cells?

I imported all of the data from my iTunes library into a Google sheet for my own amusement, and I want to know by album how many songs from each album I thought were good enough to put in my "...
user avatar

1
2 3 4 5
37