Questions tagged [formulas]

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

0
votes
1answer
15 views

How to get the SUM total value to appear in cell vs the formula?

So I have created a spreadsheet to add cells =SUM(B2:B20) ... When I hit enter the sum value should be in the cell I created the formula but instead, I have just the formula after hitting enter. How ...
2
votes
1answer
16 views

How do I create multiple time zones by using the NOW formula?

I'm trying to create 5 cells with different time zones: CDT MDT MST PDT EDT I am in MST, so I obviously get my current time by doing NOW() How do I add hours to that? I tried NOW()+(2,0,0) But ...
0
votes
1answer
42 views

Filtering a QUERY using MATCH - how to pass column value into function? Or is there a better way?

I want to get a subset of data from another tab. Eventually, this will become a script and pull data from a different sheet. I can use QUERY to get all the data available, but I only want to return ...
1
vote
1answer
24 views

Insert a weekday in Google Forms' date selector

We have created a Google form that works perfectly, there is only one issue, and that's the DATE selector, it sends the DATE, but we would like to receive the DATE and the WEEKDAY in the form we ...
2
votes
1answer
16 views

How to SUMIFS text beginning with apostrophe

I am receiving a list of products from an external data source. One of the product names begins with an apostrophe. When I attempt to produce a summary of the products and total quantity using UNIQUE()...
1
vote
1answer
60 views

How do I select multiple columns using Google Sheets =CHOOSE() function?

From this table... ...the following formula in Excel... =choose({1, 2}, E18:E23&" | "&F18:F23, G18:G23) ...gives me this {“UK | Primary”,555;”France | Primary”, 543;……} However, in ...
0
votes
1answer
22 views

Remove all text before (and excluding) match

Hi I have a cell with data such as: Dropped pin Near Hughanden Village Hall, Pome Ln, Hughanden, Low Slokey, HT11 3AT https://goo.gl/maps/dVtKw423RBA9fkGs7 I would like to use REGEXREPLACE (or one ...
1
vote
1answer
16 views

How to find the average of difference between two columns?

Imagine you have a Google sheet with two columns, each holding a time - let's say one is bus departure and the other is bus arrival. Is there any elegant way to present the average of all the time ...
0
votes
2answers
15 views

How to take the values from one cell into multiple cells going down vertically?

If I have something like: 0.04, 0.04, 0.09, 0.38, 0.67, 0.92, 0.94 in one cell and I want to split the 7 values going down 7 cells, so all the values are in cell B9 but I want each value to be in ...
1
vote
1answer
26 views

Alternatives to dragging formula down?

I'm having trouble applying the following formulas to entire columns in Google Sheets. I think that an array is the best option because there are 5000+ rows, but any help is appreciated. I'm really ...
1
vote
1answer
18 views

Shortening this Formula, and Future Proofing it

I am creating a Google Sheet to use as an employee schedule. On this sheet, I have Cells which will contain the Total number of Hours worked per day by all employees. At the moment the formula I ...
0
votes
1answer
21 views

Inserting text values into a formula in Google Sheets?

In Google Sheets, when referring to another worksheet in a formula, is there a way to insert the text value of a cell as the name of the sheet to be referenced? It is essentially a data collation ...
1
vote
2answers
43 views

ArrayFormula combined with ISBLANK

Kind of new here. Tried to look for a similar answer but not quite getting it to work. I made a formula - it worked. I turned it into an array formula - it worked. But it was returning 0's in the ...
1
vote
1answer
103 views

Query(Importrange) or Query with dates

