Stack Exchange Network

Stack Exchange network consists of 174 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
Make your voice heard. Take the 2019 Developer Survey now

Questions tagged [formulas]

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

1
vote
1answer
6 views

Identify max value, divide others by half, then total all

This may be too specific of a formula, but I'm hoping there is a function or script that will accomplish it. I need a Google Sheet to identify the largest number in a row and divide all of the OTHER ...
0
votes
2answers
9 views

Copying cell values to AVERAGE function

I’m pulling Free Cash Flow Data from Morningstar to Google Spreadsheet. I have formula: =IMPORTREGEX("http://financials.morningstar.com/ajax/exportKR2CSV.html?&callback=?&t="&B4,"Free ...
0
votes
1answer
6 views

Cumulative Sum of grouped values

I found it a bit hard to phrase this question, so here's a screenshot: I'm trying to create a timesheet for our employees and would like to have a cumulative sum function that sums up the number of ...
2
votes
2answers
20 views

Negate(NOT) used in IFS, but I get error

I try to use negate (NOT) in IFS, but it is not working, I get error. =IFS( NOT(TRUE); "aaa" )) How to fix it?
0
votes
1answer
9 views

Can you auto update the range for a filter in Google Sheets?

I have a Google Sheet document with many rows in it. I have several filters set up to view different collections of rows. The problem is, this sheet has new rows automatically added to it several ...
1
vote
3answers
32 views

Summing up amounts if one row contains a key within another sheet

I have two sheets in one Google Sheets document: | Key | Amount | |-----|---------| | ABC | 1 | | DEF | 2 | | GHI | 3 | | Keys | |-------| | ABC | | XYZ | | JKL | | GHI | ...
1
vote
1answer
12 views

Get URL from web page using IMPORTXML

Though I am new to web scraping, but I have a hand on experience of web scraping using VBA (Excel) I am facing the following problem: I have tried all methods (ImportXML, ImportHTMP, ImportData, etc.)...
1
vote
3answers
44 views

SUM columns from other sheets and group by month, but also show the individual sheets values before doing SUM

Currently, I have a spreadsheet which has a number of customer sheets, each with a pivot table showing the month in the row I and the total income for each month in row L. On a separate sheet I have ...
0
votes
2answers
12 views

Flag rows that are duplicates in two columns

My question is similar to this one, but adds a little more complexity. How can I flag duplicate entries in a Google Sheets column unless the duplicates have different values in another column? For ...
2
votes
1answer
7 views

IF Cell = True, then retrieve (separate) Cell Value

Cells: T2 = Price to Pay U2 = (Tick box) V2 = =if(U2=True, "Paid", "To Pay") W2 = empty What I want to do is be able to tick U2 and have W2 contain the value of T2. V2 works, so it does not ...
0
votes
2answers
22 views

ArrayFormula for consequential sum-up?

Here's a sample test spreadsheet - https://docs.google.com/spreadsheets/d/ What do I have to do in the ONE cell in column D such that I won't have to copy and paste the same formula in column C ...
0
votes
1answer
16 views

How to output end task time base on start time, duration and working time in Google Sheets

I'm using a spreadsheet to perform calculations and I'd like to make a task management file. For example: Working Time: 8:00 ~ 17:00 Lunch Break: 12:00 ~ 13:00 +- Start Time -+- Duration -+- End ...
0
votes
1answer
17 views

Enter Tab Name In Cell To Calculate Sum of Column or Range

I have seen one other similar question on this topic posted a few years ago, but I have been unable to have any success with the suggestions given there. I have sales sheets (a new one added each day)...
1
vote
1answer
17 views

MIN and SUM functions in Google Sheets for Time

I'm trying to use the MIN and SUM function but the answer always comes back with either 0, ERROR or !DIV/0. The format I want to have for my times are for example: 0.03, 10.56, 1:13.00. =MIN example:...
1
vote
1answer
16 views

IMAGE from Google Drive in Google Sheets [duplicate]

I have the following image on a cell in my Google Sheets: https://drive.google.com/open?id=1C9KL2vEhnZOVdCe2qUG5MZomw3jpvCiX When I use the formula =IMAGE(A1), the cell with the image stays white... ...
0
votes
1answer
26 views

Dynamically creating chart in Google Sheets

