Questions tagged [formulas]

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

Filter by
Sorted by
Tagged with
0
votes
0answers
13 views

Google Sheets - How can i collect and add values related to a value next to it in a table and add them to another table

I'm using app sheets to see what I do in a day. I select the task i am starting on the app and app sheets dumps the data into a table in google sheets. I have made an example of what this looks like. ...
0
votes
0answers
11 views

Google sheets - how can I use functions to resort my table in a way charts can read it well

I'm using app sheets to see what I do in a day. I select the task I am starting on the app and app sheets dumps the data into a table in google sheets. I have made an example of what this looks like. ...
0
votes
2answers
19 views

Return a list if 2 criteria match in Google Sheets

In an effort to organize staff training: So far I've made the pivot table, but I'd like for the pivot table to extract who needs which training, as a list in one cell. I tried using =JOIN, but I have ...
0
votes
1answer
33 views

Google Sheet Filter and Match (List the label where a name exist.)

I have 4 game columns that contain the names of the players. I want to list the games of a particular player. A. B. C. D Basketball Bowling Billiards Badminton --...
0
votes
1answer
14 views

Google script (custom function) doesn't get updated inside a cell

I have this function : function clastrow(targetsheet) { var SS= SpreadsheetApp.getActiveSpreadsheet(); var Sheet = SS.getSheetByName(targetsheet); return(Sheet.getLastRow()); } function ...
-1
votes
0answers
13 views

Google Sheet formula explanation for stock market

=min(index(query(sort(GOOGLEFINANCE("NSE:SBIN","low",today()-100,100,"WEEKLY"),1,False),"select * limit 10",1),,2)) This is a formula for finding low of last ...
2
votes
2answers
140 views

Formula to count the number of different letters in a word

In sheets, suppose cell A1 contains the word CABBAGE. I want to put a formula into, say, cell B1 that will count the number of different letters. So the output should be 5, because there are 5 ...
0
votes
1answer
24 views

How do I copy cells so that only the sheets where they reference from is changed?

I have a expenses sheet, and other sheets named Jan, Feb, etc. These sheets are identical except for their numerical values. In the expenses sheet I want to have columns, each of them applying to a ...
0
votes
0answers
2 views

How to draw a decision curve analysis (DCA) curve in a competing risk model?

I used coxph() to constructe a competing risk model after using crprep() function generates a long dataset. Then I wanted to use ggDCA package to draw a DCA curvr for the model. Unfortunately, my ...
0
votes
1answer
28 views

Google sheet formula on counting categorized data

So I have lists of items. Each items belong to different groups. And then I have data of people who has the items. And I want to count how many items of each group that person has. For example, I have ...
0
votes
1answer
19 views

Return unique values across multiple sheets

On a Google spreadsheet I have multiple sheets that are named by month. Each sheet has a section where it gets all unique values in a range by using =UNIQUE(the_range) which is what I want. I want to ...
0
votes
0answers
9 views

Looking for a formula to extract duplicate values in a separate sheet

I have a spreadsheet that has multiple columns (a site audit). I am creating a template to auto-populate from the main sheet with the data other sheets with a filtered view of the data, based on ...
0
votes
1answer
11 views

how to make make a google scanner

can any one help to write to formula for make google sheet scanner workday volume cross last 10 days average volume workday price cross weekly Simple moving average of 12,24,36,48
0
votes
2answers
32 views

Google sheets Sort by Header in View Mode

I wanna create a sheet with multiple columns of data... I would like to put sort options on the header columns.. so if one of the column is named PLAYERS NAME and then another Header is named AT BATS ...
0
votes
1answer
27 views

How to convert decimal years.months into a number that looks like years.months in Google Sheets

I previously asked how to convert age represented as year.months: 12.06 in to decimal equivalent, in this case 12.5. Fo the second step of my problem, I would like to be able to convert this decimal ...
0
votes
2answers
30 views

Converting Age Ys/Ms to Decimal equivalent

I am trying to convert age written as years and month in to a decimal, where 12.06 (12yrs6mnths) would equal 12.5yrs). I am a relative novice in formula writing and this is my best effort so far using ...
0
votes
1answer
17 views

How to categorize data according to the first n characters of its number?

Sorry for the confusing title, my problem is this: I have a table of items, like this: A B C 1 Number Name Category 2 100-150-135 Mike - 3 150-150-133 Joe - In the same document, I have another ...
-1
votes
1answer
25 views

