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.

0
votes
1answer
6 views

Identify max value, divide others by half, then total

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
8 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 ...
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.)...
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
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
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:...
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 ...
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... ...
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, ...
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
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
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 ...
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 ...
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?
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
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 ...
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
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
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
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 ...
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 ...
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.
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 ...
-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
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
1answer
13 views

If checked, add row to data chart?

Basically, I have been working on some automated solutions to show the date and data for the most recent 4 weeks on this data sheet. There are still some bugs to work out, but the last thing I need to ...
1
vote
1answer
8 views

Restrict entry to a group of cells based on the value in another cell

I am creating a work schedule where the staff enters their own schedules. I only want a certain number of staff each shift. I already use COUNTIF to show the number of staff signed up for each shift. ...
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 ...
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 |...
1
vote
1answer
16 views

How can I create a unique list of data across multiple rows AND columns?

Google Sheets has a UNIQUE() function which when given a column of values, will output a column of all unique values. This works well for column-specific data, however, you have multiple columns and ...
1
vote
1answer
16 views

Problem combining LEFT() and QUERY() functions

I'm currently working in Google Sheets and I need to import a part of a row from a spreadsheet to another. I'm importing the row with the QUERY() function and "cutting" the data from the row with LEFT(...
0
votes
1answer
17 views

Calculations which won't recalculate

I'm trying to make a simple calculator in Google Sheets with checkboxes for certain values. For simplicity I'll refer to these Checkboxes as A and B. What I would like to have is the user selects A, B,...
0
votes
2answers
19 views

How do I tie an enum to a set of numeric values?

I created a list of items Data Validation in a sheet corresponding to time-frequency: weekly,monthly,annually I want to multiply values in a numeric column with the corresponding weekly values to ...
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
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-...
1
vote
1answer
9 views

Sum using two filter variables

So I have a ledger sheet that was set up to record costs for a single project. We've added a project column so we can track more than one project so every entry (row) now is identified per project. I'...
1
vote
1answer
15 views

AVERAGEIFS parse error

=AVERAGEIFS(Sheet12!R:R,Sheet12!A:A,N5,Sheet12!B:B,">=9/16/2018",Sheet12!B:B,"<=10/3/2018") While giving cell range instead of a date value, it's showing error.
0
votes
1answer
18 views

Autofill a cell when dates are entered in 3 other cells

I'm trying to create a way to keep track of our jobs by creating a Google spreadsheet. We can only order our final product when the digital and hard copy proof is approved and we have an order from ...
0
votes
1answer
18 views

How to make date automatically display only day number and suffix (1st, 2nd, etc.)?

Title explains it. I need to know how to create a custom number format in which a cell would display only the date and suffix of a full date entered into the cell example:1/05/2019 -> 5th I have ...
1
vote
1answer
15 views

Custom formatting with data returns what the cell shows but not what the cell contains

Basically I have this set up in one of my sheets in which these cells have data validation for "is valid date" so a calendar shows up when you double click them, then it has custom formatting so ...
1
vote
1answer
23 views

How do I use the multiply line results from query or filter to get other values from an unsorted sheet?

I'm trying to take the results of a filter with multiple lines and use that results to acquire info from another sheet. Specifically, I'm trying to acquire Quantities in Customer Order sheet using ...
0
votes
1answer
17 views

Create sequential dates with each date repeated multiple times

For every day of the year, I need the day repeated in 10 rows. How can I accomplish this efficiently? e.g. 01/01/2019 01/01/2019 01/01/2019 01/01/2019 01/01/2019 01/01/2019 01/01/2019 01/01/2019 01/...
1
vote
1answer
20 views

How to include multiple IF statements with different data validations

I'm currently stuck trying to script a formula to populate a total value cell the factors in two different data validation columns (below): The first column is to deduct a certain value per hour, the ...
1
vote
1answer
19 views

Combining 3x IMPORTRANGE with dynamic range

I have 3 Google Sheet and want to combine all of them with: Sheet 1: 480 rows Sheet 2: 40 rows Sheet 3: 20 rows The sheet keeps expanding by the day since the sheet's data comes from a Google Form ...