I'm looking for a way of automatically generating a graph based on a constantly expanding dataset. The number of columns is constantly expanding (a program create a new column with new datasets for ...
0
votes
2answers
1k views

Query reference to several sheets

I have this formula: =ArrayFormula({query(query({A3:D,value(B3:B)}, "select Col1, sum(Col5), count(Col4), sum(Col3) where Col1<>'' group by Col1 ...
1
vote
1answer
9 views

LOOKUP for data from other sheet

So I have a drop-down menu in order sheet and I have an info sheet. In the info sheet, I have 2 columns, column A has the customer name, column B has the customer e-mail. So what I'd like to do is, ...
0
votes
1answer
7 views

Defining Value to Numbers and Text and then Adding them

I am comparing apartments and I have columns for features like bedroom, full bath, half bath, rent, pet friendly, balcony, laundry, utilities, parking, and bus access. Some of these have numbers like ...
0
votes
2answers
19 views

Insert weight in kgs AND lbs in one cell?

Right now, a cell is showing the value: 10 I want it to show: 10 kg or 22 lbs And I want that to happen in the same cell. Also, that 10 is also a result of a calculation =B2-80 I ended up using ...
0
votes
1answer
9 views

Empty cell reference when “Loading…”

I want to do a calculation with imported data from an HTML link. The data is imported every minute. There is a loading time and this ensures that the cells are empty during loading. Because my formula ...
0
votes
1answer
10 views

Can I use conditional formatting to highlight a cell for dates less than 30 days away?

I have a large spreadsheet where I need to keep track of coming expiration dates and I would like dates which are within 30 days from today's date OR which have already passed to be highlighted in red....
1
vote
3answers
1k views

Formulas in Google Sheets won't execute

I can't seem to get formulas to execute in a Google spreadsheet. Whenever I input a formula, it does not return any output, but rather leaves the cell blank. I have tried the following troubleshooting:...
2
votes
1answer
28 views

Current value of compound interest

How can I calculate the current value of a sum deposited in a past date, with a fixed interest rate? For example, if $1,000 were deposited on 1/1/2000 in an account which pays a 3% annual interest ...
2
votes
1answer
153 views

Aggregate results by week and month for charts

Is there a way to convert daily values into weekly averages for charting? Sample source sheet --------------------------------------------------- | Data 1 | 1/1/2018 | 1/2/2018 | 1/3/2018 | etc......
0
votes
2answers
15 views

How to round float to bigger integer?

In Google Sheet, we can use ROUND() to round float to the nearest. like 5.2 = 5 5.5 = 6. What if I want it: 5.2 = 6 5.5 = 6. If the number exceeds by a decimal, then rounded off to +1. Any idea?
1
vote
1answer
148 views

Change text case so that first letter of the sentence is capitalized

Want to automatically change text case as I type on Google Sheets so that first letter of the sentence is capitalized.
1
vote
1answer
160 views

Range notation and current row

I wonder how does work the following and is it documented somewhere? I have the sheet: A B ----------------- 1 2 3 =$A:$A 4 5 =$A:$A For column B (row 3 and 5) I see ...
1
vote
2answers
24 views

Conditional Formatting - Custom Formula - Text Contains

I have dates in column B that include weekdays in the following format: DDD, MMM DD, YYY (For example, Sat, Jan 19, 2019). I want each row to auto format to a specific background colour based on the ...
1
vote
1answer
12 views

Convert inputted time duration to minutes:seconds [duplicate]

I'm working on this spreadsheet, and I wish to convert the data in columns C, D, and E from hours and minutes to minutes and seconds. For example, an inputted 7:08 turned into 07:08:00.000 (hours and ...
0
votes
1answer
29 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
13 views

How to join VLOOKUP results of multiple sheets into one cell

I am trying to use TEXTJOIN across multiple sheets, IF a cell is is the same I would like it to return the value from another cell in all sheets that are associated with it. In this example, I am ...
1
vote
2answers
449 views

Write cell value based on the URL of another sheet

Is there any way populate a cell in a Google sheet based on the value in the URL? For example: If the hyperlink below was on sheetA and the link was clicked. Is there any way to get ?cell=english ...
0
votes
3answers
119 views

Reverse QUERY in chronological order from two standalone spreadsheets

I have a report that pulls data from a separate sheet using a QUERY. The report has a "notes" column (not part of the query) so users can add notes associated with a particular row. This solution ...
1
vote
2answers
672 views

QUERY error for BY column

I'm using the following query to pull data from one sheet into another. =query(data!B2:CL,"select B, C, D, E, F, G, H, I, J, BB, BZ, CA, BA where J = 'Industry Certification'",false) The above query ...
0
votes
3answers
4k views

VLOOKUP using cell reference with IMPORTRANGE gives error

I can't see what I'm doing wrong with this. This Doesn't Work =Vlookup(L2,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1_Le0cMfbHRSgXqhCdzjQrBscFNm6p1sL-DowWdCZ0Fs/edit#gid=1761671943","...
0
votes
2answers
11 views

COUNTIF to display which results meet criteria

I'm working on a spreadsheet, where I'm using COUNTIF to check the number of times a sheet range meets a criterion. This works. I'd like to see which cells (their contents/locations) which met the ...
1
vote
2answers
14 views

How to check if the 1st letter in a cell is UPPERCASE

I'm trying to find out if the 1st letter in a cell is UPPERCASE. I know how to convert case using PROPER(), UPPER(), and LOWER(), but not how to detect case. The following checks every word in the ...
0
votes
2answers
17 views

View only few non-continuous columns

=FILTER(Matrice_data;Matrice_nom=G2) Pretty straight forward. I type a name in G2 and I get all rows with this name. My problem is this one: the "matrice_data" contains many columns, like 30. I want ...
15
votes
3answers
82k views

Google Sheets formula for “if contains”

I'm trying to figure out how to identify 'if' a list of items in one cell contains a value or string. EXAMPLE Cell A1 contains "sites, sheets, docs, slides". I want cell B1 to display a 1 'if' ...
-1
votes
1answer
17 views

How to return cell address? [duplicate]

Basically, I have this function that finds the last non-blank cell in a range while skipping columns I don't want it to search. The value is returned, but I want it to return the cell address (for ...
0
votes
1answer
31 views

How do I find the address of last non-blank cell in a row (while skipping columns)

Basically, I need to do what the title says on my spreadsheet here. So for row 3, I need to find the address of the last non-blank cell in that row, while skipping the grayed out columns (with the ...
1
vote
1answer
21 views

Can results of ARRAYFORMULA be used as values in an annotated timeline?

I am trying to create an annotated timeline with the following data, which exists on another sheet: I am using an ARRAYFORMULA to retrieve the text. I want to display these values as an annotated ...
0
votes
1answer
16 views

Divide number by WEEKNUM to get the rate per remaining weeks of this year

I am trying to divide a number by the total remaining number of weeks and have it calculate as the weeks' progress. I am using the formula =D58/today(WEEKNUM(1/5/2019,0)), but I keep getting an #N/A ...
0
votes
1answer
10 views

How to count cells in a row while skipping specific columns?

Basically, I need to count non-blank cells in a row and I need to skip certain columns/cells in that row. I'm sure this is simple combination of functions but I can't seem to find a solution to this.
1
vote
1answer
10 views

Formula for PROPER Case with Apostrophe

I'm using Google Sheets glued together (not be me) from multiple sources where some of the names are in all caps. In trying to clean web-ready content, I have good success with the PROPER() function ...
1
vote
2answers
22 views

Next Payment Date

I am wondering how to calculate the next payment date a client has. We know when their payment date starts, what the frequency of their payments is, and of course today's date. However, I cannot seem ...
0
votes
2answers
27 views

How to group data (by row and column) in a Google spreadsheet?

I have data as follows: Name | Value | Y/N | Value | Y/N A | 10 | Y | 12 | N B | 100 | Y | 120 | N A | 20 | Y | 10 | Y B | 200 | N | 120 | Y C | 1000 |...
0
votes
2answers
31 views

IMPORTXML error - empty table

I am trying to import a table from the lower part of the following website to Google Sheets: https://vistacapital.com.br/ I used the formula: =IMPORTXML("https://vistacapital.com.br";"//*[@id='sos-...
0
votes
1answer
17 views

How to apply scripting only to rows with data in them?

I have a Google Sheet that adds multiples rows per day as forms get entered. What's the best, most hands-off way to apply formulae to rows as they get added? I've built a script, but it applies the ...