Questions tagged [formulas]
Formulas used in spreadsheets edited by web applications like Excel Online, Google Sheets, among others.
2,029
questions
-1
votes
1
answer
25
views
Additional Line impacting dependent drop downs
I have an issue when adding a line into a sheet where it kicks the drop down dependencies out of order.
Column E is a list of option dependent on the input of column D. All works fine, referencing a ...
- 1
0
votes
0
answers
87
views
How can I solve this formula?
=index(importhtml(concatenate("https://finance.yahoo.com/quote/";A6;"/key-statistics?p="A6";"EN_US");1;2)
A6 is tiker: MO
I need Dividend date: Oct 10, 2022
Original ...
- 23
0
votes
1
answer
16
views
Where should I put Upper Formula to work in my formula
=IMAGE("https://logo.stocklight.com/NYSE/"&$A$21&"_icon.png";4;80;80)
A21 is TEXT : MAIN
how I use upper for A21 to work with my formula?
- 23
1
vote
3
answers
44
views
How do I inhibit conversion of `(3)` to `-3` within a Google Sheets formula?
=index(split("A (2)"," "),2) renders as -2 even when cell number format is set to "Plain text". It also breaks further functions like REGEXREPLACE that expects text, not ...
- 627
0
votes
1
answer
263
views
How to use IF/SEARCH to check if a cell contains a substring, and return one of five strings to effectively "label" the cell?
I have a column in my Google Sheet that contains the title of each of our e-blasts from the past 7 years. I want to create a column that provides the "type" of the e-blast, using IF/SEARCH ...
- 3
-1
votes
3
answers
78
views
Multiple on edit in one gsheet
I'm trying to create a spreadsheet for my team, and using this script I am only getting one of the on edit if statements to function. How can I fix this? I'm not very versed in script writing.
...
0
votes
1
answer
33
views
In Google Sheets how do I return the total number of scenarios that match two conditions?
I am creating a summary within a large spreadsheet and want to be able to show the total number of items that meet two criteria.
Criteria A is in a range in one column, and Criteria B in another. Both ...
- 3
1
vote
1
answer
711
views
Stack two sets of data into one in google sheets - different columns
I am looking for a solution that helps me stack two sets of data (from diff. columns, different order) into one data sets
EXAMPLE:
columns names + order tables1:
first name, last name, city, age, ...
- 11
1
vote
1
answer
402
views
How do I calculate average of all days between 2 date columns in Google Sheets
I'm having trouble creating a formula, which would calculate the average of all days between 2 date columns into one cell.
I've tried Average(days(A2:A1000,B2:B1000)) and Average (B2:B1000-A2:A1000), ...
- 13
1
vote
1
answer
28
views
How to use a cell to select which sheet is used in formulas
In my spreadsheet, I had been adding values to a sheet named « Block1 » for a month. At the end of the month, I analysed this first sheet with an other sheet called « Analyse ».
At the end of the ...
- 13
0
votes
1
answer
21
views
Populating Date Column in Sheets from a Cell with manually-entered Month
I'm creating a monthly form in Google Sheets.
In one cell the month is entered manually, eq. December 2022.
I want to dynamically populate a column of dates which references the month, so the column ...
- 1
2
votes
1
answer
56
views
How to retrieve datas by a filter based on a merged cell?
In sheet1 (Source), I've a full calendar containing datas (dates in same row)
On 30/10/2022 in AM, SHIFT 1 has to work with ITEM A
On 30/10/2022 in PM, SHIFT 2 has to work with ITEM B... etc see ...
- 53
-1
votes
1
answer
38
views
Is there a formula in Google Sheets that can preform an equation only if certain conditions are met?
In Google Sheets I have a column (column M) which has values of either "-" or a number. I'm trying to figure out if there is a way to make a formula that would divide column M by column N ...
- 1
1
vote
2
answers
118
views
Strange behaviour with iterative calculations on; identical formulas give different results?
So I have a cell with this trick in it to prevent the TODAY() function from updating every day:
IF(C1=0,TODAY(),C1)
Works perfectly for its purpose. However I have other cells with formulas that refer ...
- 13
1
vote
2
answers
26
views
Combining TEXT and YEAR returns odd result
In cell A1 I have this date and time: 2022-01-01 16:00
If I do this =Year(A1)-1 the result is as expected 2021
If I do this =TEXT((Year(A1)-1), "YY") the answer is 05
Can anyone explain what ...
- 13
0
votes
1
answer
155
views
Dynamic IF statement that updates once condition met
I'm struggling to work out how to forecast inventory for my ecommerce store.
https://docs.google.com/spreadsheets/d/1X4pdpA-6c4bo-H6NptIU-A2KglbUV61LrR97bhmdrPc/edit?usp=sharing
Essentially, I need ...
2
votes
4
answers
72
views
Is there such a formula as IF/AND/OR in Google Sheets?
I'm working on this spreadsheet, field B10.
https://docs.google.com/spreadsheets/d/1wC-y0T9QeF6MZALavljzqVLJTfadSHmGt0yOFYgIMnE/edit?usp=sharing
I'd like to apply this formula, but I'm not sure how:
...
- 35
1
vote
2
answers
51
views
Formula to autofill value from a list on a separate sheet
In a Google Sheets workbook, I have a sheet named Tax Codes and Rates containing a tax table with the following 4 columns: County , Municipality or County , Location Code , Rate
Sample Lookup Data
...
- 35
-1
votes
1
answer
16
views
Subtracting Amounts from Specific Categories
I'm working on trying to make a relatively simple spending tracker using Google Sheets. In this example, the person has $5000 already in the bank, and they have earned $1000. The categories of Housing,...
- 1
-1
votes
1
answer
14
views
Which Google sheets formula?
I have multiple columns (say columns A to E) which contain various values. Some values are present in more than one column. I'd like to know in which column(s) a specific value is present - something ...
- 1
0
votes
1
answer
40
views
SUMIF Formula for Partial Text and Partial Numbers
Aim
Currently trying to create a SUMIF formula that recognises the partial text in a range and returns the sum of the number reflecting it.
.
Research & Results
I have looked into SUMIF for ...
- 33
0
votes
1
answer
31
views
Duration Format Change
Is is possible to change a duration format from for example '47 Years 3 Months' to just '16,822', representing the amount of days in said duration?
- 3
1
vote
1
answer
46
views
Is there a formula to count occurrences of a string in another sheet when the string needs to be "looked up" in a different sheet? (Quiz example)
Sorry for poor title wording.
I am keeping track of quiz questions in a Google Spreadsheet. In order to generate question statistics I also keep track of some metadata about each theme in a different ...
- 111
1
vote
1
answer
130
views
How to add text conditional on numerical ranges in Google Sheets?
I'm using Google Sheets and I want to display certain text based on the numerical value of another cell. I am a teacher trying to create an assignment where students have to find the best zombie ...
- 11
1
vote
1
answer
28
views
Google Sheets IF THEN question
I have two columns of data, one column will have either "cc" or "cash" in each cell, the second column will have $amounts. I am trying to have it automatically know whether each ...
- 11
1
vote
1
answer
34
views
Copying an absolute block range but updating the row numbers
I have the following data sheet
E
F
G
H
I
1
2
3
4
5
6
7
8
9
0
0.1
0.2
0.3
0.4
0.5
0.6
0.7
0.9
0.8
0.1
I would like to implement a conditional formatting formula that would:
bold the maximum ...
- 113
0
votes
0
answers
31
views
Formula to calculate pay when the first hour worked is a different rate, with breaks
I'm trying to create invoices for publicly-funded interpreters. They are paid $X for the first hour (or fraction thereof) they work on a given case, and then $Y (which is less) for each additional ...
- 111
0
votes
2
answers
181
views
Array Formula Countif Checkboxes
I'm working on a spreadsheet (in google sheets) for tracking characters' magical abilities.
In column b I want to have a count of how many boxes are ticked. Right now it's just the same formula =...
- 3
1
vote
1
answer
522
views
How to use IMPORTRANGE across discontinuous columns? (Or, how to omit columns?)
How are discontinuous columns imported? Here, the import is inclusive from B3 to D7 as:
Here, the formula is:
=importrange("https://docs.google.com/spreadsheets/d/...
- 686
0
votes
1
answer
18
views
Return sum of values to the right of columns matching criteria
I have each row of data organized in column pairs. In my example below, the column pairs would be A:B, C:D, E:F, G:H and the pattern would continue.
The first column in each pair contains "...
1
vote
1
answer
34
views
Communicating across different Sheet files
Excuse my poor terminology, I'm a casual Sheets user.
I have a business with 5 different, somewhat complicated revenue streams. So I made a budget sheet for each of them. I end up with a GROSS PROFIT ...
- 11
1
vote
2
answers
14
views
List words in a phrase that also appear in a range
I need help with Google Sheets to search part of a cell within a range. My formula works in Microsoft Excel but not in Google Sheets:
A
B
C
dog
this cat is very friendly
=LOOKUP(REPT("z",...
- 11
1
vote
1
answer
43
views
How can I conditionally format any empty cell from column C to L if the respective cell in column B not empty
I need your help with conditional formatting in Gsheet or Excel (I will choose one of them depending on the solution).
This format needs to be: Any empty cell in columns C to L must be highlighted if ...
- 11
1
vote
1
answer
64
views
Insert URL inside Google Spreadsheet [escaping characters?]
OK, the big picture is to use all my API Steam results inside Google Spreadsheet so I can create a database of my games and profile.
note: I don't want to share my private STEAMID for security reasons
...
- 111
1
vote
2
answers
19
views
Is there a formula for having a number in one cell be represented as a range in another cell by adding and subtracting numbers to it?
I have a number in one cell, for example 2.6, and in the adjacent cell I would like it shown as 2.1 to 4.6 (maybe displayed as 2.1-4.6 in the cell). This 'range' comes from subtracting 0.5 from the ...
- 11
0
votes
1
answer
582
views
Number of Days Positive and Negative Between Two Dates?
On Google Sheets, I am trying to create a formula within a single cell that calculates the positive or negative number of days between two dates. Specifically I am trying to determine how much buffer ...
1
vote
3
answers
34
views
'Large' 'If' Formula Help
I am looking for a formula I can put into Google Sheets that finds the 1st, 2nd, and 3rd largest values in column B, but only for rows that also contain the number 1 in column A. Problem is, column A ...
- 11
1
vote
1
answer
6k
views
Google Sheets conditional based on dropdown selection value
I have a Google Sheet with a cell (let's say its M4) that is a dropdown list that I created by:
Selecting the cell
Selecting Data >> Data validation >> Criteria >> List of items
...
- 123
1
vote
1
answer
385
views
Calculating weighted average when some values are negative
I'm trying to calculate the weighted average of stock prices but the problem is that some values are negative (where some but not all stocks were sold) and I can't figure out how to make it work.
Here ...
- 11
1
vote
1
answer
27
views
Finding the fault in a scalene triangle formula
I am trying to write a sheets document with all kinds of formulas for math, engineering and physics, and I am currently doing a formula for finding out the area of a scalene triangle knowing the three ...
- 11
1
vote
0
answers
13
views
returning value 0 when use COUNTIF
I am stuck in a situation, I am trying to count the number of releases based on each month , but not sure why it keeps returning the value 0?
example :
=COUNTIFS(M2:M5,G19,B2:B5,H18)
M2:M5 : Month ...
- 11
1
vote
1
answer
31
views
Google Sheets Remove Inconsistent Characters
I would like a formula that removes the ending of each of the text so it's consistent
https://docs.google.com/spreadsheets/d/1ysjz4B6Eh4eE5y_cYg76_CilkUena6iQDZtIIMhXSOs/edit?usp=sharing
- 87
-1
votes
2
answers
171
views
How do I make Google Sheets run a recursive equation [closed]
I want google sheets to run the equation A = 200x + The previous A value in the column. I can't seem to figure out how to do subscripts or get the previous value. Sample data 1:200 2:600 3:1200 4:200 ...
2
votes
2
answers
42
views
Google Sheets Filter Lines with no Empty Colums - AT LEASTS ONE conlum filled
I need help with Google Sheets.
Someone, know how to do a filter and sort lines with "no Blank Coluns".
I need filter names(Each Lines) - That has number 1 check AT LEASTS ONE in green colum....
6
votes
1
answer
653
views
How to sort a split string
In google sheets I have this string:
salmon,fries,peas,coleslaw,onion,garlic,chicken,pancetta,creme fraiche,onion,garlic,chicken,pancetta,creme fraiche,onion,garlic,chicken,pancetta,creme fraiche,gf ...
- 195
1
vote
1
answer
69
views
I need a formula for every 3 letter combination
I need a google spreadsheet formula that will produce every 3 letter combination.
However, I do not want any of the combinations to have duplicate or repeating letters
and
There needs to be atleast 1 ...
- 13
1
vote
1
answer
15
views
Need help coming up with formula to count and concatenate specific things
Though I'm not the best at Excel/Google Sheets (this is Sheets specifically), I'm usually able to figure out how to do the things I need. However, this one is stumping me.
Here is a screenshot of my ...
- 11
1
vote
2
answers
57
views
In Google Sheets how can I write a sequence with static words in front and a static number in back?
so I am very ameature in google sheets. I am an artist and I am organizing stock. In google sheets I have title, price, medium, sold/not sold, etc... I have print series that go up to 100. for ...
- 13
1
vote
0
answers
47
views
Google Sheets - Advanced Sumifs
So I work in marketing for a company and am analyzing survey data from our post purchase survey. I'm trying to sumifs with multiple criteria AND ranges as these ranges will update over time.
I've been ...
- 23
1
vote
1
answer
18
views
i'm not sure what function I want for this
I have a column that holds the month number(1....60) I want in another column to indicate what the number "1" is (in this case January 2023) I need a formula to look at a column, if the ...