IF formula referencing data in another sheet and a column dropdown list, populating different answers depending on these two variables

I am creating a workbook which will have an executive summary sheet referencing sheets with more specific details. Below is the sheet that I want to reference in a formula to pull in the different ...
0
votes
1answer
22 views

How to get the average percentage of a column with condition from text data

I start with text data from a Google Sheet like this: A B 1 Subject1 Text1 2 Subject2 Text2 3 Subject1 Text3 4 Subject1 Text3 5 Subject1 Text2 What I want is to get the ...
0
votes
1answer
19 views

Google Sheets: Joined queries registering as two arguments in IFERROR() function

I'm joining two queries together using an array: ={query(A1:B,"Select A where B is not null");query(C1:D,"Select C where D is not null")}. I have it wrapped in an iferror() that, ...
1
vote
1answer
24 views

Make a copy of a column of images omitting blank cells in Google Sheets

I need to convert column A to column B (without blank cells between the flag images) using a formula.
0
votes
1answer
12 views

Stepped prices in Google sheets

I looking for a formula to create stepped prices based on the buying value. Like this: 0.10 until 0.20 = $0.50<br> 0.20 until 0.30 = $0.75 <br> ... with $0.25 steps<br> 1.00 and up, $...
0
votes
0answers
14 views

With a 50 limit using importHTML is there method to increase data rows [duplicate]

Using importHTML in Google sheets is it possible to shape the following Sheet1:Rows 1-30 only Sheet2:Rows 31-61 only This would increase the data limit from 50 to 60 but with two sheets. I understand ...
0
votes
1answer
29 views

Google Sheets VLookup Help

So, I am trying to use the VLookup function to find the value of a cell that corresponds with todays date and return that cell information. However, if there is no date given for todays date, then I ...
1
vote
1answer
22 views

Look up value in column in Google Sheets

I normally don't use spreadsheets in this way so please bare with my incompetence. I want to match values from two columns, getting the row number from the second where it matches and combine this ...
1
vote
2answers
15 views

Count and number how many times the same conditions have me

I'm trying to see if there is any way to count in Google Sheets how many times conditions in other columns are met. Please see image below. In the example, there are 3 conditions (Car, Color, Status) ...
0
votes
1answer
26 views

How to get sort ordered using Unique filter in Google Sheets

I have a flag in a table. When I am trying to sort using Unique formula. It is not sorted as per my requirement. Flag order needs to be Flag T1 T2 T10 T11 instead of Flag T1 T10 T11 T2 T3 ...
0
votes
2answers
19 views

Extracting 2nd occurrence of a number in brackets / Google Sheet/Excel

I currently have cell that has the following output from my Google Sheet: Share Price vs. Fair ValueBelow Fair Value: AAPL ($126.85) is trading above our estimate of fairvalue ($118.02)Significantly ...
0
votes
2answers
38 views

How to sum all the value inside multiple brackets within single cell in Google Sheets

I'm a complete noob to everything that concerns formulas and scripts on sheets but I thought I'd ask if a certain thing was possible: I have no idea if this is even feasible in a formula or if I'd be ...
0
votes
0answers
7 views

Importing Fair Value from simplywall.st via importXML to Google Sheets [duplicate]

I'm trying to import the fair value from https://simplywall.st/stocks/us/tech/nasdaq-aapl/apple via the importXML function in Google Sheets using the XPATH for the FV. The formula: =importXML("...
0
votes
1answer
15 views

Need to count unique occurrences past 365 days from date of service Google Sheets

I'm trying to count how many visits has occurred in the past 365 days in Google Sheets. Each account is in rows and the dates of transactions are dated in the columns. I will be listing individual ...
1
vote
1answer
36 views

IF condition with ISBLANK using Google Sheets *driving me insane*

I have a Google Sheet for investment tracking setup and I'm trying to add a ISBLANK to the following formula: =IF(G67=TRUE,G12, G12*GOOGLEFINANCE(("CURRENCY:USDEUR"))) <--This formula ...
1
vote
1answer
43 views

Results of formulas in same cell

I have a weekly tracking sheet which I'm trying to get the result of 2 averages next to each other in the same cell. Column B has a number value in each cell for each day of a week Column G has a ...
0
votes
1answer
14 views

Return different value for condition in ARRAYFORMULA

I have an ARRAYFORMULA that combines text with a number. A B 1 textA 0 2 textB 1 3 textC 2 =ARRAYFORMULA(A1:A3&" "&B1:B3) But if the value in the B column is 0, I want the ...
0
votes
1answer
23 views

