Stack Exchange Network

Stack Exchange network consists of 175 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

Questions tagged [formulas]

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

1
vote
1answer
19 views

How to import values from the 2nd sheet based on the colum of the 1st sheet?

I have two sheets - Sheet1 includes: col 1 | col 2 [Name] | [phones ] Sheet2 includes: col 1 | col 2 [Name] | [e-mail] I want to importrange if the name on Sheet1 is equal to name on Sheet2.
1
vote
1answer
22 views

Google Sheets returns wrong results

Google Sheets gives me the wrong result. In the first row (M38) I write the number manually; in the second row (M39) I have a SUM for my data. Both results are the same, as you see. However, when I ...
0
votes
0answers
13 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
1
vote
1answer
27 views

Generate custom sequence in Google Sheets

I would like to create a column with a custom sequence: A-Z,a-z,0-9. (Essentially the Base64 character set with the last two characters, + and /, left out) So, A1 would be AAA, A2 would be AAB ... ...
0
votes
1answer
27 views

Search for text in multiple columns in multiple worksheets

I have 3 columns (Col1, Col2, Col3). All 3 columns are in their own worksheet (WS1, WS2, WS3). I want to search each cell in Col1 see if that cell matches anything within Col2 in WS2 then output "...
0
votes
1answer
23 views

Inaccurate output when using importrange vs manual input

I have a little problem using Google Sheets. I'm using importrange to get raw data from another sheet that I don't own. This is the sample raw data, then import it my own sheet here. Importing data ...
1
vote
1answer
16 views

Count worked days per employee

I am creating a spreadsheet in Google Sheets for a client who wants the sheet to pull data from their Google Calendar to calculate how many days each employee works. They use Calendar for their ...
0
votes
1answer
10 views

Unable to SUM column calculated by Array Formula

I've used Array Formula to calculate the total price for an order and I have a manual column next to it to note what has been paid. When I come to do a comparison of the two though, Google sheets ...
0
votes
1answer
12 views

All text in a cell before the “ - ” or “ : ”

Is the Query function capable of taking the contents of a cell and returning everything before the first instance of - or :? Here is a screenshot of what I mean: Obviously, this isn't my actual data,...
1
vote
1answer
15 views

Can conditional formatting reference other sheet?

I needed to check which indexes exist on another sheet. So for the A:A range, I made conditional formatting with the following formula: =COUNTIF('Other Sheet'!A:A;A1) That had a red error outline. ...
0
votes
1answer
17 views

Cell with multiple color formatting results only to first color being applied

If a Google Sheet cell has 2 Conditional formattings, one which changes only font color, one which changes only the background, only the first conditional formatting affects the cell. The reason ...
1
vote
3answers
53 views

Duration to seconds calculation

There are 3 columns in my google sheet with heading Start time (col C), End Time (col D) and time taken (col E). Column E is arrived by finding the difference between col D and col C. Now when I try ...
1
vote
1answer
11 views

check if cell is empty OR has a value, AND cell in row isn't empty then assess as true or false

I'm trying to format a column so that if the column A cell is NOT empty, AND the column H cell is either empty OR "no" =AND(NOT(ISBLANK($A$1:$A)), (OR(ISBLANK($H$1:$H)), (LOOKUP("no", $H$1:$H))))
0
votes
1answer
5 views

Flagging empty cells if date in another cell has passed

Good afternoon! I've created a test sheet to help visualize this - https://docs.google.com/spreadsheets/d/1-qHH4GfeIZz-7OBHFpp4trrFNg7l-gBHDT2MHg9tFmE/edit?usp=sharing I'm aiming to have cells ...
1
vote
1answer
24 views

Filtering Pivot table by multiple values

I have a pivot table below that I am trying to filter dynamically based on a separate list and am trying to figure out how to do this by referencing the relevant field. In the below example I have ...
0
votes
1answer
16 views

Is there a way to display named ranges with a space?

Sample spreadsheet here. In the spreadsheet, there is a dependent drop-down list in the first column, which automatically populates a data validation for the second column using the named range from ...
0
votes
1answer
36 views

