Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [worksheet-function]

This tag is for spreadsheet questions where the question or answers involve an in-the-cell worksheet function.

0
votes
4answers
24 views

How to pull the page number and the number of images listed from one sheet and then populate cells in another sheet accordingly?

Sorry, I didn't know how to summarize it in the title. Basically, I have a spreadsheet where I'm keeping track of the number of images that need to be on each page of a document I'm working on. On ...
1
vote
1answer
25 views

IF statement not working in Google Sheets?

So I'm using Google Sheets and I'm trying to determine whether or not cells in a certain column AO have a higher value then cells in another column O. My formula is =IF(AO6 > O6,"UNDER","OVER"). ...
0
votes
2answers
15 views

How to compare duration to another duration using IF statements

=IF(TIMEVALUE(H2)<=TIMEVALUE("00:01:00.000"), 150, TIMEVALUE(H2)<=TIMEVALUE("00:01:30.000"),100, IF(TIMEVALUE(H2)<=TIMEVALUE("00:02:00.000"), 75, IF(TIMEVALUE(H2)<=TIMEVALUE("00:02:30....
0
votes
1answer
45 views

How to import data from Investing.com into spreadsheet?

I want to import the live individual stock price in the Google Sheets as highlighted on the image. Is that possible? https://in.investing.com/equities/jagson-airlines-bo
0
votes
2answers
8 views

Import range from 3 sheets/tabs into 4th

I currently have 3 sheets (tabs) in a Google Sheets workbook. Each column header (Row 1) in the 3 sheets are identical. I'd like to create a 4th sheet that combines ALL the contents in the 3 sheets ...
0
votes
1answer
32 views

4 IF AND Statements in one cell

Trying to get two equations to then present one of four options. Example: Have no idea where to start, all four need to sit in the one cell.
0
votes
2answers
40 views

VLOOKUP is not finding text value on other sheet

See this example spreadsheet. I get #N/A value when I try to find bbb on sheet2 using formula =VLOOKUP(A1;sheet2!$A$1:$B$4;1;FALSE) How to fix it?
-1
votes
1answer
18 views

Test if value of cell is used in a formula [duplicate]

In a google sheet I have some values that are used in formulas in other cells (until now, nothing unusual...). I would like these cells to have a special color to be aware that if I modifiy them, ...
1
vote
1answer
18 views

Is it possible to return a range from an IFS or SWITCH?

I am trying to simplify some 'if else if else' code by replacing it with a single IFS or SWITCH expressions. I have simplified all the code to be trivial but show the problem. This code works and two ...
0
votes
2answers
53 views

Can I auto populate cells in Google Sheets?

I have a bunch of categories; Bar Foo Ghost Misc Other All have variable amounts - except for category Ghost, the amount is always 15.00. I have Data Validation set up (which is how I pick the ...
0
votes
1answer
95 views

Conditional formatting with color scale for many rows independently once

I create reports with different cohort tables. Each row of a table must have conditional formatting with color scale (depending only on current row values, i.e. regardless of other rows min/max values)...
0
votes
1answer
88 views

How to reference a Google Sheet by filename?

How to reference a Google Sheet by filename? I would like to use IMPORTRANGE function, but it can only reference a file by URL not by filename. Example filename: 2018-11.xlsx 2018-12.xlsx
0
votes
0answers
54 views

How do I interleave rows of data in Google Sheets or Excel

I have data on several different worksheets and I want to combine into single sheet. The data is text, not numeric. I can do this work in either Google Sheets or Excel. I have: Sheet1 R1 Sheet1 ...
0
votes
0answers
15 views

Google Sheet - Help returning 'Top Earner' each month

I have a sheet logging participation each month by members with multiple accounts, initially used the top account to track the top earner but after adding a tab to consolidate accounts by member I'm ...
1
vote
0answers
38 views

Difficulty appending to cell in Google Sheets

My goal is to automate making a calendar on Google Sheets. I used =DATE(C1,A1,B1) to make a date and then each following cell, I added 1 to the previous cell. However, now I want to also automate ...
0
votes
2answers
52 views

Conditional formatting if entry is final entry of the day

I have a Google Sheet in which I keep track of transactions, kinda like this: | Date | Funds | Running total | |---------|---------|---------------| | 5/23/18 | 10.00 | 10.00 | | 5/23/...
0
votes
1answer
71 views

Is it possible to calculate in the background of a data validation cell?

Is it possible to calculate in the background of a data validation cell? I am creating a quite comprehensive spreadsheet that shall help me calculate dice pools in an RPG, but to not trouble you with ...
0
votes
0answers
46 views

Google Sheets Combining multiple IF statements into one formula (as an Array)

Hope someone can help, got the formula below but I want to modify it so that the first IF formula, IF(F3:F="Delay","Delay", returns a “Delay” if it finds a match (not if it finds "Delay") and if it ...
1
vote
1answer
111 views

Examine for overlap in Google Sheets?

Let's say I have the following start and end days/times: SHEET1 Resulting in a range of time from Mon 10am through Tues at 11:59pm. I'd like to know whether this range of time overlaps another ...
0
votes
1answer
66 views

Change COUNTIF range based on the current day of the week, with 3 days using the same dataset

I work as a delivery driver and have been futzing around trying to create a google sheet that automatically updates with the current day's drops. However, there is a rub with the data set. The ...
1
vote
2answers
836 views

Multiple IF statements: IF, AND, ISDATE, THEN formula

I'm trying to get a nested formula to work, but I keep getting an error. I tried using the generic formula =IF(AND(A1="this",B1="that"),"x","") but couldn't figure out how to make sure Q6=date. I had ...
2
votes
2answers
36 views

Sum points per row based on if expected value is unique within row

Given a Google spreadsheet of quiz answers (with rows for questions, and columns for people's answers), I want to work out a person's total score. The scoring system is that if only one person gives ...
1
vote
2answers
3k views

Google sheets function IF, AND conditional formatting

EDIT 2: link to test sheet: https://docs.google.com/spreadsheets/d/1IWQWDK9piw_xFVm_48XZ8YAS_LQqXi6sd2GITLIfoAQ/edit#gid=903221057 EDIT 1: added a column, so now J=K, K=L, L=M | updated formulas to ...
2
votes
1answer
511 views

Query Function with Unique References

I am currently using the QUERY function to move relative information to another sheet, which is then used for forecasting. Now what is currently happening is the same business names are showing up in ...
1
vote
1answer
237 views

Multiple IF conditions nested with a CONCATENATE

I have a formula as follows which I'm using for a scheduling system within Google Sheets: =IF(B2="","",(CONCATENATE($B$1&" "&B2&CHAR(10)&$C$1&" "&C2&CHAR(10)&$D$1&"...
1
vote
1answer
42 views

Check value even if it doesn't match validation

In my Google spreadsheet, I have a column G of dates that interviews are/have been scheduled on. There is a validation set on it to validate for dates with a warning. Every once in a while, when ...
1
vote
1answer
19 views

QUERY for like-minded rows

Scenario Consider the following query: =QUERY(A15:E861, "select B, sum(D) group by B") This works for rows with col B's that have the SAME description eg. 1 | abc | n | 10 2 | abc | n | 10 3 | cba ...
1
vote
2answers
106 views

Display date of each weekday of current week

I have seven spreadsheets, one for each day of the week. I am trying to get each one to display their date for that week. So for example, this week Monday would display 7/16/2018 and the Tuesday ...
1
vote
2answers
500 views

How to perform multiple operations where the IF statement is true?

The function below is what I use to handle contact information (email/phone numbers). If the entry is an email id (has text), it will keep it as is. When a phone number is entered, it does a variety ...
0
votes
0answers
109 views

Array Formula Auto-Calculation in Google Forms

I have a google form which is returning data into Column A and Column B and I need to perform calculations on that in Column C, D & E. I have been using Array Formula so that C & D auto-...
0
votes
1answer
134 views

Replace formula with its value after evaluation

In Google Sheets, I'd like to replace a formula with its evaluated value immediately after evaluation. Specifically, given two empty cells A1 and B1, I'd like B1 to be filled with the timestamp of ...
3
votes
1answer
2k views

Data Validation with an IF formula

I've got a list of items that I want to include in a Drop Down. Using data validation, all I need to do is add the range and this works. However, the list has various types of data and I want to ...
1
vote
1answer
312 views

IF function in Google Sheets gives Parse Error, what am I doing wrong?

When I type this into the cell it gives me the parse error. =if(B10>B2,"Win:" B10 (B10-B2) "SR",if(B10=B2,"Draw:" B10 "+0 SR","Lose:" B10 (B10-B2) "SR")) B10 is 1489 B2 is 1510 I want the cell ...
2
votes
1answer
24 views

Placing text from one cell between certain characters of another sentence

Is it possible to take a word from one cell and place it in between certain characters/words of another cell? (Obviously, I can do this one time but I need to do the same thing multiple times) For ...
0
votes
2answers
35 views

How do I find the sum of the last row of data?

I have a spreadsheet with a row of numbers added every day automatically. I want a function to find out the sum of the last row of numbers, as that last row contains the most up-to-date information. ...
1
vote
3answers
158 views

Copying data from one cell in another based on date

I have a spreadsheet, using google sheets, that I have formed into the following metrics: Column A is the dates of the month Column B is the data I want to be able to copy the data from Column B ...
0
votes
1answer
690 views

Why is my =VLOOKUP function returning #N/A for the first cell?

I'm trying to build a spreadsheet in Google Sheets to calculate a list of charges that should be oncharged to child companies (expenses are paid for by a holding company, and oncharged). My idea for ...
0
votes
1answer
402 views

Countif based on multiple criteria

For reference here's the sheet: https://docs.google.com/spreadsheets/d/1CS-fYhqeKRmEiJC5XA6kRIwIjyBDlBcXj-K9FHjCZjA/edit?usp=sharing I am trying to create a filter that shows me all the data for May ...
0
votes
0answers
10 views

Sumifs summing when conditions evaluate false?

I have a sheet with five columns: ID1, ID2, date, quantity, and the cumulative sum that match both ID1 and ID2. The last is a sumifs shown below And even though the ID1 (4) and ID2 (2) first occur ...
0
votes
1answer
279 views

Regex compare two strings

I have two strings "*j?a?c?k*c?o?o?p?e?r*a?v?o?n*l?a?k?e*o?h*" and "*j?a?c?k*c?o?o?p?e?r*t?r?a?n?s?p?o?r?t*a?v?o?n*l?a?k?e*t?e?r?m?i?n?a?l*" These seem like they should be equal in regex. However, ...
0
votes
1answer
30 views

Finding Min duration for a set of cells

Lets say for example I have 6 durations: mm:ss.00 4:03.2 4:08.2 4:03.1 4:06.4 4:01.3 4:03.3 And I want to find the minimum duration. How would I do that? It doesnt work if I say =min(A1:A6). I've ...
1
vote
1answer
135 views

Google Sheets VLOOKUP with wildcards on range

SETUP Say for a simplified example I have a table of raw data from my bank displaying each store I've spent money, and I want to categorize these into either high-street, website, or pub venues. The ...
0
votes
2answers
45 views

Automatically apply new formatting and remove time from a dynamically added Date-Time in google sheets

I am new to Google sheets and don't know how to write excel/google sheet formula to apply automatic formatting to a date+time value added through an external script. I have a CRM integrated with ...
0
votes
1answer
2k views

Adding multiple rows by script

I have this simple Google Sheets script that adds a row after every already existing one. What do I have to change to for example add 12 rows? function addRows(){ var sheet = SpreadsheetApp....
-1
votes
1answer
33 views

Join two cells data - COCATENATE not working properly

I'm using the following formula to get the data of two cells: =CONCATENATE(C2,"Dia",E2) But it's not working properly. Take a look at the screenshots: It should be returning the Traffic + Date, but ...
0
votes
0answers
203 views

DATEDIF of latest date of a range (Google Spreadsheet)

I'm trying to get the latest date for a range of cells and Calculates the number of days between two dates using the function DATEDIF. As you can see from my screenshot example, my calculation should ...
1
vote
1answer
80 views

Google Sheet formula on finding smallest value larger than 0 does not work inside conditional formatting

I wanted to find and highlight the smallest value larger than 0, but the formula I am using does not work in the conditional formatting. =MIN(FILTER(L4:L100, (L4:L100)>0)) Namely, if I use it ...
1
vote
1answer
97 views

How to get new Google Form responses to take on non-form functions in Google Sheets

I developed a Google Form that copies responses to a linked output spreadsheet in Google Sheets. In addition to the columns created by the form question/responses themselves, I've added additional ...
0
votes
0answers
12 views

How to query a tab that is specified by a cell

I have a function in place (Let's say in cell H8) that I want to change the destination of a query based on what another cell says. =IF(ISBLANK(D8),"",iferror(QUERY(GU_GA_Y!$A$8:$D,"SELECT sum(C) ...
0
votes
1answer
52 views

Adding a value at the end of this Filter formula Google Sheets

I have the below formula to remove blanks in a list and would like to add a separate cell value at the end of it (let's call it A1), not related to range V9:V22, but I would like A1 to show up below ...