Questions tagged [formulas]
Formulas used in spreadsheets edited by web applications like Excel Online, Google Sheets, among others.
1,623
questions
0
votes
0answers
6 views
Autofill google sheets formula incrementally
I'm looking to pull data from several separate sheets into one overview to have a snapshot picture of a range of booking in my venue.
I'd like to look at the venue booking per space for any given day, ...
0
votes
0answers
9 views
Google sheets - edition inventory [closed]
I need help making a Google Sheets script to streamline our sales inventory. Right now we get a sale and it automatically updates a sheet, we then select that sale and manually copy it's info in ...
0
votes
0answers
10 views
Action paln formula for google sheet
I need to do a plan for my team. Name, time of working, and I need a formula that can assign randomly in the slot of 1 hour: chat, call, break, and task.
I have to add a rule so that an agent per day ...
0
votes
0answers
14 views
How do you color a cell if no other cell references it? [duplicate]
I am making a spreadsheet with a bunch of variables I want to change and some formulas that use those variables. I want to use conditional formatting (probably?) to color any cells which are "...
0
votes
0answers
14 views
Max sum held over a month
I need help with a formula that needs to calculate the max sum held at one time for each month.
In this example it would be 54 for March (12+13+14+15, held on the 29/03/2021). Month is determined by &...
0
votes
0answers
11 views
While using INDEX and REGEXEXTRACT, how can I return blank cell instead of 0 [duplicate]
While using INDEX and REGEXEXTRACT, how can I change this formula to return a blank cell (instead of 0) when all of the cells in that section are blank?
1
vote
1answer
12 views
How can I list column names with rows that only contain value
How can I list column names (in one cell) with rows that only contain value (blank not included)?
If possible, separated by a line break.
1
vote
1answer
27 views
GOOGLEFINANCE in Google Sheets to get high & low and timestamp of high/low from the day I took trade
I wanted to keep a track of high/low of a trade from the day I took the trade.
For a day I am able to get high/low but how to get from the date I took my trade which is stored in one of cell.
=IFERROR(...
1
vote
1answer
18 views
Change Google Sheets displaying “0” for =Sum formulas
I'm putting together a spreadsheet in Google Sheets to keep track of my nutrition and weight loss.
I want to make 2 charts, 1 that will display my weight over time and one that will display my calorie ...
0
votes
0answers
17 views
How to calculate probability in Google sheet based on previous years data
I was trying to get a leave taking trend and predict based on the trend that which dates are more likely to have higher number of leaves.
I have a data set of date wise leaves taken by hundreds of ...
0
votes
0answers
7 views
Google finance condition to take 1 year return or from listing date
I am using following query to get 1 year price change percentage for a list of stocks,but some stocks were not available 1 year ago ,some got listed during the year. How can I modify the query to ...
0
votes
2answers
23 views
How to correct this formula with If function?
I have a data filled from Column C9 to column I39
AND I Auto-Add text in Column J using this formula:
=ArrayFormula(IF(I9:I39="Excellent","Excellent",IF(I9:I39="Good",&...
1
vote
1answer
31 views
Create a Unique Order Number From Existing column of values
I need to create a unique ID from an existing list of order numbers. Pls see the image below:
What I want is for the existing duplicate order numbers to be made unique by adding an extra digit to the ...
0
votes
1answer
27 views
Concat columns from query result in Google Sheets
I have a sheet that has a column with labels in a single cell. A cell can contain 1 or more labels, each separated by a comma.
+-----------+
| Labels |
+-----------+
| Foo,Bar |
| Bar,Baz |
| ...
0
votes
0answers
44 views
Copy Google Sheet's cell if there is today's date in any cell next to [duplicate]
I need a formula that would copy all the cells from column B if there is today's date in any cell next to (same row). The copied cells would go to empty column I, each cell under the next.
0
votes
1answer
8 views
How to tie a formula to a specific row
Let's say I Have that formula in the picture at B2. I always thought it is natural to add more rows to the top of the page to add more data so I can see them first as more recent. My problem is now ...
0
votes
1answer
27 views
Create row for each entry in a table (ideally without script) [duplicate]
I have a table of number entries. Say, for each Product (rows) a number of purchases on each Date (columns).
Date
Date
Product
150
200
Product
350
400
I'd like to turn this into one entry for each ...
0
votes
1answer
22 views
Bulk Search Or Find a bunch of values at once
I have a list of order numbers that I get from one sheet & cross check whether they are there in another sheet (they shouldn't be, they would be the orders that we don't need so we delete them ...
0
votes
1answer
19 views
Google Sheets filter or query where range of columns matches condition
I feel like this should be simple but haven't been able to figure it out or find a corresponding question/answer.
I need to get specific column values for rows where any column in a range matches a ...
0
votes
1answer
100 views
Display a list of names if today's date equals a date in any adjacent cell on the same row
My spreadsheet has "variant" names in Column B, and relevant dates in adjacent columns C-H on the same row. I have included a screenshot below
I want to create formula that will display a ...
1
vote
1answer
31 views
countif but not empty cells
Can I make this formula not count empty cells?
={"Score"; INDEX(if(len(A2:A), countif(if(REGEXEXTRACT(C2:BE,"(?i)yes")="yes", row(C2:C)), row(C2:C)),))}
1
vote
1answer
21 views
Google Sheets formula - how to count up to 12 and then restart?
I have columns under names of people, the rows correspond to dates below the name. I also have another row with a counter. Something like this:
Bob
Counter
2
12 MAR
X
11 MAR
10 MAR
X
The counter ...
-2
votes
1answer
13 views
how do I get the sum of hours, from 13:30 to 13.5
How can I have the sum of hours and minutes? For example, I enter in A1 14:20:00 how can I have in A2 the sum that's 14.33?
0
votes
1answer
18 views
Help with formulas to create an inventory tracking system on Google Sheets
I have created an inventory tracking system with check in/out on Google Sheets and a companion Google Form. For the checking in/out part, I have 2 separate sheets with another for a status. The status ...
0
votes
0answers
15 views
Creating a sheet that pulls columns from other tabs
I have created a Google Sheet with multiple tabs for individual project schedules. The individual schedules have two columns: one with the tasks, a second with the project's name and the due dates. I ...
0
votes
1answer
83 views
Google Sheets - Formula to find difference between two values and recognize if the number difference is positive or negative
Hope my blabbering will make sense to someone.
I'm trying to create a spreadsheet in Google Sheets to record profits and losses.
I've used the =ABS(A1-A2) formula to get the differences in between two ...
0
votes
1answer
44 views
Google Sheets - duration between two specific times
I'm having a little trouble writing a formula to measure duration between two specific times within a time range.
i.e. Time range: 1300-2330
Total hours=10:30
Hours between 2200-0000=1:30
So far I've ...
0
votes
0answers
15 views
Trying to Automate invoicing using Google Forms and Sheets
I am trying to problem solve a Google spreadsheet that I use for invoices.
Currently the set up is as such. I have 4 sheets in the spreadsheet. The first sheet is linked to my google form gets its ...
1
vote
1answer
21 views
Google Sheet - filter non-zero or #N/A using custom formula
Say my data is in range A6:Z with the headers in row 5. I want to display rows with value in column C that is non-zero or #N/A. I tried OR(C6:C <> 0, IFNA(C6:C, true)) and OR(C6:C <> 0, ...
0
votes
1answer
40 views
Running Average in google sheets
I'm new to data analytics. This might be a simple solution but I could not find it after 2 weeks of trying. I have a column of numbers that represent wins and losses. Losses are represented as "0&...
0
votes
1answer
15 views
Need Google Spreadsheet Formula to get all same-postal code cities on a single row
Looking to get all same-postal code cities on a single row
On a sheet I have thousands of postal codes from Australia and the cities that are tied to those postal codes.
There are two main columns:
...
0
votes
1answer
9 views
Data validation with specific condition
I have special conditions for column in Google Sheets which are:
should only be filled with numbers
no special characters allowed
no alphabets allowed
no space allowed
should be 16 digits in total
I ...
0
votes
1answer
43 views
Count if contains yes using array formula for Google Form entries
I want to count every yes per submission of each Google Form, so the row.
Some of the responses have comments after the yes and it won't count those. This is the formula I am using:
={"Score"...
0
votes
0answers
11 views
Is it possible to move a cell if two other cells have the same value in Google Spreadsheets? [closed]
If so, how can I do it like in the example image, from the data on the left to be in the format on the right?
Example:
0
votes
0answers
18 views
I need help shifting all my formula references in a Google Sheets table by one cell to the right
I have a table in Google Sheets right now that deals with growth from the most recent period (As compared to 2 periods ago, 3 periods ago, 4 periods ago, etc.). What I need help with is how to change ...
0
votes
0answers
21 views
Google Sheets #DIV/0! error needs to be blank or 0
On my percentage average cells Profit Margin % =((A5)-(B5+C5))/A5 & Markup % =(A5-D5)/D5*100 I am getting #DIV/0! and need the cell to have a 0 (zero) or stay blank so I can average the respected ...
0
votes
0answers
12 views
Import range with a sum built in
I have two questions:
What formula would I use to importrange from another spreadsheet if I wanted it to import as a sum of two columns from another sheet?
The second question is:
in this other sheet ...
0
votes
1answer
11 views
Find whitespace and replace with string from formula in Google Sheet
I have list of cities and I want to create URL for each of them in Google Sheet.
Scenario is most of the part of the URL is same only at the end city name is different so I created a formula
=...
0
votes
0answers
10 views
Google Sheets formulas that needs to read data from drop down boxes
I am trying to create a master spreadsheet that is pulling information from several other sheets into this one master sheet.
I then plan on using this master sheet to pull statistics.
In the sign up ...
1
vote
1answer
18 views
How to reference a cell outside range using query function in Google Sheets
I'm trying to copy cells B to F to another sheet if the text in cell J contains specific text. As an example case, I'm using the query function as:
=query('Sheet 1'!B1:J,"Select * Where J ...
2
votes
2answers
25 views
DGET fails to lookup properly when two entries are similar
I am running into a weird DGET error in Google Sheets.
in one table, I have the following entries:
Column A Column B
Horse Wins
Daedalus I 15
Daedalus II 12
Panacea I ...
0
votes
0answers
4 views
Issues with auto updating IMPORTXML (google sheets) [duplicate]
I have a spreadsheet monitoring my Stocks and cryptos etc on Google Sheets.
I have a formula which 50% works and auto updates and 50% doesnt work and comes up with 'N/A : Could not fetch URL'...when ...
0
votes
0answers
9 views
Import XML intermittently doesnt work but sometimes does on Google Sheets (currencies/shares etc)..Image attached [duplicate]
I have a spreadsheet monitoring my Stocks and cryptos etc on Google Sheets.
I have a formula which 50% works and auto updates and 50% doesnt work and comes up with 'N/A : Could not fetch URL'...when ...
0
votes
0answers
10 views
Obtain breakdown based on a different column
Is it possible to obtain nested values of a column immediately after the value they are related to from another column?
Basically, I have a value (Order Item Cost coming from a unique Order Item Id), ...
1
vote
3answers
29 views
Count instances of character in a column
Is there a way to count instances of characters down a column, without creating further columns that do row-by-row counts of each character?
Below is an outline of what I'm trying to do.
Note that no ...
0
votes
0answers
18 views
How do I make formulas effective only starting from tomorrow in Google Sheets?
I made a Google Sheet for insulin-dependent diabetics, to calculate insulin units based on their meal content and glucose reading inputs. Each day has its own block of cells for the patient. When I ...
0
votes
1answer
10 views
Jump to cell address mentioned in other cell
A1 shows address of last cell un a column (e.g. A572 or A577). Every time address will change if adding new row (there can be blank rows in between of rows with data).
I need a link in B1 to click and ...
0
votes
0answers
18 views
Jump to a cell, address of which is located in another cell
A1 has a formula that shows the address of the last row with data (A958, A959). I need to make a link in A2 to the cell address of A1. (eg. If A1 has Value C456, I need to jump to Cell C456 by ...
2
votes
1answer
45 views
Adding together multiple Sum Filter or Sumifs with multiple conditions across multiple sheets?
So I have a budgeting spread sheet. I have an overview page that I need to link to a "Checkings" sheet and a "CC-Chase" sheet.
To calculate the amount spent on a specific category ...
0
votes
0answers
18 views
Auto-generate unique sheets when you add a new row in Google Sheets?
I'm a fairly new user to Google Sheets, I'm trying to set up a sheet whereby when you add a new line in one sheet, it creates a separate check-list sheet. So when a new user is added to the master ...