Determining type of hours worked past certain time of day

Link to sheet: https://docs.google.com/spreadsheets/d/1dwpeRWkkbX0adBeQkwMUI6PQa6WqRCNzNluSOw0RU5k/edit?usp=sharing Our business pays a different rate for drive time vs. site time. We also pay ...
1
vote
0answers
56 views

Calculations based on a dynamic cell [closed]

I am recording stats from games. One sheet lists PLAYERS with their stats. It includes a RATINGS formula based on performance standard of previous games. One a second sheet, I list the results of ...
0
votes
1answer
31 views

Is it necessary to use ArrayFormula to get the SUMIF function to work properly in Google Sheets?

I am trying to find the sum of values in a column based on the month in an adjacent column. For example, Sheet 1 has Three columns, one for student remark, one for payment date, and one for amount. ...
0
votes
0answers
34 views

How to formulate a vector in Wolfram Alpha?

Wolfram Alpha is the best calculator. However, today I needed to calculate the vectors, specifically this formula But I don't know how to write it correctly. How should I formulate vectors in Wolfram ...
0
votes
1answer
15 views

Counting a cell if another cell has certain strings

Have a cell which will either have W, L or BB in it. I want to add the cell in O6 if the N6 cell contains any of the strings mentioned below what is the code for this? below is what I currently have ...
0
votes
1answer
23 views

Simplify Google Sheets Sum Formula?

This might be a super easy fix. I'm just getting started with formulas and I want to simplify my document so it can run a little faster. I have a big sum formula: IF(D2<>"X - Done";C2;...
1
vote
3answers
45 views

'contains' and 'starts with' cannot match text with a character after a hyphen

Google Sheets queries cannot match text using contains or starts with when there the text to match contains a hyphen, and the hyphen is not the last character in the text to match. Am I doing ...
3
votes
2answers
125 views

cannot drag query across multiple rows (overwrites data)

Is it possible to click and drag queries? I describe my problem below and also have a reference sheet. cells A1:B5 Category Withdrawal CatA 4 CatB 2 CatA 6 CatB 11 cells C1:C3 Check For CatA ...
1
vote
1answer
74 views

How to convert text to a number in Google sheets

I've built a form that dumps data to a Google spreadsheet and that data, studio reads it... and turns that into a dashboard.. only problem is the text doesn't convert very well for building a dash ...
1
vote
1answer
27 views

How to remove duplicate text in a cell?

I have names and email addresses that are repeated within a cell. Is there a way to just keep unique names and addresses in the cell? For example: Currently A1=martha stewart martha stewart ms@site....
0
votes
0answers
22 views

Formula to Reference an Outside Sheet If Specific Text In Cell Range Is Present It Will Compare Text in Two Ranges and Populate Text

I need a formula to reference an external workbook for to query a cell range for a certain entry to exist, and if that specific text is present, it will cross reference the a cell from a certain ...
1
vote
1answer
43 views

Google Sheets: VLOOKUP formula for a multi line cells range

I have here a sample project, where columns A to C are in FORM RESPONSES TAB/SHEET and columns D and E are in RECORDS TAB/SHEET. In the RECORDS I have a list of names, in alphabetical order, and a ...
1
vote
1answer
74 views

Finding the longest time duration in Google Sheets

I'm trying to find the longest time value (hh:mm:ss) and I'm looking for it in column I, starting at row 5. I've already tried using the LARGE function but that outputs a decimal, not a time. =LARGE(...
0
votes
1answer
12 views

attempting to compile an output List, into a single cell in one Sheet, based on matching criteria in a range on another Sheet

I found a proposed solution on Stack, but I can't get it to work. https://webapps.stackexchange.com/q/120111/270308 Starting formula: =IF(G3<>""; JOIN(CHAR(10); FILTER(ARRAYFORMULA(...
0
votes
0answers
31 views

Formula to Have Sheet Populate Data From Another Sheet If Referenced Cell Exists in Import Sheet

I am doing my best to write formulas, but I am still super new to it all. I am doing my best to figure out how they work and I am stuck at the moment. I am looking for a formula that can reference ...
0
votes
1answer
34 views

How to Import Data From Another Sheet If Any Data is Present In A Cell Range Corresponding to The Same Row

I am extremely basic in my understanding of coding formulas for Google Sheets, so please help where you can. The formula I'd like to put together is to search a vertical range (column) of information ...

1
2 3 4 5
34