Questions tagged [formulas]

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

2
votes
1answer
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 ...
2
votes
1answer
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 ...
2
votes
1answer
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. ...
2
votes
1answer
83 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
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 ...
2
votes
1answer
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......
1
vote
1answer
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
1
vote
1answer
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 ...
1
vote
1answer
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. =...
1
vote
1answer
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 ...
1
vote
1answer
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 ...
1
vote
1answer
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 ...
1
vote
1answer
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 ...
1
vote
1answer
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 ...
1
vote
1answer
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.
1
vote
1answer
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 ...
1
vote
1answer
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 ...
1
vote
1answer
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 ...
1
vote
1answer
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 ...
1
vote
1answer
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 ...
1
vote
1answer
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 ...
1
vote
1answer
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 ...
1
vote
1answer
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 ...
1
vote
1answer
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"). ...
1
vote
1answer
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 ...
1
vote
1answer
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 ...
1
vote
1answer
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 ...
1
vote
1answer
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, ...
1
vote
1answer
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 ...
1
vote
1answer
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. ...
2
votes
0answers
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?
1
vote
0answers
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 ...
1
vote
0answers
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 ...
1
vote
0answers
77 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
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 ...
1
vote
0answers
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?
0
votes
0answers
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/...
0
votes
0answers
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/...
0
votes
0answers
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,"*"&...
0
votes
0answers
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
0
votes
0answers
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....
0
votes
0answers
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 ...
0
votes
0answers
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> | | &...
0
votes
0answers
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 ...
0
votes
0answers
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 ...
0
votes
0answers
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 ...
0
votes
0answers
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 ...
0
votes
0answers
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 ...
0
votes
0answers
366 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, ...
-1
votes
0answers
27 views

Split total volume of each customer through the weeks, respecting weekly volume

I need to create a tool that splits the total volume of each customer through the weeks, respecting each customer's weekly volume, like below's picture: Columns A to F are the input, "Division by ...