Questions tagged [formulas]
Formulas used in spreadsheets edited by web applications like Excel Online, Google Sheets, among others.
1,734
questions
0
votes
1answer
8 views
Multiple Columns, Multiple Criteria Count
Sorry if I don't word this perfectly, but I'm trying to count multiple options in multiple columns. The current formula I've attempted is:
=COUNTIFS('Data Dump'!AA:AA,{"Awaiting Copy","...
0
votes
1answer
28 views
Need help with Google Sheets!
0
I need help figuring out a formula. In this example: Column E is Start and F is Finish. I want to write a formula in column J that writes an "X" with the following conditions:
E > 7:00 ...
0
votes
1answer
11 views
Google Sheets IFS Text result based on multiple conditions FIX & SORT
I'm very new to Google Sheets and I'm trying to make find the correct formula for a text result based on multiple conditions, here is my best crack at it:
=arrayformula(IFS(B9:B="",V9:V=&...
1
vote
1answer
19 views
Using multiple IF statements to return a value based on multiple ranges
I'm trying to write an IF statement for whether an engine size is small (900 - 1400), medium (1401 - 2000) or large (>2000).
The engine sizes are in the B column.
I'm not really sure how to ...
0
votes
1answer
37 views
How to insert first numeric value in a column?
I'm trying to create a formula which will in the absence of data for itself, reference a column for the first piece of numeric data. However, I'm not sure what formula I should be using. Below is a ...
0
votes
1answer
16 views
How to make an output of a column that "filters" through another?
Essentially, I have a Column A with a bunch of websites, and Column B with a bunch of acceptable websites. I'd like column C to display the websites from Column A, except those that contain url's from ...
0
votes
0answers
28 views
How to create a google sheets formula to return a value based on % values in other cells & weighted average formula? (FIXED)
I've been trying to create a scoring system for expected stock market returns -
Sheet
I'm having problems creating a formula to firstly give stocks a score in multiple conditions for example SS
I want ...
-1
votes
1answer
35 views
Get a single row with true and false if the string is contained in a range
First of, sorry for the title, I have no clue what to search for or how to name the question.
I got an example sheet which might already explain everything:
https://docs.google.com/spreadsheets/d/...
0
votes
1answer
25 views
Filter function in Google Sheets
I need help filter some data in Google Sheets. I want to create a FILTER() function that filters between 2 columns and also discards duplicates. Something like the UNIQUE() function between 2 columns.
0
votes
0answers
15 views
Auto populate comment
Is there a way to have google sheets auto populate a comment based on specific entries in a cell? I am trying to have google sheets auto populate a google form response if certain content is entered ...
1
vote
1answer
13 views
Conditional Formatting Help
I must be having an off day as I'm usually good with formulas - but I'm trying to finalize something for work and my brain is not getting me there....
I need a formula to color cell G75 Orange if cell ...
0
votes
1answer
11 views
Whats wrong with this formula in Google sheets? Im trying to get info from another sheet, but I need the cell to be dynamic
Heres the formula:
='Sector info'!(CONCAT("C", B2)) there's 2 parts to it, the part before the ! which references another sheet, and the second part, that lets me change the row im looking ...
0
votes
1answer
37 views
Google Sheets formulas: how to return where a MIN or MAX values within a range were found?
I'm trying to find a way to get the index of a cell where the value was found; more specifically, I want to return the numerical value of the row where it was found, as I need the number to be used in ...
-1
votes
2answers
27 views
How to add 2 cells if the value of 1 cell is >0, if its <0 then minus?
I tried googling but without luck.
How do I add =ADD(E19,G19) but only if g19 is >0. If g19 is <0 then Minus?
0
votes
0answers
17 views
Reference same cell from multiple worksheets automatically with list going down? [duplicate]
I was wondering how can I make my main /master sheet more automatic so I don't have to input every data such as =sheet1!b2, =sheet2!b2, etc. I was thinking if there is some way to make it more ...
0
votes
2answers
52 views
Conditional Formatting 'Blocks' of cells
I am wondering if it's possible to use a custom formula in conditional formatting to highlight a single 3x3 block of cells - rather than a single cell, an entire row/column, or the entire range - ...
0
votes
0answers
21 views
Change cell colour in mastersheet if cell in secondary sheet contains any text
I'm new to this and trying to find a way to change the cell colour of a mastersheet in my schedules document on the basis of an individual schedule in a different sheet containing text (any text) in ...
2
votes
1answer
45 views
Can I have a drop-down list appear in a cell only if another cell contains a specific value?
What I mean by this is that I want a cell to show a drop-down list (the only kind I know of is from Data Validation) only if another cell (NOT containing Data Validation/drop-down list) has a specific ...
0
votes
1answer
25 views
Need To Randomize Selections From A List But Ensure Uniqueness
I'm building a tool to randomize setup for a very large modular card game. There are several lists from which I need to select 2 to 6 non-duplicate entries, and each list is on a separate tab from the ...
0
votes
2answers
30 views
Text EXACTMATCH + other characters
I have a list of items that have the main item name plus some others that have customization in them, like below.
What I'm aiming for is to find how many that are M01 - Supreme Pizza + OTHER TEXT. ...
0
votes
1answer
36 views
Extract Data from 2 columns if it matches condition
This is how my main data sheet looks like
And for any item that has customization, for e.g. - ORGANIC, I want to extract/copy just those data, the meal name & total units to another sheet.
And ...
1
vote
1answer
36 views
Arrayformula to get closest non-numeric value above
I have Column A with mixed data types (numbers and strings) and in Column B I want for each numbers in Column A to display the string that is the closest above. Example:
Title|
1|Title
2|Title
Next ...
1
vote
1answer
46 views
How to get weekly averages for workout tracker data in Google Sheets?
I am making a personal training app that uses data from Google Sheets. I want to create a formula that takes the data from a sheet of workout tracking inputs and creates a weekly average for each ...
0
votes
1answer
18 views
Combining year date into autofill google sheet
So I am trying to create a basic daily finances tracker using google sheets. And I want to be able to have it so that yearly tabs reference a template sheet that allows you to change the year on each ...
0
votes
1answer
33 views
Google sheets reference Text in Cell in Array Formula & Countif formula
So, I have a Array Formula and Countif formula in place because I need to know the total of entries in a combination of ranges. I need to know the sum of the number of cells that have "X" ...
0
votes
0answers
51 views
Array Formula Problem - How to Fill In Blank Rows with the Same Value before a Breakpoint which advances to the next letter
I have a Google Sheet which is grouped into segments of a TV show. So there's an A segment and then a B segment and so on ... I have allocated two columns to provide two different timing functions (...
0
votes
1answer
50 views
Google Sheets Drop Down Menus that remove used options
I am attempting to use Google Sheets as a scheduler of sorts for some rooms.
A1:A4 is a list of room names.
B1:B4 is using this formula =IF(COUNTIF($H$4:$H$6,A1)>=1,"",ROW()) Where the H ...
1
vote
0answers
37 views
Use ArrayFormula to automatically update the column with a formula already containing an ArrayFormula
I am using the below formula to concatenate some data into one string:
=TEXTJOIN("",FALSE,ARRAYFORMULA(IF((Data!$F$3:$F=$C2)*(Data!$G$3:$G=$D2),Data!$L$3:$L,"")))
I however have ...
0
votes
1answer
18 views
Sum of rows in one column based on values in adjacent column
I need to sum a range (D20:D22) but exclude any values in an adjacent column (G20:G22) where the Column G values is <-2. The resulting value is the divisor in a second calculation.
For example:
...
0
votes
0answers
22 views
Short number format in Google Sheets in Indian format (Lacs, Crore etc); 1,53,642 → 1.54 Lacs, 28,18,76,724 → 28.19 Crore
How to truncate large numbers in Indian format? This question answers it perfectly for the US system. The custom number format for Million and Thousand is:
[>999999]0.0,,\M;[>999]0.0,\K;0
Need ...
0
votes
1answer
7 views
INDIRECT for dynamic inputs for IMPORTRANGE in Google Sheets?
I'm trying to aggregate data across several different Google sheets using IMPORTRANGE (or a similar function). The challenge I'm having is I need to reference a dynamic sheet name.
I'm importing an ...
0
votes
1answer
19 views
How do I use a drop down of sheet names to change the data pulled using the FILTER function?
I am creating a performance tracker for my employees, and I'd like to have a sheet named for each person's first name, with all the metrics by date. I'd then like to create a performance page that ...
0
votes
1answer
25 views
How to count the number of times a certain label appears within a date range using Google Sheets?
I often have this problem and haven't found a good solution. It drives me nuts!
I have a set of data with 1000s of rows and each has a date and some label -- might be salesperson, category, region, ...
0
votes
1answer
22 views
Google Sheets Query: Order By and accented chars
In a Google Sheet, if we have a column with accented character strings, the sort order of menu option "Data -> Order sheet by column" correctly ignores the accents (ordered column A in ...
0
votes
0answers
14 views
Conditional content in column A based on value in column B, with content determined by column C
I am trying to automate a task where the contents of one column is informed by the (text) value in another column. The content should be pulled from a list in the spreadsheet.
For example:
In this ...
0
votes
1answer
23 views
How to make Row() formula dynamic based on a cell content?
I am creating a multiple choice (see column A) and I want it to be randomized (see column C). I saw a formula in the internet, modified it, and it really does randomized the contents of each cell in ...
1
vote
2answers
34 views
Array Formula omits IF / AND functions
I'm using a series of IF and IF/AND functions to assign a set value according to, sometimes one set or two set of colums. While creating a range and Array Formula to apply that to all posible new ...
0
votes
0answers
24 views
Why doesn't my function work in Google Sheets?
=IF(B2=0,0,800*2(B2-1))
Why won't my function work in Google Sheets? Why can't I get an equation to work inside an IF function?
0
votes
1answer
19 views
How to calculate compound interest based on bottom x rows of a spreadsheet in Google Sheets
Let's say I have a table with a percentage interest on each row. I would like to know how to calculate the compound interest based on the bottom x number of rows.
For example, if each row is a day, I ...
0
votes
1answer
45 views
Google Sheets - Return a text value based on another cells text value
I have a set group of "program names" in a column, and want to have another column automatically write the matching "acrononym" for its sister value.
For example: Master in Finance ...
0
votes
1answer
49 views
Create A Result Limit for Array Column. Spill Into Next Column. (Google Sheets)
I'm looking for a formula that lets me take a dynamic column range and tell it to spill into the next column if it exceeds a certain number of rows.
The most rows I want in one column is 10,000. The ...
0
votes
0answers
22 views
Google Sheets: SUM of the LAST value - apply across multiple columns
Use Case: I have multiple financial portfolios that cannot be connected to via API. I want to track my total balance across portfolios. I do not want to tally every portfolio every day - instead, I ...
0
votes
1answer
27 views
Formula change needed every 24 rows
I think I'm missing something very basic here :( - My 1st sheet "Time Pred" -> MY SHEET LINK
Trying to get formula in Col E to change every 24 rows- it should refer to Col D cells ...
0
votes
0answers
12 views
Google Sheets formula - ordering the # of times text occurs
I am preparing for my fantasy football draft and I was wondering if there was a formula I can use to generate rankings by positions. I'd like to use a formula to look at the list rit large and give me ...
0
votes
0answers
21 views
Can I convert a number in a cell to read as text in another cell?
I have a numerical value in a cell. If the number is greater than 2 then I'd like it to say 'YES' or 'Y' in another cell. If it's equal or less than 2 then 'NO' or 'N' - is that possible?
0
votes
0answers
10 views
Date computation dependent on another cell that has a data validation option
I am working on a lead generation tracking spreadsheet in Google Sheets with the hopes of having a follow-up date auto-populate. There are different amounts of time when we want to follow up with ...
0
votes
2answers
53 views
Adding up cells in a column till the value of sum reaches a certain value - Google Sheets
I have a column with a list of numbers. I want to calculate the sum of the numbers, starting from the top until the value of sum reaches a fixed value, and then stop and return the cell address of the ...
0
votes
1answer
15 views
REGEXMATCH for single cell min/max character criteria?
I'm very new to the regex scene so forgive my level of intelligence! I want a single cell to be 5 or 7 characters long via data validation and an error message for a false return.
I've come up with ...
0
votes
0answers
14 views
How can I reference values from one list in the order of another list?
I don't know if that title even fits but here is the setup:
I have drawn numbers randomly from an urn without putting them back and I have a list of that in a row. For a small example's sake lets say ...
1
vote
1answer
16 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 ...
