Web Applications

Questions tagged [formulas]

Ask Question

Formulas used in spreadsheets edited by web applications like Excel Online, Google Sheets, among others.

1,766 questions
Newest
Active
Bountied
Unanswered
Filter by
Sorted by
Tagged with
0
votes
0answers
18 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
53 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
61 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
26 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
53 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
20 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
53 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
52 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
59 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
41 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
19 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
48 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
13 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
30 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
26 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
25 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
36 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
26 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
48 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
67 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
23 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
106 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
18 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
15 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 ...
0
votes
1answer
19 views

Counting unique instances with multiple columns

I'm not sure if I'm wording this properly.. I have two columns - one with an event name that can contain duplicate values, and another that contains the week number the event occurred. The event can ...
0
votes
1answer
23 views

Google sheets conditional format to highlight a column containing items from another list

I have rows of data, Col. A will be the order number and there will be duplicates of the order number (this is for each individual item purchase and MUST NOT be removed to give unique numbers). Then I ...
0
votes
1answer
81 views

How do I sum the hours worked, per employee, between specified dates in Google Sheets?

I have been working on a Google Sheet that I am able to import my Google Calendar into and it automatically breaks it down. I have one more formula to figure out before its usable but I can't seem to ...
0
votes
1answer
37 views

How to add to each element of an array

I'm trying to take each element of an array that's generated from a SPLIT function and do some math on them, specifically, I want to add 3 to each element. Cell starts off with this: 1,2,3,4,5 SPLIT ...
0
votes
2answers
17 views

How to extract the decimal fraction of a number for a formula?

For a column where years and months are entered as 1.02 for one year and two months, or 3.04 meaning three years and four months, how is that converted to a date? Or just expressed so that it's base ...
0
votes
1answer
23 views

How to get error text from Google Spreadsheets?

Is there any way to get an error text from the formula in Google Spreadsheets? For ex., I see the following error - Ошибка В диапазоне функции VLOOKUP значение "zzz" не найдено. (the value '...
0
votes
1answer
45 views

Google sheets macro to run on worksheet change

I have a simple macro in Google Sheets that I can run from Tools > Macros > MacroName. I have it set up with a trigger to run when the spreadsheet is first opened. (All the macro does is go to ...
0
votes
1answer
20 views

Copy/duplicate entire rows based on number in another column

I want to be able to copy/duplicate several columns of data based on their qty in another column. I have my data in col. A- C & the qty in D. Basically it is "A-C" times D. And on the ...
0
votes
1answer
19 views

How do I get data from the 'Statement' sheet to automatically sort into the 'Expenses' sheet?

https://docs.google.com/spreadsheets/d/1xrS8_Lcuqt3rLJgEkV4SHR_XTyGCAn8t9dJyWIUtMJ0/edit?usp=drivesdk So it will read columns B & C, Everyday > Groceries on the Statement sheet and that amount ...
0
votes
1answer
29 views

Google Sheets Conditionally format a range based on values from another range

I have a column/range of emails like: jschmoe@zzz.org vwilliams@zzz.org hpotter@zzz.org swilliams@zzz.org and a column/range of names like: jschmoe vwilliams tbrady rdowneyjr caguilera swilliams I'...
1
vote
0answers
29 views

Adjusting Filter with Regex

Original Problem: Need to count Multiple Criteria from one column in addition to a single piece of criteria from 2 other columns in one tab into a count on a separate tab of the same workbook in ...
0
votes
1answer
35 views

Search a Column of Data & Enter a Specific Value based on 2 criteria

I have a sheet, where I will enter a bunch of data with skus into 1 column, and have search terms somewhere else. What I want is to automatically, look at the SKU AND the search term, and then bring ...
0
votes
1answer
45 views

Countif Multiple Criteria from within same column & Criteria from other columns

I would like to Count Multiple Criteria from one column in addition to a single piece of criteria from 2 other columns in one tab into a count on a separate tab of the same workbook in google sheets. ...
0
votes
2answers
213 views

Conditional Formatting for cells that are within 1 hour of the date and time posted within a cell

I have a Auction that I am updating and posting bids to on a Google Sheet that bidders can view. The condition to the auction is that the item will be sold to a bidder if no one has bid on said item ...

1
2
3 4 5
…
36

Your privacy

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.