I am trying to show or limit what is seen to just entries made in 2019. I found a few places that referenced this and have tried the following without success. =query(IMPORTRANGE("1bSyor-...
0
votes
1answer
15 views

How to simplify a specific formula?

Example sheet here. Basically, I want to find out how many days have had their values entered, then average the totals across the number of days. If a day's value hasn't been entered it will be blank,...
0
votes
2answers
103 views

Custom SHEETNAME function not working in Arrayformula

I have been successfully deploying the sheetname function outlined in Display sheet name in Google Spreadsheets for a couple of years. It has allowed me to have several sheets collecting information ...
1
vote
1answer
15 views

Insert value after analysing 2 cells

I have a sheet that will a tick box in column J. When another tick box is ticked in column E I would like the sheet to check if column J has a tick in it and if so insert the value of 1.5 into column ...
0
votes
1answer
12 views

Percentage from subtraction

I currently have an issue with a statistics sheet for a sports team. If I have a total amount of practices, with the number of days missed subtracted for each player. How do I turn that into a ...
0
votes
1answer
21 views

How to use arrayformula to find and get data from only a certain, dynamic set of columns?

I have a table where my headers are dynamically generated from another source and can show up with any header values, in any order. In the example below I've got the header values: AA - BB - CC - ...
0
votes
2answers
6 views

Look for a value that might be in many columns

Say I have a value in $A, to match to another sheet of the same file. That value (in this case, a student id) might appear in many columns of the other sheet. In the following example, I am looking ...
0
votes
1answer
79 views

ImportXML not stable in Google Sheets, how can I improve that?

I have ImportXML in my spreadsheet. I am tracking the Alexa ranking of a list of websites. Here is the formula: =HYPERLINK(CONCAT("www.alexa.com/siteinfo/",A4), value(ImportXML(CONCATENATE("http://...
0
votes
1answer
15 views

Change Colors of Cell per Day based on Date in Cell

I'm new to Google Sheets and need help with conditional formatting. I have Column C, RESPA DATE, that has a date that varies for each borrower. I want to change the color of the cells in Column D, ...
1
vote
1answer
15 views

Finding the average from the last 14 days in Google Sheets

I would like to figure out a formula that will look at the last 14 days of data in column D and calculate the average. But I would like it to dynamically update every day so it's always showing the ...
0
votes
1answer
25 views

Can Weekday value be shown in natural (Su, Mo, Tu..) format in Array Formula?

For Google sheet with the following data in A2:B4 06/29/2019 1:25 PM 1 06/30/2019 1:32 PM 1 07/01/2019 1:40 PM 1 The output will show Monday 1 1 Saturday 1 1 Sunday 1 1 Can this be ...
0
votes
1answer
28 views

Look up a list embedded in a Google Sheets from a Google Forms, and add attributes to the corresponding row?

I'm running an event for a large number of children and need a way to let them register quickly as they come in, to the event. We're expecting about 400 kids who have pre-enrolled and we will have a ...
0
votes
0answers
15 views

How to compile sets of cells with a formula in Excel?

I have a spreadsheet with specific sets of cells that I would like to be able to just tick a checkbox (or something similar) which ones I need to make a one full set on a main sheet. Example (width/...
0
votes
1answer
26 views

Count in QUERY to include counts for all unique items (dates)

=query(A:B,"Select A, Count(B) where A is not null and B='A' group by A,1")
0
votes
2answers
18 views

Prevent shifting of rows in auto-updating sheet

I am struggling with an automatically updating spreadsheet that is connected to a Google Form. Whenever there is a new accomplished answer in the Form it is adding a row in the spreadsheet, resulting ...
0
votes
1answer
7 views

Sorting column A with drop-down and column B with checkbox

I have a spreadsheet that I use to run a foosball tournament, and in column A I have a dropdown data validation that will let me select a players name. In Column B I have a checkbox that I check once ...
0
votes
1answer
62 views

Equivalent of IMPORTRANGE with script in Google Sheets [closed]

Since the Importrange() is not working as expected, do you know how I could express e.g the following function with a script? ={IMPORTRANGE("SheetKey","SheetName1!A:J") ; IMPORTRANGE("SheetKey","...
1
vote
2answers
20 views

Google Sheets' Duration Input issue

We have been using Apple Numbers which seems to have this duration thing figured out a little better than Google, (either that or I'm missing something). In Apple Numbers, if I type into a cell with a ...
1
vote
1answer
23 views

How to change row colour depending on due date in a cell

I need a whole row to be red if the date in column C is older than today() I have tried this basic formula =C2:C<TODAY() but that makes every row that is still empty also red. So how to add ...
0
votes
1answer
23 views

Subtract different percentage based on quantity range

I want to be able to provide a different percentage discount based on the quantity purchased. The percentage should be based on a range, so for example: QTY > 0 && <= 5, discount = 5% QTY >...
0
votes
2answers
31 views

How can I pretty-print a formula in Google Sheets?

Without proper line breaks and indentation, even moderately complex formulas are visually not well navigable in Google Sheets. How can I pretty-print them? I know I can create a line break with Ctrl +...
1
vote
1answer
16 views

Formula works in cells but not in conditional formatting

So, I have a Gantt chart style diary. I want to apply conditional formatting to any column whose date (in row 2), is a public holiday. There is a sheet called 'Public Holidays', that has the dates ...
0
votes
2answers
33 views

IF and THEN statements on spreadsheets using color

I am creating spreadsheets with multiple tabs for children to work through. To show they have completed each tab, when they have finished the work correctly I am using the following statements: =IF(...
1
vote
1answer
12 views

COUNTIFS issue, output is always zero

I have no idea what I'm doing, considering there's plenty of topics on COUNTIFS. However, each solution I've tried, as well as my own solutions, which I shall list, end up in the same thing... ZERO. ...
0
votes
1answer
10 views

How to replace reference cell and have it affect the main cell if it referencing?

I asked a similar question on the Google Support Forum. I was wondering how I could reference a cell with any sort of value in it to another sheet (inside the same Google Sheets document) and then ...
0
votes
1answer
37 views

How to select existing sheet to be populated by a Google form?

I have a team working in pan India with 6 Team leaders. I have created a Google form to collect the everyday status report. Now the question is: How can I assign a particular sheet[already created in ...
0
votes
1answer
22 views

How to use a cell value to define the sheet on IMPORTRANGE

Trying to create a spreadsheet that reads values from a certain sheet of another file using importrange formula. I am trying that the referenced external cell be a cell on the current one so I could ...
0
votes
1answer
77 views

How to import historical open price in Google Sheets with GOOGLEFINANCE?

For some reason, I can't get the google finance function to work with any open-price lookups I do. For example: =GOOGLEFINANCE("AAPL","priceopen", DATE(2019,4,12)) comes back as #N/A with message: ...
0
votes
1answer
22 views

Simple Scorecard Conditional Formatting

I simply want to create a scoresheet comparing scores to par. If less than par then a red number, if more than par then highlight red, if equal to par then highlight green. Here is what I'm trying ...
0
votes
1answer
24 views

Using a cell value, to dynamically create a formula, that links to a cell in a different sheet (tab)

Ok. This is a bit complicated, but I will do my best to describe the problem. All of these elements are located in multiple Sheet pages (Tabs) within one singular Google Sheet (Workbook). Sheet 1: ...
1
vote
2answers
31 views

How to keep a checkbox value to reuse it later in Google Sheets

I'm working on a Google sheet actually (only for fun) and I'm actually stuck on something, as I said in the title, I want to keep a checkbox value in Google sheet. I will explain my problem. I'm ...
0
votes
1answer
28 views

Grabbing only non-empty cells from a row range for every row

I have a survey that sends responses into a Google sheet. The raw data that is inserted is messy, so I have another sheet that processes the incoming raw data. One of the things I need to do is: from ...
1
vote
1answer
57 views

Compile cells when cell contains certain value

In Google Sheets, I have a range of values as follows: I am looking for a formula that will search the range, find all of the cells that is a 'wire' (contains the term "wire") and outputs the range ...
0
votes
2answers
17 views

Format cell if all the cells in a row are not empty

I tried all kind of formula but they all seem to work the other way around for me. I have a very large sheet with lots of columns. I'd like to have the first cell in the row to be a certain color if ...
1
vote
1answer
25 views

How to convert multi-value answers into single column in Google Forms' response sheet?

I have a Google form that asks for Name and up to 3 Score. The data is dumped into a sheet like this But that data is not very convenient. I want to have the data like this... Can I do that by ...
1
vote
1answer
20 views

Finding multiple “most common values” in a column

I am trying to display the 5 most commonly appearing text values in a column in Google Sheets. The following will give me the most common value in column A: =ArrayFormula(INDEX(A:A,MODE(MATCH(A:A,A:...
0
votes
1answer
14 views

Copy Google form and linked sheet preserving existing formulas and formatting?

I am required to copy an existing Google Form, however, the linked results sheet has deep formatting and formula. Apparently, the new form requires a new sheet to be linked to form results. How can ...