Questions tagged [formulas]

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

474 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
4 votes
1 answer
10k 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 ...
user avatar
  • 41
3 votes
2 answers
602 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 ...
user avatar
3 votes
3 answers
622 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,", ")...
user avatar
3 votes
3 answers
411 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 ...
user avatar
2 votes
1 answer
62 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 ...
user avatar
  • 21
2 votes
1 answer
24 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)/...
user avatar
2 votes
1 answer
41 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:
user avatar
2 votes
1 answer
132 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 ...
user avatar
2 votes
1 answer
54 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 ...
user avatar
  • 21
2 votes
2 answers
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 ...
user avatar
  • 216
2 votes
1 answer
38 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 ...
user avatar
2 votes
2 answers
653 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 ...
user avatar
  • 59
2 votes
1 answer
32 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. ...
user avatar
  • 21
2 votes
1 answer
203 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 ...
user avatar
  • 21
2 votes
1 answer
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). ...
user avatar
2 votes
0 answers
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?
user avatar
  • 21
2 votes
0 answers
666 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 ...
user avatar
2 votes
1 answer
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 ...
user avatar
  • 21
1 vote
1 answer
6 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
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
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
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
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
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
1 vote
0 answers
26 views

How do you change the font color if the cell contains hyperlink on google sheet

How do you change the font color if the cell contains hyperlinks on google sheet? I have 1 cell that contains texts with hyperlinks. I want to change the color of the cell if it contains hyperlinks. ...
user avatar
1 vote
1 answer
17 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 ...
user avatar
  • 11
1 vote
1 answer
115 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 ...
user avatar
  • 11
1 vote
0 answers
53 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 ...
user avatar
1 vote
1 answer
19 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 ...
user avatar
1 vote
0 answers
32 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 ...
user avatar
  • 11
1 vote
2 answers
22 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) ...
user avatar
1 vote
1 answer
53 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 ...
user avatar
1 vote
1 answer
33 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 ...
user avatar
1 vote
0 answers
20 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 ...
user avatar
1 vote
0 answers
16 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 ...
user avatar
1 vote
0 answers
43 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))...
user avatar
  • 11
1 vote
0 answers
19 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 ...
user avatar
1 vote
1 answer
86 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 ...
user avatar
1 vote
1 answer
32 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 ...
user avatar
  • 123
1 vote
0 answers
37 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 ...
user avatar
1 vote
1 answer
33 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 ...
user avatar
1 vote
0 answers
51 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 ...
user avatar
  • 111
1 vote
1 answer
31 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 ...
user avatar
  • 11
1 vote
2 answers
48 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 ...
user avatar
  • 153
1 vote
1 answer
502 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 ...
user avatar
1 vote
2 answers
32 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),"...
user avatar
  • 11
1 vote
1 answer
324 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", "...
user avatar
1 vote
1 answer
29 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 ...
user avatar
1 vote
1 answer
43 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 ...
user avatar
  • 11
1 vote
1 answer
2k 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 ...
user avatar
  • 11

1
2 3 4 5
10