Convert phone number format from (404)-313-2974 to 404-313-2974

I have a list of phone numbers entered into Google spreadsheet in this format: (404) 313-2974 How can I convert this entered cell into 404-313-2974?
0
votes
1answer
9 views

Return a list of unique values based on part of the string - Google Sheets

I have a list of values as follows: Maestro purchase 28.02.2019 18:54 Coop-4853 ZH Bombach Card number: 79090775 Payment order Maestro purchase 27.02.2019 08:29 VBZ ...
0
votes
2answers
14 views

How to extract hovered links from a cell in Google Sheets?

I have set up this demo spreadsheet. Can someone please help me extract the URL?
0
votes
1answer
16 views

If cell contains a text string assign a value to another cell

This is my Google Sheet. I have a number of "shopping" cells formatted to have a dropdown list of products/words using the (right-click) "data validation" function. I have another cell I want to ...
1
vote
1answer
15 views

Using Google Sheets to dynamically add entries to column based on logic?

I have a list of people that I have a drop down for if they are attending or not to an event. If "Yes" I want them to be added to another column on sheet 2. Except I don't know how to add them ...
1
vote
1answer
22 views

Is there a way to count unique numbers and count unique text+numbers

For example: Column A 1234 4567 2345 4356 a123 b345 c567 What formula to use so that when I count row 1-7, I get 4 counts of numbers and 3 counts of text+numbers?
1
vote
1answer
10 views

VLOOKUP needs to find value from a list and if its equivalent then

I have a problem where I'm currently using a VLOOKUP function on this sheet. LINK What I wanted to happen is VLOOKUP would search on the List on G column and compare it to B column and if it matches. ...
1
vote
1answer
14 views

Adapting Conditional Formatting Formula for Cross Sheet Reference

I currently have a sheet setup for work that compares 3 columns of data with conditional formatting. The goal is to identify whether the same string of characters can be found 1 2 or 3 times across ...
1
vote
1answer
16 views

How to combine two QUERY results into one column

I have the following QUERY =query(XX!A2:A;" select A where A>'50' ") and =query(YY!A2:A;" select A where A>'50' ") how do I combine these two data sources so I don't have to have two ...
1
vote
1answer
19 views

Conditional Formatting Text with more then one value

I am looking to have a row of 20 cells change font colour based on ABC to Z being white. Then 0 to 10 being blue. Then all the symbols being red. Is there a way to do this? I found I can do this but ...
0
votes
2answers
22 views

Extract Information from Webpage onto Google Sheets

I've been trying to grab some data from this webpage: https://seekingalpha.com/symbol/BZUN. I want to import the follower count (under the add to portfolio button) into a cell on google sheets. For ...
0
votes
2answers
12 views

Google Sheets Formula for Attendance Records

I have a very overwhelming and large attendance sheet of 350+ people that I need to keep track of. I have a very simple attendance record of 0 if you weren't there and 1 if you were. For one of our ...
0
votes
2answers
25 views

How to make a formula so it repeats istself indefinetly

I have a formula that does exactly what I need except for that, for now, I need to manually expand the range for which it applies. What do I need to add so I only have this in one cell and it ...
1
vote
1answer
16 views

Extract Value from Poorly formed JSON result

I have a Google script that returns the following result set, but when I try to get the value for asking_price or asking_price_condition it comes back undefined. I assume it's because of some {} ...
1
vote
2answers
25 views

Combining numbers based on a seperate cell

