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
0answers
6 views

Help with a Google Sheets formula for Inventory Tracking

Wanted to get some help with finding the correct formula to input into a cell in google sheets. I need to determine when we are getting low on product and when it is recommended to order more. I ...
0
votes
0answers
11 views

Is there any way to set different formulas for multiple ranges all at once?

In my question Is there any way to set different values for multiple ranges all at once?, I found out how to set different values across dis-contiguous cells all at once, and we developed a good ...
0
votes
0answers
6 views

Moving Rows of Google Form Responses to a new Sheet based on a specific answer in a column

I have a Google Sheet collecting responses from a Google Form. Based on answers in Column C (Col3), I would like the entire row imported to a completely different Google Sheet. I figured out a ...
0
votes
0answers
9 views

Change entire sheet information based on input of one cell

I'm trying to create a function on a secondary sheet to monitor which of my blogs are getting the most traffic. Currently my secondary sheet has a formula that works to show me blogs that have over ...
0
votes
0answers
15 views

Logic Dependent Check box/Tick box (i.e. a check box that is only usable if criteria are met)

Simply, I would like to have a column of tick boxes/check boxes that can only be ticked if another column has a corresponding true value. I thought perhaps custom data validation would be the way to ...
-1
votes
0answers
16 views

Display only last 4 character in Google Sheets for privacy

Display only last 4 character in Google Sheets Current situation: I have a scanner which will scan customer id. The id number will be in google sheet. However, on scanning I do not want google sheet ...
0
votes
1answer
14 views

Google Sheets: Formula to get data from other tab

I have a Google Sheet to get an overview of all workshops/events we are hosting. There is a tab INPUT. There is a column for Date, Month, Event, Location, Presenter, Language. The column month is ...
0
votes
1answer
22 views

Why is this Google Sheets formula counting blank cells?

I am new to Google Sheets but really am only using it for simple tasks - yet I have encountered an issue that I cannot seem to resolve. I will start with an image of my test Google Sheet below: If ...
1
vote
1answer
44 views

Getting every combination of 4 items from a unique list

I have a list of items that looks like this : The goal would be to get every unique combination of 4 of those items. The combinations are unordered so ABCD, BADC, DBAC, etc. are considered the same ...
1
vote
1answer
20 views

In google sheets, want to copy cell values from 1 sheet to another if a specific cell has any text

I have this working well: =if('Lee''s sheet'!$AP2="x",'Lee''s sheet'!B2,"") which will populate cell values into a cell, from 1 sheet to another if there is an "X" marked in column AP. I want to ...
1
vote
1answer
20 views

google sheets, making a dropdown list based on user input

I am working on a scheduling system for a very specific process. I am going to be sending out some shifts to my employees, and based on the result of their input, I want to create a dropdown for the ...
2
votes
1answer
25 views

How to generate standard competition ranks (not dense) for my Google Sheet?

What I have is a leaderboard sorted according from high to low scores. I would like to assign standard competition ranks (1224) formulaically. All 7 people who have the highest score (30) should be ...
1
vote
1answer
39 views

My formula in Google Sheets doesn’t add up to the same total in Yahoo?

Link: https://docs.google.com/spreadsheets/d/1KcYqRRqxZ3GWNAIUWgg9AGraTAzkrVxkDrk4ZsI5axY/edit?usp=sharing So I do sports spreadsheets for fantasy sports. I’m filling out my baseball spreadsheet ...
0
votes
1answer
27 views

How can I comment out parts of a formula in a Google Drive Spreadsheet?

How can I comment out parts of a formula in a Google Drive Spreadsheet? In PHP you can comment a line of code using a # what is the syntax to do the same in a Google Spreadsheet? I want to use this ...
0
votes
0answers
25 views

Google Sheets Array formula

=ArrayFormula(IF(ISBLANK(Input!$B4:$B)," ",(INDEX(Input!A4:A, SMALL(IF(COUNTIF($A$3,Input!$B4:$B), MATCH(ROW(Input!$B4:$B), ROW(Input!$B4:$B)), ""), ROWS(A6)), COLUMNS(Input!B4:B))))) The result is ...
0
votes
1answer
15 views

Multiple IF(AND) statements to determine Pass/Fail

I'm using Google sheets to try to generate a Pass/Fail in a cell based on two different parameters; If (R2) is less than 85% AND (I2) equals "T1","T2, or "T3" I need a fail generated. similarly, for ...
0
votes
1answer
13 views

