Questions tagged [formulas]

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

391 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
3
votes
2answers
351 views

Conditional Formatting in Google Sheets

The shorter question to spare you from reading more than you need to I have a few different formulas for use in conditional formatting with custom formula in Google sheets. They are all roughly 1100 ...
3
votes
3answers
144 views

Conditional formatting for columns, date & time specific?

I have uploaded 4.000+ Venmo donation data sets into Google Sheets for sorting, but the date and time is preset and looks like this: 2018-11-01T20:16:52 I have tried multiple approaches to sorting ...
3
votes
1answer
4k views

How can I duplicate a row in Google Sheets while retaining all of the formulas and format?

I need help with Google Sheets. We are using a sheet as a working file and new rows will be added by different users. Formatting and formulas are included in various columns. How do I ensure the new ...
2
votes
1answer
10 views

How do I write a formula while referencing another sheet in Google Sheets?

So I want to do a CountA on Column A and Column B in Sheet Two and divide Column A by Column B in Sheet One to get the percentage totals. Can I write all that in one line? =SUM(Sheet!((COUNTA(A2:A516)/...
2
votes
1answer
26 views

How do I input a current balance in a cell from a running balance that will be updated frequently?

I know I'm not accurately explaining that but here's a sample spreadsheet of what I mean:
2
votes
1answer
160 views

How do I pulling in historical daily trade volume data in Google Sheets with Google Finance?

If I try =GOOGLEFINANCE(A1,"volume",A2) where A1 is the stock code and A2 is the date I'm querying, it comes back printing 'date' and 'volume' on one line as headers and then the data on the ...
2
votes
1answer
75 views

ImportHTML Altering Data Format/URL Fetch Issue

4/17 6PM Update *** I am now trying to use the URL Fetch App. I've been playing with this for hours, but can't seem to figure out the issue. I am just trying to get the info from this site as plain ...
2
votes
1answer
21 views

How to copy and paste a formula changing only the cell references made on this sheet

How can I copy and paste a formula changing only the cell references made on the active sheet and leave references to ranges on another sheet as is?
2
votes
2answers
1k views

Set calculation precision in Google Sheets

Google Sheets uses some sort of floating point arithmetic in its internal calculation, even if only integer numbers are in the calculation. For example: A1: =128^128 A2: =A1+1 A3: =A2-A1 Then, A3 ...
2
votes
1answer
74 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 ...
2
votes
1answer
62 views

Google Sheets date conditional formatting

I have dates in column D in Google Sheets. I want the cells to highlight red if 30 days have passed and there is not a comment date in column F. Example: Column D: Submittal Date 6/29/2019 ...
2
votes
1answer
23 views

How to create draggable sheet reference with the same cell?

I'm not very familiar with Google Sheets and have a question that may be very simple but I can't seem to find an answer. I am trying to pull information from one same cell example, H47 from multiple ...
2
votes
2answers
63 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 ...
2
votes
1answer
3k views

How can I track the time it takes for someone to fill out a Google form?

I am running a study for my thesis, and would very much like to track the time it takes to complete a Google form to compare against how long it takes to fill.
2
votes
3answers
419 views

How to remove duplicate words in a cell only if longer than a certain number of characters?

I need to remove a duplicate text in a cell but only if it is longer than 4 characters. I have this formula that only works for any types of duplicate words: =join(" ",unique(transpose(split(A1,", ")...
2
votes
1answer
34 views

IF Cell = True, then retrieve (separate) Cell Value

Cells: T2 = Price to Pay U2 = (Tick box) V2 = =if(U2=True, "Paid", "To Pay") W2 = empty What I want to do is be able to tick U2 and have W2 contain the value of T2. V2 works, so it does not ...
2
votes
2answers
368 views

Formula for PROPER Case with Apostrophe

I'm using Google Sheets glued together (not be me) from multiple sources where some of the names are in all caps. In trying to clean web-ready content, I have good success with the PROPER() function ...
2
votes
1answer
25 views

Using multiple IF and OR statements with calculations

I'm trying to calculate prices for items that have various costs. Depending on what that cost is determined its multiplier. I'm hoping I can create a formula in a spreadsheet to streamline my process. ...
2
votes
1answer
184 views

Add values between two dates in Google Sheets

So I've already found this question answered several times around here but for some reason, despite essentially copy and pasting the answers it still doesn't work for me. I'm trying to add ...
2
votes
1answer
2k 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). ...
2
votes
0answers
58 views

If I ask a yes/no question, am I able to give it a value and add to calculations?

Question on my form: Is your wedding on a holiday? If they answer yes, I'd like to add a $100 value to that answer and then add it to the total. Is this possible? If so, how?
2
votes
1answer
535 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 ...
1
vote
1answer
12 views

Count number of rows with a specific MONTH in the date of a specific cell and a Boolean condition in another cell

Let's say I have a number of dates and whether they occupy full or half a day: DATE | WHAT | HALF? ... 2021-4-1 | Something Something | false 2021-4-2 | Something Something | false ...
1
vote
0answers
22 views

Google Form to sheet to Doc, with calculations after pushing to sheet

NOTE! I am not a programmer I'm just fumbling through this! I've gotten as far as I can with searches, getting lost on multiple steps as most responses don't integrate on thing to the next. Background ...
1
vote
1answer
20 views

Add '/' character in between a number (which should be formatted as a date) and turn it to a date

I have received a spreadsheet which has the dates formatted with only numbers without separators and I would like to transform them into a date format. For example, I would like to change the number ...
1
vote
0answers
11 views

Is there a way to detect clicks in Google Sheets and run a function?

I have a Google Spreadsheet as shown below. The 30 at the bottom is what the title takes to display the points. How do I make it so that when the user clicks the buy button it will deduct the amount ...
1
vote
1answer
23 views

How to add data in a specific row after searching a column for a specific keyword?

So I'm trying to figure out how to have sheets look in the username column for a specific username and then find the sum of the numbers in the "kills" column that are in the same row as that ...
1
vote
2answers
31 views

Spreadsheet formula to calculate most common items in ordered items lists

I am searching for a correct google spreadsheet formula to go through the list of ordered items and summarise the resulting winning items. Like is you have an Oscar ceremony and you get votes from ...
1
vote
2answers
27 views

How to prevent row/column insertion/deletion from breaking links to other sheets?

Earlier I asked this question: How to create a link with content bound to other cell's content in Google sheets? And until now the provided solution was working perfectly for me but now, the ...
1
vote
2answers
23 views

Want to use a cell to get a limit number

Here is my query. I want "select * limit 5") the 5 to come from a cell. How do I do that? =ArrayFormula(AVERAGE(QUERY(SORT(Tabell!D4:D33,ROW(Tabell!D4:D33)*ISNUMBER(Tabell!D4:D33),0),"...
1
vote
1answer
38 views

importing from multiple workbooks for Google Sheets to ignore workbooks that have nothing listed, instead of returning an error

={FILTER(IMPORTRANGE("116uNZi52pAQe1_D_ZLNuD18gSx3zfB1P08TL2cT7Vdk", "Trade and Manufacture!P17:T"), IMPORTRANGE("116uNZi52pAQe1_D_ZLNuD18gSx3zfB1P08TL2cT7Vdk", "...
1
vote
1answer
20 views

Getting the column header if the row value is the greatest of the entire row

I think that the best way to describe what I want is with a image Check the image. Each row will have a description (Of a feature of the programming language, for example) each language will have a ...
1
vote
1answer
22 views

How do I return all results associated with an input cell in Google Sheets?

I have a time study data set that has three different order types and resulting times to fulfill that order. An Order Type will be entered into C8. In cells C9:C, I want to create an auto-updating ...
1
vote
1answer
520 views

Looking for a “weighted average if” functions in Google Sheets

I am familiar with the =AVERAGE.WEIGHTED function in Google Sheets, but I'm wondering if there's a way to add an IF in there, like =AVERAGEIF. I have a baseball spreadsheet, with the following ...
1
vote
1answer
289 views

Can automatic send emails when checkbox = True?

I'm looking for an script can automatic send emails when checkbox is True. This is my format example: What i want is when im clicking on checkbox = True at F2, i can recieve an email with excactly ...
1
vote
1answer
52 views

How to link cell within googlefinance formula

I'm trying to get the googlefinance formula to work for multiple country exchanges. e.g. Tencent is listed on Hong Kong stock exchange, the code is 0700. If I put =googlefinance(HKG:0700,"price"), ...
1
vote
2answers
24 views

How to write a text if conditions met in Sheets?

I want to automatically create a text depending on what the other columns shows. For example, if in A1 there is "Service", then G1='1'. And if in B1 there is "True", then G1 = "11" (as in '1' + '1'). ...
1
vote
1answer
21 views

How to stop IFS function always giving the same result for x even if greater than x or less than x

C9 being the group size, these are the formulas I used: =IFS(C9<5, "0%",C9>=5,"15%", C9>=15, "25%", C9>=40, "40%") =IF(C9<5,"0%",IF(C9>=5,"15%",IF(C9>=15,"25%",IF(C9>=40, "...
1
vote
1answer
218 views

How to copy formulas from one spreadsheet to another?

If I select say 10 rows in Google Spreadsheets and use ctrl+c and then create a new spreadsheet and try to paste all the data it only moves over the values but not the formulas. Is there any way to ...
1
vote
0answers
28 views

Filtering results on a lot of parameters

I'm searching for a way to filter +- 100 weighted averages, for which I've already made the correct formula. The datasets are in 7 different tabs and contain +- 40 columns and on average +- 50 rows. ...
1
vote
1answer
180 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
58 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 ...
1
vote
0answers
1k 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 ...
1
vote
1answer
84 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 ...
1
vote
1answer
66 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%*...
1
vote
1answer
574 views

Google Sheets - Split row into multiple rows

Current: ------------------------------------------------------------------- | Amy | Adam | Bob| Cliff | Dave | David | Derek |...| Zachary ------------------------------------------------------------...
1
vote
1answer
375 views

Google Sheets countif attendance sheet, quarterly report

I have a Google Sheet that I use to track attendance at an open lab that I run. Each quarter I put together a report where I count the number of people that attended that quarter. If I just use the ...
1
vote
0answers
47 views

Survey Data & Google Sheets: Excluding Missing Values?

I am using survey data in Google Sheets with rows for "before" and "after" responses, with the same survey administered to the same subjects at two different time periods. With each question, a number ...
1
vote
1answer
2k views

Check for regexmatch in multiple ranges in Google Sheets

I have a sheet that auto-populates with a sub-set of articles (each a row with a column representing various information such as title, year of publication, author name, etc...) on the basis of a set ...
1
vote
1answer
78 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 ...

1
2 3 4 5
8