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
2answers
283 views

Google Sheets: Count how many unique values appear more than X number of times

Given a column of names I would like a formula to return a count of unique names that appear >=X number of times. The function COUNTUNIQUE works only if X=1.
0
votes
2answers
430 views

Increment the reference to a cell formula by 1 in Google Sheets

So, I'm wanting to use the following line of code: =image(CONCATENATE("https://chart.googleapis.com/chart?chs=150x150&cht=qr&chl="&A2&+B2&+C2&+D2&+E2&+F2&+G2)) ...
-1
votes
1answer
197 views

Dynamic VLOOKUP + COLUMN + IMPORTRANGE

I need to keep updated a COLUMN number into VLOOKUP formula with IMPORTRANGE. =vlookup(B5;IMPORTRANGE(url;"RIASSUNTIVO!B:CC"); column(IMPORTRANGE(url;"RIASSUNTIVO!C1:C10"));FALSE) The COLUMN ...
1
vote
1answer
787 views

HAVING clause in Google Sheets QUERY

My Problem I have built a count query that returns the most common elements from column H in the final sheet: =QUERY(final!H2:H,"SELECT H, COUNT(H) GROUP BY H ORDER BY COUNT(H) DESC") I would like ...
0
votes
1answer
41 views

Search a range for a value, return the value of the first cell in the matched column

I have a table, such as: A B C 1 Animals Colors States 2 Cat Red CT 3 Dog Orange CA 4 Mouse Yellow CO I want to search for ...
1
vote
3answers
77 views

How to get average from last 3 values?

So I am trying to calculate an average for a golf score. I want the average to be of the persons 3 previous scores. So I am looking for a formula that will average the last 3 numbers in the column. I ...
1
vote
1answer
148 views

Google sheets array comma-delimiter not working in functions

I was trying to do a VLOOKUP in a column and print results of a column left to the on to be searched. So I tried put them into an Array to move the search column to the front. But for some reason the ...
2
votes
1answer
53 views

Truncate Text returned from indexhtml

I'm new to making spreadsheets. I'm making one for a game I play. I'm trying to use an api call to return json data from the games exchange website, to update prices in near real time. However I don't ...
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 ...
1
vote
2answers
965 views

Google Sheets, formula, reference to variable sheets

Google says in their web help how to reference data from another sheet, but what happens if what I need is to make the reference depending on my fields on the current sheet? Let's say I have a sheet ...
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 ...
2
votes
3answers
103 views

Copying data from one cell in another based on date

I have a spreadsheet, using google sheets, that I have formed into the follow metrics: Column A is the dates of the month Column B is the data I want to be able to copy the data from Column B into ...
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
1answer
209 views

Google Sheets - Fetch random value from array for each new line created with form

I have a Google Form that fills up a new line with values in Sheet1!, column A and column B. I am trying to create an =ARRAYFORMULA function in column C, which will populate the C cell in each new ...
0
votes
1answer
266 views

Using UNIQUE and SUM for all possible values in Google Sheets

