# Questions tagged [formulas]

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

277 questions
17 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 ...
26 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 ...
19 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. ...
83 views

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 ...
25 views

### How to summary the multiple entry into a single sheet by name and by date?

Names are in rows and Dates are in columns. Sheet 1: Name 1-Jun-18 2-Jun-18 3-Jun-18....... KB CL KB EL AJ SL SD SL SD CL I need ...
411 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......
10 views

### Using SUM in Google Sheets with indirect ranges

I need to use SUM formula to fill the Total formula until next Total comes in Google Sheets like this: Total 6 A 1 B 2 C 3 Total 15 A 9 B 6
9 views

### Issue with finding the averages of multiple rows at once

I am currently tasked with hundreds of rows of data for a project. I need to take the average of each row, but there are way too many rows for me to do by each individual row. Is there any way to take ...
14 views

### Google Finance Historical Data with Multiple Attributes?

=GOOGLEFINANCE("NSE:"&A2, "all",(TODAY()-(\$N\$2)),TODAY(),"DAILY") The above code returns OHLC data for specified days with the date column. What I want are only high, low and close columns. =...
13 views

### How to get cell's parent ID?

Firstly thank you for helping me out. I'm in the construction industry and don't have any clue how to best do this - not my forte! I'm trying to bulk add a bunch of building product categories to my ...
11 views

### Adding mm:ss times when expressed as TEXT

I have a series of numbers that, for a reason out of my control I can not change, are expressed as plain text strings on google sheets. The numbers are supposed to be minute second. mm:ss. For the ...
10 views

### Concatenate - Show blank cell result if a cell is empty

Could someone help me? I am trying to create a social media keyphrase strategy using keywords. I would like to know if Google Sheets could return a blank cell if one of the cells in the concatenate ...
28 views

### How to get data to fill alternative cells when data is present in a zigzag pattern

https://docs.google.com/spreadsheets/d/1kwGbFd88cgifQRK_ZqsahQt1aDLzMTnmxHKs7mML5jA/edit?usp=sharing How to get data entered in one cell to fill another cell in a different location with same values ...
15 views

### Date highlighting of a cell, separated by weeks, given only a starting date and a cell range

I am trying to build a "life calendar" similar to this - https://labs.coruscantconsulting.co.uk/life/weeks/ - in Google Sheets. I have the manually designed sheet finished, but want to add the ...
25 views

### How can I track the time it takes for someone to fill out a Google form?

I am running a study for my thesis, and would very much like to track the time it takes to complete a Google form to compare against how long it takes to fill.
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 ...
20 views

### Disable Conditional Formatting without deleting the rules

I have multiple conditional formatting rules in one of my Google Sheets, I would like to disable a few of those rules for some time and would like to enable them again. But I don't see any such ...
14 views

### Autopopulate a RANGE of Cells from one sheet (tab) to another in a single workbook

All I want to do is have cells in one sheet (tab) in a workbook auto-populate with data and formatting of a different sheet (tab) in the same workbook. I know how to do this one cell at a time, BUT is ...
19 views

### How to have a table that updates across multiple sheets in both directions in Google Sheets?

I have two sheets, and a table that I want to be displayed on both sheets. I can use =('Sheet_name'!A1) to create a one-way dependency. However, is it possible to create a table that updates both ...
10 views

### Conditional Formatting Duplicate Invoices in Google Sheets

My workplace uses Google Sheets to track invoices across all locations. The two most important columns are next to each other and titled Invoice number and Vendor. I wanted to highlight the Invoice ...
43 views

### Date fields that refuse to be parsed/formatted

I have some very mysterious date fields in a huge (600 000 records) CSV file which absolutely refuses to behave. They come up aligned left, and conditional formatting shows that they cannot be seen ...
54 views

### Combine Custom Formula with Colour Scale in Conditional Formatting

In Conditional Formatting in Google Sheets, I'm wondering if it is possible to combine the Format Cells if - Custom Formula is with a Colour Scale? It would appear not, but is there a clever ...
28 views

### How to combine dynamic columns in Google Sheets while retaining data in other rows?

I have a Google Form that asks people to fill out their name, gender, and then their size preference for shirts and pants. The form automatically outputs the 2 sizes in separate columns. I want to ...
355 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"). ...
172 views

### How to concatenate cells using ARRAYFORMULA across rows and columns?

I have a document that's a recurring CSV import/export. I'm trying to concatenate images separated by ", " and using ARRAYFORMULA to make it all the way down. The problem is, I don't know how many ...
39 views

### Populating alternatives in question 2 based on answers in question 1

I have ten people who sell something to companies, and I want to track what they do IN GOOGLE FORMS. Every agent has his own 10 companies that he contacts. So, in Question 1, I will have a drop-down ...
46 views

### Google Sheets function returns an incorrect value

I have a very simple function for every cell in column D, for example: D24 =C24-(B24*N\$2) Example with values: D24 =10-(1*6,5) This works; however, not all the time. For cell D24 this returns a ...
17 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, ...
26 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 ...
34 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. ...
47 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?
20 views

### Is there a way to create a calculated field using results of another calculated field?

Is it possible to use the results of a calculated field as part of a new calculated field? I know further calculation could be done outside the pivot table but I was hoping there was a way to somehow ...
33 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 ...
77 views

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 ...
38 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 ...
72 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?
29 views

### How to fetch data in Google sheet using Import XML function with Xpath?

https://www.monito.com/send-money/australia/india/aud/inr/1000 Please open the URL and see what Query should I put to extract following data (=IMPORTXML("https://www.monito.com/send-money/australia/...
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/...
15 views

### copy/paste formula with reference to another sheet?

Within Google sheets, I have a formula like: =COUNTIFS(foo!H:H,"*"&B14&"*",foo!E:E,"*"&C14&"*") which I want to copy past as: =COUNTIFS(bar!H:H,"*"&B14&"*",bar!E:E,"*"&...
15 views

### 7 days high and low value of a stock formula

How to calculate the 7 days high and low value of a stock in one cell of Google sheet. Last 7 day high value in one cell and last 7 day low value in another cell
44 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....
19 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 ...
28 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> | | &...
19 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 ...
182 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 ...
78 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 ...
1k 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 ...
365 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 ...