Questions tagged [formulas]
Formulas used in spreadsheets edited by web applications like Excel Online, Google Sheets, among others.
1,283
questions
0
votes
2answers
18 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 ...
-2
votes
0answers
16 views
Keep Cell reference in a formula fixed while adding columns to a sheet of data [duplicate]
I have a spreadsheet with multiple sheets, and many rows and columns of data. I like to count the number of each occurance of every value for the last "x" values entered. The problem is ...
0
votes
0answers
25 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
64 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
19 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
33 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
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
31 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
23 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
25 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
81 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
24 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
38 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
15 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
33 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
27 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 ...
1
vote
1answer
21 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
29 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
29 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
50 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
54 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
55 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
0
votes
1answer
16 views
How to get a cell reference used in a cell for use in a different formula?
Suppose cells in a column (TheSheet!A) contain just references to cells in the column of another sheet, but noncontiguously. For example, give Sheet!A17 the formula:
=OtherSheet!A23
Other cells on ...
0
votes
2answers
51 views
In Google Sheets, analyze cell content, and if certain values are present, return a response in the next column
In the below example, I want to analyze each cell in column A, and if the cell's contents match certain parameters, return a value in column B accordingly.
So if the value in a col A cell = apple, ...
0
votes
1answer
24 views
How to assign country based off list of either provinces or states?
I have a list of names and locations. Most of the locations include the name of the province or state. Some only include country names, some include state abbreviations.
I also have a list of ...
0
votes
1answer
39 views
How to Copy Cell Value and Format From One Tab to Another Using a Formula?
An answer was provided in this question: webapps: Cell reference with colour formatting for doing so within a tab, but not across tabs. Does anyone have suggestions for a script that will work across ...
-1
votes
1answer
41 views
Google Sheets: carry value from one sheet to another
I have 2 worksheets in 1 file. I want to bring the value from 1 sheet to another. I only want the value IF it has the name of someone.
Sheet 1
+---+-------+--------------+
| | A | B |
...
0
votes
2answers
90 views
Is there a function in Google Sheets to extract the first letter of a text in a cell?
I am trying to find a way to generate email addresses from the first name and the first letter of a last name. I used the CONCATENATE function, but it will only take a full cell, not a first letter.
0
votes
1answer
36 views
Google Sheet - Count Random Average
Number Of Invoice | From | To | Total
10 | $25 | $100 | ?
I want the Total column to fill with the average number of 10 invoice with each invoice random between the ...
0
votes
1answer
83 views
Add multiple hyperlinks in one cell, using a Formula
in Google Sheets, I am trying to add multiple URLs in one cell, using the Hyperlink Formula.When trying =HYPERLINK("foo.com","Foo") & " some other text " & ...
1
vote
2answers
23 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),"...
0
votes
2answers
63 views
Restructure (flatten) data of Google Sheets table in a new table
Column A of my Google Spreadsheet contains dates, columns B to G contain numeric values, line 1 contains the header names. Due to automatic updates the line count varies, but the column count is ...
1
vote
2answers
31 views
IF Statement works in Excel but not G Sheet?
I have a simple IF statement in Google Sheets that is not computing correctly but it is computing correctly in Excel. The formula is =IF(G15="TRUE",1,0) and G15 is a cell with "TRUE&...
0
votes
1answer
14 views
Google Spreadsheets SUMIF/SUMIFS according to a another column?
I have two columns:
Billing date : date
Billing amount : currency
I have another static cell where I want to sum all the Billing amount values, but only if the date in Billing date is greater or ...
1
vote
2answers
27 views
How do I count the number of times string “A” occurs in cell B1
Given the string "AABACCCDAC",
how do I count the number of times "C" occurs?
My initial thought was to use countif on a SPLIT of the string. But Split won't split on "" ...
2
votes
2answers
50 views
Calculate Total DateDif Average Between 2 Columns (start, end) according to year expressed in days, hours, minutes
I'm having trouble getting a formula to work to calculate the total average time between times (start and end).
Sheet: https://docs.google.com/spreadsheets/d/...
0
votes
1answer
20 views
Wildcard for any value including blank
According to COUNTIF the documentation,
criterion can contain wildcards including ? to match any single character or * to match zero or more contiguous characters.
But this is incorrect because * ...
0
votes
1answer
16 views
Google sheet - highlihghting cell if in the same row other cell not empty
I'm trying to make a worksheet where I log shift hours.
I made columns with dates and to each date a row of hours
I want the date text to be green if in the same row a cell is not empty..
The code I ...
0
votes
0answers
14 views
How can I calculate variance under certain condition in APPLE numbers
I want to do something like STDEVA(IF()) or STDEVA(FILETER()) in APPLE’s numbers, is it possible?
thank you very much
1
vote
1answer
19 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
37 views
Multipliers In Google Sheets
I'm trying to apply multipliers to rows within a column total, having issues..
I've tried multiple attempts using ARRAYFORMULA, MULTIPLY, SUM, and combinations of these, with no luck..
Search attempts ...
0
votes
1answer
29 views
How do I convert rows with 45 columns into 15 separate rows, each with 3 columns?
I am working with a google sheet that gets data from an email parser. Each time an email comes in, a single row is created, and fills in these columns:
B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, ...
1
vote
1answer
59 views
Google Sheets formula to find rows with matching values, looking up in multiple columns
What would be the Google Sheets formula to search for a matching value in a range that goes across multiple rows an columns? For example I need to search the entire range H:P (all rows and columns) ...
0
votes
2answers
60 views
How do I keep all the values when I split a cell by delimiter (comma) in Google Sheets?
I have a spreadsheet in Google Sheets, where I am currently running a script.
There are two tabs, the first tab is the following:
The second tab is the following:
This is the script I am running:
...
0
votes
1answer
30 views
How can I use TODAY() as part of a formula checking against a list of dates in Google Sheets?
I'm trying to use the TODAY() formula to check against a long column of date cells, and mark them as 'LIVE' if the given date is less than (or equal to) today. Crucially though, if the date cell is ...
