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.

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 ...
2
votes
1answer
18 views

What's wrong with this arrayformula?

=arrayformula(if(LEN(A:A),if(ROW(A:A)=1,"Outstanding amount Payment", sum(QUERY(daily_fee_collection!A:D,"select D where B="&A:A&"",-1))))) I am trying to cross-check for payments made in ...
2
votes
1answer
15 views

Using multiple IF and OR statements with calculations

I'm trying to calculate prices for items that have various costs. Depending on what that cost is determined its multiplier. I'm hoping I can create a formula in a spreadsheet to streamline my process. ...
2
votes
1answer
38 views

Add values between two dates in Google Sheets

So I've already found this question answered several times around here but for some reason, despite essentially copy and pasting the answers it still doesn't work for me. I'm trying to add ...
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......
1
vote
1answer
10 views

Formula for filtering all duplicate data pairs in Google Sheets

I'm making a table to check duplicate data. Where data that wants to be duplicated is in the "Sampel1" column and the duplicate checking results are in the "Status" column. The formula that I have ...
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
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
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
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
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. ...
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
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
13 views

How to insert cell text from one cell into an ImportRange Function

Hello! so I have an issue where I am trying to do is have multiple functions in C, D, and E where a link is required. Instead of copying and pasting the link inside all the functions in specific ...
1
vote
1answer
25 views

How to combine FILTER with TRANSPOSE in data validation custom formula

Cells A2, C2, and E2 contain the data I want to make my custom list for data validation. Cells B2 and D2 have data I don't want in the list (just text - "vs" - in them). So, I'm trying to use the ...
1
vote
1answer
17 views

If cell contains then

I need to create a formula that will check for a word in a cell and return a value or if the word does not exist then check for another word. Eg. Check Cell A1 contains the word "red" then print "...
1
vote
1answer
18 views

Is there a way to pull or search for data from other sheets?

We use Google Sheets for tech commission sheets (track jobs and hours). Right now we have 2 years (2017&2018) in 1 set of sheets, 1 sheet for each month (jan 17, feb 17, etc). So instead of adding ...
1
vote
1answer
18 views

Query Importrange with multiple values

This formula is working fine, but I need to add in other teams into Column A. I would need to return ATT SLC, ATT Orem, and ATT Scottsdale within this same range, but I'm not sure the language to use ...
1
vote
1answer
11 views

Import Range of cells as per today date in another sheet

There are three columns A, B, C AND today date is in the first row of the column A means A1. I need to import these three columns date excluding date row. A2:A100, B2:B100, C2:C100 in another sheet. ...
1
vote
1answer
145 views

Conditional Formatting across a range of cells with a formula in Google Sheets

I have a calendar put into Google Sheets with the data put in as dates such as "9/24/2018". I have a separate sheet with a data set that has rows that classes end such as "9/24/2018". I want to ...
1
vote
1answer
84 views

Compound IF Statement in Google Sheet

I'm trying to link 2 Google Sheets with each other & need to create an IF statement. In Sheet 1 I have checkboxes: Yes, No, Maybe. In Sheet 2 I'd like a statement that says basically: If A2=true,...
1
vote
1answer
440 views

Dynamic updating referenced cell from another workbook after adding/deleting rows using IMPORTRANGE - use ARRAY or VLOOKUP?

I add/deleted/move rows frequently in my workbooks. When I add/remove rows from Spreadsheet#1 ("S1"), I need the referenced cell in Spreadsheet#2 ("S2") to dynamically update so it still references ...
1
vote
1answer
403 views

Conditional format custom formula in Google Sheets

Have a question on how to do a custom formula in conditional format for the following: A1 = DCC220 B1 = 7.50 C1 = 7.00 D1 = 7.60 I want to compare the result in B1 (7.50) to a range, C1 lower, D1 ...
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
161 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 ...
0
votes
1answer
5 views

Perform operation on specific column in filtered range, right after filtering

I have this formula in E2: =FILTER(A1:C20,B1:B20=2) and need to perform substitute like: spreadsheet here I'm hoping to do the formula in a single cell, without additional columns.
0
votes
1answer
7 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 ...
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
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
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 ...
2
votes
0answers
43 views

If I ask a yes/no question, am I able to give it a value and add to calculations?

Question on my form: Is your wedding on a holiday? If they answer yes, I'd like to add a $100 value to that answer and then add it to the total. Is this possible? If so, how?
1
vote
0answers
24 views

How to create a user defined function that maps one value to another in Google Sheets