Counting with Query

I am using queries, in order to create a dashboard, =query('Usuarios icorp'!I2:M522; "count(F) when F contains '"&B3&"' ", -1) I am having issues with my query function. I want to count how ...
2
votes
3answers
39 views

How do I get the address of a range with a formula?

Let's say I have a range called WidgetTypes in cells C5:M5. I want my formula in cell A1 to return C5:M5 or $C$5:$M$5. What formula goes into cell A1? cell("address",WidgetTypes) returns $C$5 but I'...
1
vote
1answer
21 views

How to calculate the following mathematical expression in Google Sheets?

I am trying to calculate the total money that can be made from dollar cost averaging every year using Google Sheets. For example: how would I evaluate this?
1
vote
1answer
26 views

“Calculating formulas” in Google Sheet disaster

I have a worksheet with 13 sheets. the largest one has 2000 rows. I have already deleted all the possible blank rows i could. I have also read all the guides on how to make a Google Sheet faster and ...
0
votes
2answers
12 views

Opposite of SWITCH function? (i.e. enums in Google Sheets)

In programming, I could use a switch/case statement to do something like this: func animalNameFromType(animalType: Int) { switch animalType { case 1: return "dog" case 2: return "cat" ...
1
vote
0answers
49 views

Insert image to spreadsheet from google drive folder, png image dose not show, why?

Update It is now working,i dont know if google fix it or what. But if i use the image function and use the script below to find the id from the folder it works. Here is my final text if someone would ...
0
votes
1answer
10 views

How do I advance the column instead of the row when I copy/paste a formula?

So pretty simple thing I am doing, pulling data from a form and putting it in a template. =('Form Responses 1'!B3) is what I am doing, which works fine for my needs, however when I copy and past ...
0
votes
1answer
26 views

Is there a better way or more efficient way to write this IF statement? [duplicate]

I need a way to get the return number to increase by "1" after every increment of "10". Is there a more efficient way to do this other than: =SUM( IF(AC11<10,0, IF(AC11<20,1, IF(AC11<30,2, ...
1
vote
1answer
24 views

How to show the current day of the week in Google Sheets

I have a Google Sheets that works as my To-Do list, and I want to make a cell that displays the current day of the week in text and updates itself every day. For example, Monday or Friday.
1
vote
1answer
22 views

Google-sheet IF function not working

I need a formula in Google spreadsheet that will: +1 when the value is >=5, +2 when the value is >=10, +3 when the value is >=15, +4 when the value is >=20, +5 when the value is >=25 ...
0
votes
1answer
27 views

Aggregate Formula in Google Sheet using wildcards

I'm trying to copy and paste an excel formula I use to categorize data into Google Sheets and struggle as the formula doesn't work. I have two sheets: The Referral sheet contains all the referral ...
3
votes
1answer
42 views

Filter in Filter function Google sheets

I have a Sheet with the prices of some products Each column is a parameter. How to use the FILTER() function in order to filter by multiple conditions In this case I need to filter and see only ...
2
votes
2answers
72 views

Query or Filter formula to 'search for' and 'return' multiple words

I am a basic user of Google Sheets and need help creating a new formula. I am trying to filter a worksheet so that it returns particular information into another sheet. The filter I have created is ...
1
vote
1answer
26 views

Formula Parse error

Trying to use this formula in Google Sheets but getting a #error! parse error. Any help as to what may be wrong in the above formula =if(AND(B4="YES",G4=0),(ROUNDDOWN((AVERAGE(I4,K4,M4,O4,Q4,S4))*35%*...
0
votes
1answer
21 views

Formatting a cell based off of the rest of the row

I have a spreadsheet keeping track of the various documents that students of mine have been turning in. The first column contains names and each column after tracks a particular document. Is there a ...
1
vote
2answers
44 views

Can I use arrayformula in Google Sheets to count things over a variable range?

Is it possible to create a single arrayformula that does the same thing as the following column of formula: A1: =COUNTA(B1:B1) A2: =COUNTA(B1:B2) A3: =COUNTA(B1:B3) ... A9: =COUNTA(B1:B9) My best ...
1
vote
2answers
89 views

Take cells from a column, populate in two different columns following a pattern & delimiters

To better explain what result I need, I made a text block below as a pseudo sheet to illustrate. Col A (source) is the manually entered text data. Col B and C (desired result) will have formulas so ...
2
votes
1answer
39 views

How to add every Sunday in sequential order with 3 blank cells under each date?

How to add every Sunday in sequential order with 3 blank cells under each date? For example: March 22 Blank cell Blank cell Blank cell March 29 etc etc
0
votes
1answer
19 views

reference cell as sheet in criteria in countif formula

I have a countif formula that gathers information from another sheet. =SUM(COUNTIFS(test!A:A,{"Story","Task"},test!D:D,{"In Progress"})) The sheet is named test. Is there any way I can write test ...
1
vote
1answer
40 views

Syntax for Unicode characters in regexes in Google Sheets?

What's the syntax to use for general Unicode characters in regular expressions in Google Sheets? Use case: When using REGEXREPLACE with user submitted strings in Google Sheets, I have come across a ...
1
vote
1answer
58 views

Filter the row containing an exact word and highest value in Google Sheets

I have a table (Master sheet) in Google Sheets that looks something like this: https://ethercalc.org/t8f0ze0kyx6a I need to filter out an entire row of information on a separate sheet (Last Lesson) ...
1
vote
1answer
54 views

Syntax of field “Google Sheets / Pivot table editor / Filters / Filter by condition / Custom formula is”

In Google Sheets, if I create a pivot table, there is a Pivot table editor I can use to configure the pivot table. The lowest section of the interface of the editor is Filters (which allows me to add ...
2
votes
1answer
24 views

How to sum amount with a criteria separately by day in Google Sheets?

I am using Google Sheets to track income and expenses. I have a separate file for each month and a master file to summarize each month. The file for monthly account contains a couple of sheets - each ...
3
votes
1answer
87 views

Filter and match multiple columns in Google Sheets

I have a list of teams and their corresponding owners in Google Sheets. (owners own multiple teams) I have a great formula that will filter the list by owner name: =FILTER(A2:A6, MATCH(B2:B6,"RINGO",...
0
votes
1answer
24 views

Trying to make a column of “select count” in reference to another column

I am pulling data from multiple sheets in my workbook to create an array. In the first part of the array I pull data straight from a sheet like a user name # of points and rank. Now for the rest of ...
1
vote
1answer
23 views

Import cells into the text of another cell preserving quotation marks

Well, I did some research but I didn't find what I'm trying to do. I have this text: "en":"B2", "es":"C2", "it":"D2", "pt":"E2" I have 4 columns with information in each cell. I need to "import" ...
1
vote
1answer
39 views

QUERY function to search three columns for the same keyword?

I have a table with data in columns A-U. Rows go down to 368. I would like to pull (onto a different sheet) all the information in columns A-F where it matches a keyword from a dropdown menu (in cell ...
0
votes
1answer
26 views

When editing a formula in Google Sheets, how do I resume 'range selection mode' so that I can select a range with the mouse or keyboard?

When you first enter a formula in Sheets you can use the mouse or cursor to select the range. If I want to sum the 5 cells below the cell with the formula, I just type =SUM( and then use the down ...
1
vote
1answer
21 views

How do I make the “Text contains” evaluate any word written in the cell?

I just want the cell in Google Sheets to change its fill color when any text is inputted in the cell? But it has to be text excluding numbers. Please help!
1
vote
1answer
148 views

Finding merged cells in Google Sheets

I'm often trying to freeze columns in a Google Sheets and I get the error as below The way I get around this is to manually hunt for the merged cells and unfreeze them, but this can be difficult on ...
1
vote
2answers
21 views

Use Google Sheets to create column C by deleting any phrases that contain word found in column B in column A

I have a list in column A and I want to remove any cells that contain any word.
0
votes
2answers
55 views

Extracting last match from a string with REGEXEXTRACT

I've created a simple text filter: =IFERROR(REGEXEXTRACT(A1;"- May 2019| - Jun 2019|- Jul 2019| - Aug 2019|- Sep 2019"); FALSE) It should extract the month and year for an event from the cell A1. A1 ...
0
votes
1answer
24 views

How to find the last value in a column when the column is populated from an ARRAYFORMULA

Some columns in my sheet are filled manually, but then two columns are created dynamically from an ARRAYFORMULA (i.e. there is nothing in the column if the manually populated cells are empty, but once ...
1
vote
2answers
56 views

Inserting error (or spacer) when IMPORTXML can't scrape data in Google Sheets

I am using IMPORTXML to create what is essentially a vehicle inventory feed file. My issue arises when a price is not included in our main website. What seemingly is happening is when the price is ...