I have a much bigger and more serious table just like this one: As you can see there are more possible category than I use. What I want to achieve is listing all the categories (even ones that does ...
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 ...
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.
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
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 ...
0
votes
1answer
310 views

Google Sheets formula to calculate gross monthly income based on net annual income

I'm trying to figure out how to get the monthly gross income based on the annual net income. The net income is computed by multiplying the monthly gross by 12 and then subtracting the annual tax. The ...
0
votes
2answers
195 views

Conditionally formatting duplicate values in Google sheets with exclusion criteria?

I'm currently working in a Google sheet where I use the following formula to catch duplicate values with conditional formatting: =countif($B:$B,B2)>1 While this works on its own, I find that it ...
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 ...
0
votes
1answer
35 views

Conditional format a cell with a graduated colour when a range of other cells are completed with text

I'm trying to conditionally format a cell with a graduated colour (traffic light: green/ amber/ red), when a range of other cells are completed with text within google sheets. In other words, 4 cells ...
3
votes
2answers
2k views

Ignore formula if cell is blank

I'm attempting to create a formula that essentially counts the days between today's date and the date entered in a cell. So far I believe it works. See below: =DATEDIF(H3, Today(), "D") My dates ...
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
1answer
28 views

Better management of long conditional formula in Google Sheets

I have a long and unwieldy formatting condition, currently it's doing 9 function calls, and I'd like to make it even larger. My trouble is that it's getting increasingly difficult to manage all the ...
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
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 ...
0
votes
1answer
1k views

Convert Cell From Text To Formula

This request may look silly, but I've distilled a much larger task into a very simple example. I have cell A1 with _,=$D$12 and then another cell that references that with =SPLIT(A1,",") and it ...
1
vote
1answer
78 views

Consecutive numbering skiping empty cells with ArrayFormula

I am trying to auto-number only the existing names on a cell using an arrayformula: I have two cells one for the names and the other for consecutive numbers. Here I have an example of what I want to ...
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 ...
1
vote
1answer
395 views

Google Sheets: REGEXEXTRACT Formula to remove characters not inside [brackets] but leave , commas?

I'm trying to create a formula that uses either =REGEXEXTRACT or =REGEXREPLACE to remove all characters not inside [brackets] but leaving the ,,, commas where they are. For example... ➜ Text to ...
-1
votes
1answer
52 views

Importxml convert content only

I am getting the price value from the website. I would like to make the value as static(remove formulas) to prevent auto refresh in sheet due to that the price values will change-- I don't want it. ...
-1
votes
1answer
33 views

Join two cells data - COCATENATE not working properly

I'm using the following formula to get the data of two cells: =CONCATENATE(C2,"Dia",E2) But it's not working properly. Take a look at the screenshots: It should be returning the Traffic + Date, but ...
2
votes
1answer
892 views

Format telephone numbers, keeping only digits

I have a spreadsheet that has phone numbers in the cells that are typed out like this: +1(313)313-3333 and I need these numbers to all be plain and look like this 3133133333 (with no 1 in front). ...
0
votes
1answer
29 views

spreadsheet conditional: if in top x then

spreadsheet data: Total Average Highest Top Sales $23,550.00 $7,850.00 $8,100.00 not $42,700.00 $14,233.33 $14,400.00 not $30,500.00 $10,166.67 $10,300.00 not $37,840.00 $12,613....
3
votes
2answers
292 views

CountIf in upper cells with ArrayFormula

I have some values in the column A and in the column B want to calculate concatenation of the value and how many times it was used in rows above. In the usual formula, I would've written in B2 =A2 &...
0
votes
1answer
44 views

Forms Data Manipulation Revise in Google Sheets

So I came across this post that is doing exactly what I want. We use a google form to collect new learners to add to a store. What I'm trying to do is have the first 9 columns repeat for each learner, ...
3
votes
2answers
2k views

How to write conditional formula for multiple conditions

Trying to figure out how to write a formula in Google Sheets that will automatically calculate a deadline for me based on priority and an established date. I have 3 columns: Date (A), Priority (B), ...
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 ...
-1
votes
1answer
76 views

Apply the conditional formatting formulas across every column of the sheet

I've got a Google Sheet that I'm trying to write some formulas for - it's basically just a bunch of numbers in columns that I'm trying to do some conditional formatting on (per column). I want to be ...
-1
votes
2answers
739 views

I got this “Error: Imported content is empty”

I am trying to create a google sheet to combine some information. I want the sheet to pull information h index of my friend this is the code =IMPORTXML("https://www.scopus.com/authid/detail.uri?...
0
votes
1answer
99 views

Can't get number of month for a specific month in the Portuguese version of Google Sheets

I am obtaining the number of months for the Portuguese names of the months using Google Sheets. For most months, the formula below works: =MONTH("01/"&A1&"/01") Except for Setembro (in ...
3
votes
1answer
43 views

2-dimensional comparison for conditional formatting in Google Spreadsheets

So I have this spreadsheed with two areas (D7:AD16 and D18:AD27). Now I want to format the colors in D18:AD27 such that yellow color is added to every cell that is empty in D18:AD27 but not in D7:AD16....
2
votes
1answer
193 views

Array formula with running total that restarts based on values in another column

I have tried to create a running total with some kind of filter as it were and have been unable to figure out how to do it. I have seen people do a running total using MMULT but not sure how to adapt ...
1
vote
1answer
30 views

Getting total number of identical items from multiple columns

My goal is to find out the total number of each type of shift for the year. I have each type of shift calculated out per month in their respective month columns: I got the list of unique shifts for ...
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?
3
votes
1answer
1k views

Sum all numbers converting all negative numbers into positive numbers

I have a column of numbers and would like to know the total after everybody has paid me back, but I have negative numbers currently in there: So the total of this should be 95, currently if I do a ...