I would like to create a mapping function that takes as an input 3 arrays of the same length and a double between 0 and 1. Array #1: Each element is the minimum value of the nth bin. Array #2: Each ...
1
vote
0answers
37 views

Moving linear regression on google spreadsheet

I have a table with four columns: A: date B: values C: projection D: short-term projection The first two are hardcoded values. We admit that dates go up, while values mostly go down. I used a ...
1
vote
0answers
37 views

Dynamic dropdowns - for sales contact management exercise

I am trying to figure out a method in Google spreadsheets where I would be able to display information of contacts after I have selected 2 more criterias before. So imagine it this way: You see a ...
1
vote
0answers
66 views

Is there a way to do collaborative equation editing in a Google Presentation?

Since Google Docs now forbids equation in presentations (you cannot drag equation from doc anymore according to this question). Is there a way to include equations in a Google Presentation?
0
votes
0answers
14 views

How do I automatically import one CSV cell to a Google Sheet?

I am a bit of a novice as this will probably be the most complicated thing I have set up so please assume I know nothing. I have an app (App Usage https://play.google.com/store/apps/details?id=com....
0
votes
0answers
12 views

How to highlight a cell in column A if the text in column B matches text in a list on another tab?

I have a Google Sheet set up with place names in column B as below. I have another tab on the same spreadsheet with a list that can change from time to time depending on which stores needs items ...
0
votes
0answers
24 views

Convert column with multiple subheadings to sortable table in Google Sheets

How can I convert a column with variable data like this: | <h2>TITLE 1</h2> | | <input ...>Item A</input> | | <input ...>Item B</input> | | &...
0
votes
0answers
17 views

How to carry over data from one G-sheet to another

Obviously not a programmer and fairly unfamiliar with G-Sheets. I have several sheets that represent the steps of process maps. When a certain process goes over it's timeline, the "Status" column ...
0
votes
0answers
135 views

rank cell value by column values in google sheets formula

So I have a table of apartments I'm looking at, and bunch of parameters I consider for each apartment, such as Price, size,distance etc.. Heres an example: So the conditional formatting helps me ...
0
votes
0answers
136 views

Formula that returns the column reference of the last non-zero value cell in Sheets

I have a list of values by month for multiple users in a Google Sheet (trailing 12 months). In another column, I need to provide the column reference for the last non-zero value in that row. So, for ...
0
votes
0answers
12 views

Can you tell me what I'm doing wrong with this?

=SUMIFS(A3:A50,"=AIS","=LifeScan" B3:B50) I'm not sure what is wrong, but I keep getting the error "Formula Phrase Error" and I need the Sum of Column B if Column A equals x or y.
0
votes
0answers
26 views

Google sheets: convert ragged array for to 2D array

I have multiple columns in a single row in a ragged array and want to convert this to a 2d array. This example can be found here I realize that similar questions have been asked for multiple entries ...
0
votes
0answers
42 views

Formula to calculate count of possible orderings (permutations) of a set of values

I'm working on a Google Sheets spreadsheet, and I'm having trouble figuring out a way to count all possible ways a set of (concrete) values could be sorted, taking into account possible duplicate ...
0
votes
0answers
669 views

SUMIF giving #N/A error

I am trying to add up last the 7 entries in the E column from the sheet with my Google Form results if the corresponding value in the D column is "Blender", but I keep getting the "Argument must be a ...
0
votes
0answers
16 views

How do I change the cell color in the Google Sheet formula cell?

=IF((ISBLANK(H2)=TRUE)+(ISBLANK(I2)=TRUE)+(ISBLANK(J2)=TRUE)+(ISBLANK(K2)=TRUE),"",(B2+1+L2)) I created a pitch count log. The user enters the information and the cell gives out a date. I need ...
0
votes
0answers
331 views

Google Sheets split multi-cell into new rows

My question is about merging multi-line cells into separate rows from a Google Forms responses doc. It looks like this question has been asked & answered, however, I have spent several hours over ...
0
votes
0answers
36 views

Two Timestamps in diffrent cells to update seperately when data is put into a cell

I am trying to get two separate timestamps to fix to two different cells when data is put into a separate column. E.g If i enter a number in B3 i get a timestamp in D3 I also enter a number in C3 and ...
0
votes
0answers
306 views

Change formula based on value of cell

I'm calculating the a Loan payment with PMT function and was wondering how I can change the percentage based on the outstanding loan. Say if the outstanding loan drops below 80% of the total loan ...
0
votes
0answers
313 views

COUNTA function counting one extra filled in line

I am creating a register for some workshops to introduce people's personal information, organized by when these workshops are taking place. I have six different spreadsheets (Monday, Tuesday, ...