Questions tagged [formulas]
Formulas used in spreadsheets edited by web applications like Excel Online, Google Sheets, among others.
1,546
questions
1
vote
1answer
25 views
Trying to calculate how frequent of the shift happen in certain criteria
I work on schedule, would like to calculate how many are morning shift and how many are afternoon shift base on market.
I've tried if with countifs, arrayformula with countifs, it doesn't seem to work....
0
votes
1answer
28 views
How to find _any_ formula error in a Google Sheet via a standard command (and not a script)?
For complex Google Sheets with many tabs (my teams effectively employ Google Sheets as a sort of database in these scenarios), is there a relatively-easy way to search for any and all formula errors (...
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
1answer
25 views
Trying to add multiple formulas in one cell
I'm very new to Google Spreadsheets and I'm trying to add multiple formulas in one cell. This is my code so far, I'm not very good at formulas.
={SUBSTITUTE(C:C,"wd","Web Development&...
0
votes
1answer
44 views
importrange & query
I don't have programming background, but as a novice, experiment with formulas on my own.
I have two separate live sheets;
On the 1st sheet (sheet A) tab F, when I write Y into any cell in Column O, ...
0
votes
0answers
22 views
How to convert multiple form responses into easily readible table to use
I have a Google Form that is going to be used by different employees to add stock received. Since there is usually more than 1 item being received, I needed to provide space for them to add multiple ...
0
votes
2answers
52 views
Google Sheets for if contains “*”
I have found the the regexmatch solution for substring: Google Sheets formula for "if contains"
However, why for "*" is not working? I'm trying to make a condition if the name ...
3
votes
1answer
51 views
Using regex to select the text between two characters IF there is text
I have this text:
<a class="_f5rco33" href="/account-settings"><div class="_ojs7nk">Account</div></a>
And I want to grab the word "Account&...
1
vote
1answer
26 views
Locking a formula when adding rows
Currently I have this formula in there
=IF('Form Responses 3'!G$20="","",COUNTIF('Form Responses 3'!G$1:G$167,F20))
It does what I want it to do if I go in a tweak it later, but I ...
1
vote
1answer
23 views
Replacing items in comma delimited cell with same string each time
I'm trying to create a formula that will replace any contents of comma delimited cell with single string. The number of items in delimited cell will vary...
Specifically:
524352435,2345235523,...
0
votes
1answer
8 views
Excel formula to skip the rows with zero value while linking
I need a small help in fixing my formula.
i have a report from erp with few employees name and the amount they owe. It is a dynamic report and changes everyday.
My requirement is that I need a formula ...
0
votes
2answers
24 views
Removing specific text from a box?
Is there a way to remove text from a box in google sheets? Here is a example:
Total: [=sum(stuff here)]
User 1 5 points
User 2 8 points
The [5 points] is one cell. If I just try to sum it up, it ...
1
vote
1answer
23 views
Attempting to use a LOOKUP with an Importrange tab on Google Sheets to make sure values across two sheets are correct, but I keep getting errors
This is the sheet I am working on. The trouble starts on the tab titled "Sectors and subsectors", on row 56 under the column named "checksum".
What I am attempting to do is as ...
0
votes
1answer
26 views
Google Sheets - Filtering Chart Data with Checkboxes
In Google Sheets, I'm making a 'workbook' that keeps track of inventory in a shop. There are 3 sheets in the workbook.
The first one is the raw data which is 'Item Name', 'Initial Stock', 'Stock Used',...
0
votes
1answer
18 views
Strikethrough name in cell in specific range if it contains/matches name from another cell range on same sheet
So we've got a calendar on google sheets, it has a bunch of different things besides just the days but the part I'm working on is easily displaying who is not going to be working that day.
We have a ...
2
votes
2answers
64 views
How do I find the max value in a column only if it contains certain texts in google sheets?
The data has numbers in text format and texts with numbers.
How do I find the max of the numbers in text format without converting to numbers?
How do I find the max if it contains certain texts (ie, ...
0
votes
1answer
29 views
Google Spreadsheet Advanced Conditional Filter
Let's say I have a money "Transactions" Table with columns: [Timestamp, Account, Category, Inflow, Note, Group].
I would like to use FILTER() function to display it in another page with 5 ...
1
vote
1answer
39 views
Google Sheets Insert Cell Values into another Cell using formula
So, I am looking to insert two cell values into one cell, one following the other.
For the following example see the Image below.
I want to put a Formula in C such that the formula would result in ab
...
1
vote
1answer
50 views
Use Regular Expressions to Add Text to Beginning or End of Cells
I have a bunch of words in a sheet in one column, e.g.:
Study Guide 101
Meditation for Beginners Cheatsheet
Lose Weight Fast
What I want to do is add a word to the end of these in the cell using ...
1
vote
1answer
42 views
Query Function - remove parts of entered term and carry out formula
I'm sorry if the title is confusing.
In the List to Search tab, I've got a bunch of orders. What I do is select the dishes in a single cell in this manner:
And I want to just dump them into the ...
2
votes
1answer
72 views
Conditional Format if cell contains partial match for text
I want to conditional format the columns B to G, if one of them contains the text in say, cell H5.
This is the formula I use for that, =$H$5=B1 at the moment.
What I want is for the conditional format ...
1
vote
2answers
57 views
Google sheets, convert value from 1 column to another based on particular data in it
Let's say I have those values in the column:
w 1
w 2
w 3
I want to convert those values to
1
2
3
in another column.
So basically I need to convert the value "w x" to "x".
How ...
0
votes
0answers
31 views
Conditional formatting with multiple conditions (lost between IF or AND, and not empty cells !)
I am trying to combine formulas here, to put color on specific cells (out of a specific range) when they are not empty, and when a checkbox in another cell is checked.
Here is what I tried :
=AND($D4=...
1
vote
1answer
107 views
Search multiple columns for one value
This is roughly what my table looks like:
A B C D E F
Name |Ingredient 1 |Ingredient 2 |Ingredient 3 |Ingredient 4 |...
0
votes
1answer
21 views
Conditional Formatting Based on another Cell with Multiple IF's
I had a Conditional Format in Place, but it somehow stopped working once I filled in an IF Argument into my cell.
A1 Contains a Number I put in manually
A2 Contains another Number I put in manually
A3 ...
0
votes
2answers
39 views
How to pull data from one sheet to another
Very inexperienced google sheets user here. I have a sheet with employee data. One row and many columns of data for each employee. Some employees appear more than once (i.e. in multiple rows)
I need a ...
0
votes
0answers
3 views
Using a cell to build a formula
I am trying to build an ever changing formula, that will list the contents from multiple work sheets.
The trouble I have is sheets will be constantly deleted and added to the spreadsheet, meaning I ...
0
votes
1answer
16 views
How do I use text and a formula in a cell
"Books Added in October 2020 " & "counta(A3:A)"
This is not working.
Can someone help me?
0
votes
1answer
32 views
If colE contains value then import colAY from same sheet
I am trying to import 2 columns from one sheet into another but trying to make sure I import only the cells from col2 when col1 has a value (all different values).
I imported my first column needed (...
0
votes
1answer
41 views
Convert weekday to text output
In a monthly spreadsheet I have cells which contain the year (A1) Month (A2) and then the rest of the column running from cell A4 through to A34 numbered as the day in the Month (1 to 31).
My ...
0
votes
1answer
35 views
Use array as function parameters
Given a function that returns an array, is it possible to "spread" the return values as the arguments to another function?
For example, I'd like to pass the result of SPLIT("example.com,...
1
vote
1answer
151 views
How to show Day of Week in Order by Day of Week in Google Sheets
I have the following formula that displays a total by Day of Week (weekday) from a date in J and amount in K
Example
J contains 1/1/2020 ... until 9/29/2020
K contains numeric value 123 ... 1999
=...
0
votes
1answer
24 views
Any way to duplicate a formula that needs individual relatively absolute references?
I need to duplicate a formula down for many rows. However each one needs to be an absolute reference, referencing a specific cell in that individual row. I can't just fill down the original formula ...
1
vote
1answer
34 views
How to create a link with content bound to other cell's content in Google sheets?
As stated in the tittle I'm trying to make the content of a cell that links to an other cell have it's display text be based on the other cell's content. Before I was able to do it by right clicking ...
0
votes
1answer
41 views
Using REGEXEXTRACT to retrieve specific content from two separate rows in the same column
I Included a Google Sheets link
=REGEXEXTRACT(A2:A3, "19..")
To me, it would seem as if this would check both cells for any date starting with 19 and extract that into the cell
But, it ...
0
votes
1answer
22 views
Google Sheet average a row from last 10 days to the current date
I am looking for a formula that would automatically average their own row on C3 to C10 based on the last past 10 days from the current day as seen on their right side.
The list doesn't have the ...
0
votes
0answers
35 views
How to write a conditioning rule referencing another cell in Google sheets?
Problem:
I want the color of the cell to change based on the value of another cell.
I have a cell (B1) with MTD capture that is calculated by taking asset A and dividing it by Asset B in different ...
1
vote
1answer
26 views
Is there a formula to automatically populate invoice with only the products that have been ordered and excludes the products not ordered?
I have a range of products in my order data sheet and I need help creating a formula that automatically fills up my Invoice sheet with only the products that were ordered.
Currently I can only ...
0
votes
0answers
30 views
Is it possible to use a cell containing date as data validation in query statement
Is it possible to use a cell containing a date (as data validation) in a query statement?
0
votes
2answers
69 views
How do I change the date to the coming Monday every Friday at 9:30 pm in Google Sheets?
I would like to write a function that checks what day of the week today is, and whenever it is Friday 9:30 pm it should populate the cell with the date of the coming Monday.
So since today is Friday ...
0
votes
1answer
35 views
Multiple IF functions on a single cell
How do I add multiple IF functions on 1 cel, without blocking it on the first line.
As you can see, column D contains a bunch of text. In column A, I want to categories them.
It works up until the ...
1
vote
1answer
30 views
Use importhtml to import a single cell vs a whole column
As the title would read, I am looking for a method to import data from a retail site to my current spreadsheet to have the prices update in real time. Unfortunately I am not educated in this so I am ...
2
votes
1answer
31 views
Keeping a cell formula set to the same cell even when a row is inserted
I have one sheet that needs to pick up singles sells for a lot of other sheets.
On the other sheets I have to change the information each month and I work across the rows so I insert a row at the top ...
0
votes
0answers
30 views
Putting multiple arguments/conditions
How can I put multiple arguments in a cell using Count IF in Google Sheet when I am providing separating the arguments/conditions I am getting error message?
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:
1
vote
0answers
50 views
How to keep same protections/settings when making a copy of a Google Spreadsheet? [duplicate]
I have a spreadsheet that I want to create about 100 copies of at once, all copies with different titles. I found a script that allows me to do that, all I have to do is fill out information from cell ...
0
votes
0answers
75 views
How to separate direct sibling elements into different columns?
So basically I'm trying to import a list of values from a certain web-page into my spreadsheet and I'm stuck. I want to make a list of names with their corresponding number stages next to them, all ...
1
vote
1answer
151 views
Daily Average Calculations in Google Sheets
In the editable spreadsheet below I have a column of dates (column A) alongside a column of data (column C).
I want to calculate the daily average of the data in column C and enter it in columns C and ...
2
votes
1answer
57 views
Compare number in one cell to multiple cells and give out nearest number
https://docs.google.com/spreadsheets/d/1Dew-CLxHvMW2BgI9axdJTEI-2CezTx7k6Vt1R48mwgA/edit?usp=sharing
Above is the sample sheet. I need a formula in K cell