I'm trying to make a sheet that records every 'player's' balance. Transactions come from a form, and I was wondering if I could run a script that Reviews each incoming data set Adds an amount [what ...
1
vote
1answer
18 views

IMPORTXML not updating for unknown reason

Doing a web scrapping exercise I found that after I was able to view the table in the aim sheet with IMPORTXML the formula stopped pulling/scrapping and an #N/A tag show up in its place.
0
votes
1answer
16 views

Google Sheets uniquecount function

Is it expected or is there a reason that Google Sheets uniquecount returns one more than the actual unique count for a column?
-2
votes
3answers
30 views

Parsing a non-nested JSON string in Google Sheets using formulas only

I have a cell with the following JSON data: {"rev2_0": "2,058", "rev2_1": "2,099", "rev2_2": "2,141", "rev2_3": "2,184", "revchg3_0": "3%", "revchg3_1": "6%"} In another cell, I want to get the ...
0
votes
2answers
12 views

Query function problem

When using a query function that selects two columns from another sheet, I get two results in one cell. It looks like it doesn't respect the initial structure of the selected data. Any idea, please?
2
votes
1answer
29 views

Sum value in cells that contain both number and text

I need to sum a range of cells with text in them. Example: a1= "25,00 market" b1="24,00 credit card" c1="75,11 food" a2= "10,00 sweets" b2="9,00 market" c2="24,00 keyboard" I want to sum ...
-1
votes
5answers
48 views

Combining ARRAYFORMULA with functions whose arguments can be rectangular ranges

I mean to concatenate several columns, so use of CONCATENATE is preferred over & (here I post an example with only 3 columns). For instance, D1 would contain =CONCATENATE(A1:C1). Then I would copy ...
1
vote
1answer
33 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
14 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
35 views

Conditional IMPORTRANGE

In Google Sheets, I'm using IMPORTRANGE to pull in data from to a large sheet. One field is a dollar amount. I would like to import the dollar amount and replace any zero amount with a specific ...
2
votes
2answers
48 views

Processing data from multiple cells and comparing results with another cell

For most orders, I have multiple deliveries under the same recipient ID and I need to compare whether everything ordered is really delivered. All products are divided by a comma and have always the ...
1
vote
1answer
23 views

Formula for combining text if there are duplicate numbers

I have a table to combine text with conditions if in column A there is a duplicate number, then in the text in column B combined. The formula that I have made like this: =IF(COUNTIF($A:$A,A2)>1,...
1
vote
1answer
10 views

How to SUMIF depending on another column's value and REGEXEXTRACT

I'm trying to use SUMIF and REGEXEXTRACT, i want to get a total sum of the description only where paid out is not blank and the description value needs to be multiplied by value in paid out so on the ...
1
vote
3answers
58 views

How to return what is missing from a 2 column list comparing against all possible combinations of 2 single column lists?

Sample data: A B C D E F 1 name handed in students assignments name missing 2 a 1 a 1 b 2 3 b 1 b 2 ...
1
vote
1answer
12 views

Nested IF with FIND

I have a list of transactions in column A that I want to categorize. I want to search A1 for, say, "Shoprite" and if found return "Groceries", otherwise search for "Shell" and return "Gas", otherwise ...
0
votes
2answers
36 views

How to get price of specific cryptocurrencies at specific times?

I have an assortment of cryptocurrency transactions over time. Various currency pairs at various prices. I need to figure out what the price of those currencies was, in Bitcoin, at the time that I ...
1
vote
1answer
31 views

How to return all values (horizontal array) in B that has the same key stored in A?

Sample Sheet 1: A B 1 a x 2 a y 3 a z 4 b i 5 b h 6 b w Formula result, in Sheet 2, I am looking for: A B C D 1 a x y z 2 b i h w
0
votes
1answer
60 views

Getting data from another Google sheet that isn't in the same order

I have a very basic knowledge of deeper functions of Google sheets and solving this has been a little bit of pain in the butt so far. I have a spreadsheet A column A contains ID column I that has ...
2
votes
2answers
42 views

Query returning #VALUE error when the column has no values

The below query returns #VALUE error when the column H is completely empty: =QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, ' ', ' ', ' ', G, ' ', ' ', ' ', '"&...
2
votes
1answer
24 views

Return first vertical value that matches given criterion

On Google Sheets, I would like to figure out a way to have a cell return the first value in column C where the same row in column D matches the criterion ("Blue" for example, as opposed to "Red"). I ...