Questions tagged [formulas]

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

452 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
4
votes
1answer
9k 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 ...
3
votes
2answers
571 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
581 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,", ")...
3
votes
3answers
360 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 ...
2
votes
1answer
47 views

Check if a string appears within another cell, and return the longer of the 2

I am having a hard time thinking of an formula that can help me pull the data needed in this example: In the C1 cell, I would like to make a formula that does this: Check if any cells in the A ...
2
votes
1answer
20 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
34 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
120 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
31 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
74 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
36 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
578 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
31 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
194 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
3k 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
60 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
0answers
663 views

Random #REF! errors

I am a new Google Sheets user, but have been using Excel or equivalent for over 25 years. I have an issue with #REF! errors associated with Google Finance lookup in a sheet I wrote to pull stock ...
2
votes
1answer
2k 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
15 views

Conditional Formatting Help

I must be having an off day as I'm usually good with formulas - but I'm trying to finalize something for work and my brain is not getting me there.... I need a formula to color cell G75 Orange if cell ...
1
vote
1answer
53 views

How to get weekly averages for workout tracker data in Google Sheets?

I am making a personal training app that uses data from Google Sheets. I want to create a formula that takes the data from a sheet of workout tracking inputs and creates a weekly average for each ...
1
vote
0answers
41 views

Use ArrayFormula to automatically update the column with a formula already containing an ArrayFormula

I am using the below formula to concatenate some data into one string: =TEXTJOIN("",FALSE,ARRAYFORMULA(IF((Data!$F$3:$F=$C2)*(Data!$G$3:$G=$D2),Data!$L$3:$L,""))) I however have ...
1
vote
1answer
16 views

Round up duration calculation to the next multiple of five minutes

I currently have this formula: =text(D2 - C2, "hh:mm:ss") I need this formula to round up to the nearest 5 minutes. I have tried mround(), ceiling() and roundup(), but I'm not getting the ...
1
vote
0answers
29 views

Adjusting Filter with Regex

Original Problem: Need to count Multiple Criteria from one column in addition to a single piece of criteria from 2 other columns in one tab into a count on a separate tab of the same workbook in ...
1
vote
2answers
20 views

Count and number how many times the same conditions have me

I'm trying to see if there is any way to count in Google Sheets how many times conditions in other columns are met. Please see image below. In the example, there are 3 conditions (Car, Color, Status) ...
1
vote
1answer
40 views

IF condition with ISBLANK using Google Sheets *driving me insane*

I have a Google Sheet for investment tracking setup and I'm trying to add a ISBLANK to the following formula: =IF(G67=TRUE,G12, G12*GOOGLEFINANCE(("CURRENCY:USDEUR"))) <--This formula ...
1
vote
1answer
104 views

How to convert text to a number in Google sheets

I've built a form that dumps data to a Google spreadsheet and that data, studio reads it... and turns that into a dashboard.. only problem is the text doesn't convert very well for building a dash ...
1
vote
1answer
23 views

FILTER between columns

I have thousands of data in 2 columns First one is ordered by export and the other by latest activity. The export columns has more queries than the latest activity. (can't change the export to the ...
1
vote
0answers
19 views

Change the text string of a cell based on whether or not previous cells are filled

I am working on a spreadsheet that tracks the overall pipeline for video production for a group of volunteer filmers and editors. Being on as a volunteer manager myself, I wanted to create a ...
1
vote
0answers
13 views

How to create formula that copies 15 rows and inserts below

I am a newbie when it comes to formulae and macros in spreadsheets. I am creating a manager sheet for a tabletop gaming group, and during the game players may add additional characters to their ...
1
vote
0answers
38 views

Moving average Google Sheets

I'm looking for a way to calculate the average turnover of the past 4 weeks. The formula I used to determine to current year/weeknumber is: =year(now())&"_"&0&(WEEKNUM(now();21))...
1
vote
0answers
17 views

How do I add specific entries in columns to a final total?

To start, I'm trying to create a spreadsheet to help keep track of my spending and earnings throughout the year in an effort to crack down on my budgeting and excessive spending. I would like to ...
1
vote
1answer
61 views

Google Sheets formula working in only some cells

https://docs.google.com/spreadsheets/d/1nmMw2cfJkD5EgPHFjFYdO4PhJ822PfQhCcl3MYYnYJ0/edit?usp=sharing I'm using a formula to search for a term within a cell and populate the correlating name into a ...
1
vote
0answers
14 views

Modify contents of an array using an IF statement in Google Sheets?

How do I modify contents of an array if it matches a condition? Right now I have var data = sheet1.getRange(2,1,3,2).getValues(); var key = sheet2.getRange("A2").getValue(); // if (data[...
1
vote
1answer
19 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
33 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
28 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
35 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
28 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
44 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
1answer
382 views

Google Sheets: filtering and matching a list treating NON-unique values

I have a list (column A) of non-unique text values. Items from that list are put into column B (via dropdown data validation). Any items from column A that are missing in column B are shown in column ...
1
vote
2answers
30 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
268 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
26 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
39 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
1k 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
2answers
31 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
219 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 ...
1
vote
1answer
24 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
1k 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
1answer
28 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?

1
2 3 4 5
10