Questions tagged [formulas]
Formulas used in spreadsheets edited by web applications like Excel Online, Google Sheets, among others.
441
questions with no upvoted or accepted answers
5
votes
1
answer
12k
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 ...
- 51
3
votes
1
answer
1k
views
What ticker symbol to use for the crude oil price?
Which ticker symbol can I use for the crude oil price for the GOOGLEFINANCE function?
Nasdaq Brent oil is "BZ:NMX"
https://www.nasdaq.com/market-activity/commodities/bz:nmx
Nasdaq Crude oil ...
- 271
3
votes
1
answer
139
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:
- 31
3
votes
1
answer
6k
views
Repeat formula n number of times in Google Sheets
Apologies, I'm very new to Google Sheets and spreadsheets in general. I'm trying to fill some cells with repeating data, and then analyze it. The data is pulled from another large spreadsheet and has ...
Phinet
3
votes
3
answers
654
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,", ")...
- 31
3
votes
2
answers
74
views
If a cell value is TRUE then retrieve another cell's value
In Google Sheets I have a formula in Column V that returns "Paid" or "To Pay" depending on whether a checkbox in Column U is checked:
=IF(U2=True, "Paid", "To Pay&...
3
votes
3
answers
525
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 ...
- 31
3
votes
1
answer
243
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 ...
- 31
3
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).
...
2
votes
1
answer
42
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 ...
- 21
2
votes
1
answer
84
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 ...
2
votes
1
answer
56
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 ...
2
votes
1
answer
74
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 ...
- 21
2
votes
1
answer
288
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
...
- 133
2
votes
1
answer
54
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 ...
- 21
2
votes
1
answer
28
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)/...
- 65
2
votes
2
answers
112
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 ...
- 163
2
votes
1
answer
429
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 ...
- 120
2
votes
2
answers
691
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 ...
- 129
2
votes
1
answer
410
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 ...
2
votes
1
answer
179
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 ...
- 21
2
votes
1
answer
829
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 ...
2
votes
1
answer
373
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 ...
2
votes
1
answer
37
views
Adding mm:ss times when expressed as TEXT
I have a series of numbers that, for a reason out of my control I can not change, are expressed as plain text strings on google sheets.
The numbers are supposed to be minute second. mm:ss. For the ...
2
votes
2
answers
180
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 ...
- 246
2
votes
2
answers
453
views
How do I get the count number of weekdays in a datetime column in Google Sheets?
I have a timestamp in mm/dd/yy hh:mm:ss format from January to March. How do I count "how many days are Monday" in the range of A1:A199? (Or How Many are Tuesday, Wednesday, Saturday?
This gives me a ...
- 85
2
votes
1
answer
47
views
LOOKUP for data from other sheet
So I have a drop-down menu in order sheet and I have an info sheet. In the info sheet, I have 2 columns, column A has the customer name, column B has the customer e-mail.
So what I'd like to do is, ...
- 21
2
votes
1
answer
296
views
How to join VLOOKUP results of multiple sheets into one cell
I am trying to use TEXTJOIN across multiple sheets, IF a cell is is the same I would like it to return the value from another cell in all sheets that are associated with it.
In this example, I am ...
2
votes
2
answers
695
views
Next Payment Date
I am wondering how to calculate the next payment date a client has. We know when their payment date starts, what the frequency of their payments is, and of course today's date. However, I cannot seem ...
- 21
2
votes
1
answer
209
views
Restrict entry to a group of cells based on the value in another cell
I am creating a work schedule where the staff enters their own schedules. I only want a certain number of staff each shift. I already use COUNTIF to show the number of staff signed up for each shift. ...
2
votes
3
answers
3k
views
Select last entry from spreedsheet
So I have a Google Form dumping data into spreadsheet tab form responses 1.
I have created another tab labeled summary. I need to have the summary tab only show the latest entry for a given pilot ...
2
votes
2
answers
48
views
Using multiple IF and OR statements with calculations
I'm trying to calculate unit price based on quantity.
I have nested IF functions to check the quantity and multiply that by the correct unit price.
Unit Price Table
QTY >=
QTY <=
Unit $
0
...
- 21
2
votes
1
answer
1k
views
Conditional Formatting across a range of cells with a formula in Google Sheets
I have a calendar put into Google Sheets with the data put in as dates such as "9/24/2018".
I have a separate sheet with a data set that has rows that classes end such as "9/24/2018".
I want to ...
2
votes
1
answer
2k
views
Dynamic updating referenced cell from another workbook after adding/deleting rows using IMPORTRANGE - use ARRAY or VLOOKUP?
I add/deleted/move rows frequently in my workbooks. When I add/remove rows from Spreadsheet#1 ("S1"), I need the referenced cell in Spreadsheet#2 ("S2") to dynamically update so it still references ...
- 489
2
votes
2
answers
63
views
Count how many times a certain word comes before and after another in a list
I am trying to evaluate some information from an animal studying exercise using Google Sheets.
I'd like to know how many times the animal:
forages then allogrooms
sleeps then allogrooms
etc.
Also ...
- 31
2
votes
1
answer
166
views
How can I pull a value from one column based on the values of another column?
When the value in the "Amount Owed" column becomes less than 0, I want the value in the "Year" column to appear under "Year Paid Off."
For example, if the "Amount Owed" dropped below zero at "Year" 1,...
2
votes
1
answer
456
views
Dividing one column by another based on values on other columns and getting the sum
In the following image, what I'm trying to do is to find a formula that will divide values on column C by values on column L, ONLY IF:
Value on column D = "L", and
Value on column J = "Right", and
...
2
votes
2
answers
287
views
Is it possibile to concatenate with bidimensional arrayformula?
I have a table like this:
+----------+----------+----------+----------+
| Photo1-1 | Photo1-2 | Photo1-3 | |
+----------+----------+----------+----------+
| Photo2-1 | Photo2-2 | | ...
- 53
2
votes
2
answers
2k
views
Using Google Sheets to bulk collect the number of Google Search results for given keywords
I would like to create a Google Sheets that will allow me to paste in a list of 25 or so keywords in column A and would then return the number of search results for each of those terms in column B.
...
Jake Havenridge
2
votes
0
answers
68
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?
- 21
2
votes
0
answers
692
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 ...
- 21
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 ...
- 21
2
votes
1
answer
260
views
How to insert cell text from one cell into an ImportRange Function
Hello! so I have an issue where I am trying to do is have multiple functions in C, D, and E where a link is required. Instead of copying and pasting the link inside all the functions in specific ...
- 21
2
votes
1
answer
300
views
Moving linear regression on google spreadsheet
I have a table with four columns:
A: date
B: values
C: projection
D: short-term projection
The first two are hardcoded values. We admit that dates go up, while values mostly go down. I used a ...
- 21
1
vote
0
answers
18
views
How to parse multiple cells in one go using ImportJSON()?
I have a dataset with 14 columns. Two of the columns contain text that is in JSON format.
I am currently parsing the data with ImportJSON(), a script that is a collection of custom functions. It has ...
- 11
1
vote
1
answer
8
views
im trying to get a group of cells and there values repeat along a row. i have values entered from b5:bl5 and i need it to just keep repeating
Google sheets- entered values in B5:BL5 and i need it to repeat across row 5 all the way to og5. I need it to do it automatically. I have tried a scrip that doesn't work cause i don't know what I'm ...
- 11
1
vote
1
answer
63
views
How to combine ARRAYFORMULA + QUERY?
"Please advise on how to correctly input the formulas to get the desired result as shown in the picture.
Task:
https://docs.google.com/spreadsheets/d/14tpPliM_3b7RIikSimvdzLjkFq3CexPKbtOEuD4aIKU/...
1
vote
3
answers
32
views
How would I SUM column values while excluding any that precede the text string "reset"?
I have a spreadsheet where the hours of wear for pieces of equipment are logged:
Data is logged in new rows as additional wear-hours are put on the equipment.
From time to time, equipment is replaced,...
- 11
1
vote
1
answer
80
views
Clear cell value if cell in adjacent column is blank
In Google Sheets I have two data columns, S and T.
What formula would clear the value in T if the value in S is not RUN?
Sample Data
Before
S
T
1
...
1
vote
0
answers
27
views
How to convert a Splitting formula from Excel to Sheets (given that Textsplit is missing)
I'm attempting to copy the function of the following Excel sheet into Google Sheets and running into a snag.
https://1drv.ms/x/s!ApTGA-odt1HWhzGjuVHPn9bJyr5G?e=ZxtUCi
It takes the list from A2 chops ...
- 13