Questions tagged [formulas]
Formulas used in spreadsheets edited by web applications like Excel Online, Google Sheets, among others.
2,029
questions
1
vote
0
answers
27
views
Count same occurrence one time
I have this data in my Google Spreadsheet which you can see on the image. Now please notice the column "F", you will see the same data "W" or "L" multiple times. What I ...
1
vote
2
answers
73
views
Google sheets - formula for subtotal based on a field
I am sure this is available somewhere but K could not figure out how to make it - I am looking to create a summary based on a keyfield. Rather than be confusing, here is what I am working with and ...
1
vote
1
answer
240
views
count the number of unique records within date range
I have a spreadsheet with the following three columns (A=resource code, B=start date, C=end date). I am trying to count the number of unique resource codes that overlap for any date of the year.
A
B
...
1
vote
0
answers
49
views
NETWORKDAYS.INTL Method Changed
Google sheet's NETWORKDAYS.INTL Method supposes to calculate number of working days between two given dates, and consider weekends and holidays.
I use this method a lot in my functions, but today I ...
- 25
1
vote
1
answer
29
views
Show most recent date a theatre is visited Google Sheets
I visit 4 theatres regularly, sometimes multiple theatres on the same day but never the same theatre on the same day. I have columns A, B and C in the picture attached.
Column A records the date a ...
- 25
1
vote
1
answer
136
views
How to search a range for a matching word within a string?
What I'm trying to do is this:
Cell A1 contains a sentence. The dog had black spots all over
Column B contains a range of keywords. Let's say B7 contains dog.
What would be the formula to get C1 to ...
- 13
1
vote
1
answer
263
views
Compare Columns, Highlight Missing Values
I am looking to compare two columns, a "Roster" column and a "Present" column and highlight any missing values.
The "Roster" column is a fixed value, but the "...
- 11
1
vote
0
answers
35
views
Incorrect sum in google sheet
I have a formula to calculate hours before and after certain times in google sheet. When i mark several cells I get the wrong sum in the right corner. All times are in format duration. When I use the ...
1
vote
0
answers
207
views
Incorrect sum Google sheets
In Google sheets I have following formula "=(C1-A1) + (B1-D1)" to get the duration between two times, including a break. I have this in several cells and before I could mark these cells and ...
1
vote
1
answer
100
views
Rotating Shift by Week
I am attempting to create an auto-rotating shift as follows:
There are 9 employees.
The shifts are:
Tuesday 12 - 2 in half an hour blocks (12, 12:30, 1:00, 1:30)
Thursday 11:30 - 2:00 in half an hour ...
1
vote
1
answer
1k
views
Use of COUNTIF in an array?
Overview
In Google Drive I have a sheet in which I frequently export my Spotify playlists. From this list I like to document artists I listen to.
In short:
Column A holds all the artists occurring in ...
- 23
1
vote
1
answer
444
views
custom formula in pivot table google sheets
I'm trying to filter on a value in a call in a pivot table.
In my "config" tab I have the following data:
In my source data tab I have the following date:
And in my pivot table I have two ...
- 139
1
vote
0
answers
32
views
Formula Help - Pricing/ROI Calculator
I am trying to figure out the proper formula for Sheets and am running in to a brick wall.
I am building a calculator to requires one cell for me to enter monthly subscription cost that can be ...
1
vote
0
answers
183
views
How do I add HTTP request headers to a Hyperlink in Google Sheets
I'm generating a Hyperlink programmatically with reference to the contents of another cell using =HYPERLINK(B5&"?some_query, "my_text").
But there's no option here to add a HTTP ...
- 111
0
votes
1
answer
62
views
IMPORTHTML - Add a Column with the URL
I'm currently using IMPORTHTML to import tables from a website with 5 columns.
I need to add a 6th column that is called "url", which will be the URL that I used in the IMPORTHTML formula.
...
1
vote
1
answer
29
views
Dynamic and not dynamic data in the same Google Sheet
I am taking a range of names from another tab with the formula
=SORT(UNIQUE('brands check'!B9:B8228)) in column B.
I would like to add descriptions in other columns (D, E, etc.), but as soon as I add ...
- 11
-1
votes
1
answer
46
views
Average the smallest 3 values of the last 5 values, excluding blank cells
I'm trying to average the smallest 3 values of the last 5 valves, excluding blank cells, in a row.
I have a working Excel formula, but I'm not savvy with Sheets yet.
Here is a screen shot of my Excel ...
0
votes
3
answers
27
views
Find totals for each category based on categorization
I'm using numbers to categorize items in a list, each with a $ amount.
Column A has a number, 1-15, depending on the item in column B. Column C has the cost of the item in column B.
I want to find the ...
- 1
0
votes
1
answer
438
views
Google Sheets LOOKUP last available value from another sheet
I have an excel document with the LOOKUP formula which grabs the last entered date from another sheet. From below, you can see the date for 'Person One" is also the last date entry on the second ...
- 65
1
vote
1
answer
117
views
Formula to count number of cells with a name in if it matches this month
looking for a formula that will count names in one column if it matches another date column which is this month.
Column A - Name
Column B - date
Column C, Row 1 = todays date
The formula would look at ...
- 31
0
votes
1
answer
291
views
ArrayFormula and Categorize Data in Google Sheets using Regular Expressions
I'm following this post to categorize data in Google Sheets using regular expressions.
The person who wrote the answer was able to get the formula to automatically write down the category in column D.
...
0
votes
1
answer
94
views
Create chart in Google Sheets to predict win/loss data over time [closed]
I've been unable to find tips to apply to my scenario. I keep data on gambling wins and loses. I want to create a chart in Google Sheets to predict future win/loss data over time with dollars on the y-...
- 13
0
votes
1
answer
15
views
importing data from several sheets and average without including 0s
I have the following formula:
=query({'6th LA'!A2:G66;'6th SS'!A2:G66;'6th Math'!A2:G66;'6th Sci'!A2:G66;'7th LA'!A2:G93;'7th Sci'!A2:G93;'7th SS'!A2:G93;'7th Math'!A2:G93;'8th LA'!A2:G80;'8th SS'!A2:...
1
vote
1
answer
53
views
How to make a variable relative to the sheet it is in? [closed]
In Google Sheets, I can define a variable such as interestRate by using Data -> Named Ranges.
Then I can use the variable interestRate as if it is a number, like 10%.
But what if in Sheet2, I want ...
- 801
0
votes
2
answers
90
views
Google Sheet formula on showing the lowest number if none of the values equals 0 does not work
I'm trying to create a formula that meets the following conditions:
If any of the cells is 0, then just show 0
If none of the cells equals 0, then show the lowest number of those cells
So let's say ...
0
votes
0
answers
69
views
Auto delete certain rows on the first of every month - Google Sheets
I've built a Sheet on Google Sheets with multiple tabs and formulas for me and my coworkers to use.
Only thing I couldn't do is set a formula that deletes multiple specific custom cells on multiple ...
- 1
1
vote
1
answer
27
views
How can I change a row to be text instead of a formula?
I am copying a row of phone numbers that are text strings from Excel to Google Sheets.
The phone numbers change from text to formulas when pasted and return #ERROR!
For example, the text +49 4942 ...
0
votes
0
answers
33
views
IF and Arrayformula for Google Sheets
=IF(C2=""(vlookup('FBA Returns'!B:J,9,0),vlookup(C2:C'RMA Main Sheet'!D2:H,5,0)))
IF "C" is blank, Vlookup "B" on Sheet 1, IF "C" is NOT Blank Vlookup Sheet 2.
...
-1
votes
1
answer
190
views
Formula to select the name associated with lowest value from data set & a random name from the lowest options if tied
In Google Sheets, I have 4 Names associated with 4 data points that update throughout the month. I'd like to auto pick the name associated with the lowest data point. If there are 2, 3 or all 4 that ...
- 1
-2
votes
1
answer
31
views
Have answer cell read text different than what is displayed in cell it is pulling data from
I have the following table of data with the row of focus highlighted in red. This is being done on Google Sheets.
With the following summary page and row of interest highlighted in red
The question ...
- 1
0
votes
1
answer
19
views
How to set a max value for individual cells in a column, where if the actual number would have been higher, it reverts to the max value?
I have a column of scores, where I want to set the maximum score anyone can receive to 1000 points. It is possible to get more points than 1000 but that is the max.
How do I set up so that my Google ...
- 3
0
votes
1
answer
20
views
Connect Participant Scores(Column D) to their id Numbers(Column A) by sorting Columns B and C
I have 4 columns of data. I am trying to connect the Scores to the idNumbers.
Two sets of data. Column A and B are idNumber and Member names.
And Column C and D are Participants and their Scores.
The ...
- 3
0
votes
1
answer
68
views
How to pull data from one sheet to another based on duplicate values in a specific column. Google Sheets
I need to figure out how to pull rows based on non unique data in a column to another sheet.
For example in the below table I want to pull rows based on duplicates in column "Number" so in ...
- 1
0
votes
1
answer
221
views
Countifs between 2 dates from an import range
Looking for some help on a Countifs formula from an import range (if this is the right thing to use(?))
I have a sheet tracking rejected bookings on certain dates, and I want to pull this into another ...
- 3
0
votes
1
answer
384
views
Google Sheets Row number of Filter+Search result
I have created a filter+search formula that returns me data depending on search fields input.
It works fine, however I was wondering if there is a way to get a return of a row number in the original ...
0
votes
1
answer
330
views
Indirect ► Transpose ► Importrange = It is not a valid cell/range reference - not an expert, need help
I'm trying to get a range from another spreadsheet, to appear depending on an ID number placed in the current sheet, I've been doing that for other data within the sheet and spreadsheet, but whenever ...
- 11
0
votes
1
answer
24
views
Formula that references a different sheet each row based on set criteria?
I'm wondering if someone can help me. I have a Google sheets workbook with more than 50 individual sheets. This workbook has a primary 'index' page that collates set data fields from each sheet. I'm ...
0
votes
1
answer
23
views
Looking for less messy Google Sheets formula
I am making a spreadsheet counting how many unit each 'thing' has and the formula is getting out of hand. The number in the row needs to be multiplied with the number in row 1 and then added together ...
0
votes
1
answer
729
views
Search across multiple columns for a value(s) return multiple row results
I am a rookie at this and I spent hours trying to figure this out but can find anything that really helps.
Scenario 1:
Looking at the photo.
I want to search columns C through M for a selected color(s)...
0
votes
0
answers
14
views
Google Sheet - compute apr for less than a year old investment
I am trying to convert an excel spread sheet I have kept for over 15 years of investments into Google Sheet, but I am getting a formula error on all purchases I have made that are less than 1 year old....
- 1
0
votes
0
answers
114
views
Google Sheets - Link to Left cell of "MAXIFS" result
I have table of heroes with win rates. Now I want to do mini table with best builds for each hero.
First column is pretty easy with =UNIQUE(B2:B)
In second colunm I use =MAXIFS(E:E,B:B,J2) that ...
- 119
0
votes
0
answers
17
views
Results from many dropdown lists
Sample Scenario: Sheet 1 has 15 lists and each list has multiple dropdown options.
Sheet 2: I need to have the results from the options chosen on the lists on sheet 1 divided by lists.
So if on the ...
0
votes
1
answer
232
views
sequential number as long as they are in the same category, but if the category changes, the numbers start over for each category
I have two columns in my Sheet, one for the serial numbers and the other for the category.
I want the numbers to be sequential as long as they are in the same category, but if the category changes, I ...
0
votes
1
answer
35
views
Dynamically split a row depending on cell results & copying the beginning of the rows each time
I'm trying to dynamically split a row in columns depending on the results of my cells with copying the beginning of my rows each time.
What I have :
What I need :
The Google Doc example
I've tried ...
- 3
0
votes
1
answer
118
views
Is there any way to use Named Ranges in Google Sheets to replace the column letters in formulas for readability?
I have formulas that reference values on other sheets such as =OtherSheet!M2
For readability, is there a way to substitute "M" with something else so I can remember what data is being pulled ...
- 101
0
votes
1
answer
276
views
Count number of occurrences in Google Sheets, with multiple criteria and not considering duplicates in the row
I'm looking to count the number of rows that contain a certain text (specified in the adjacent cell), from a specific range, on the basis that the criteria have been met.
I currently have this formula ...
0
votes
1
answer
24
views
Filtering by flight times in Google Sheets
I have flight delays ranging from hours ahead of schedule to delays of all durations. I need to countif any delay that is longer than 20 minutes. The values in the column take on this format: "-0:...
- 1
0
votes
1
answer
139
views
Google Sheets formula to format substrings of column concatenation?
If I have a spreadsheet with columns for author, year, title, publication, etc, could I add a column that concatenates and formats those as a citation for a bibliography?
It would be something like ...
- 285
1
vote
1
answer
697
views
Regexextract to get a text in the middle of string
SAMPLE SHEET
I have data in a column in this format:
1 P01 - Product Name here - PAID IN FULL
2 P02 - Product2 Name here - PARTIALLY PAID 20
1 P03 - Product2 Name here
I've been trying to ...
- 345
1
vote
2
answers
135
views
How to return a value from a row from the last column a text appears
I am having problems returning the correct value i need.
I enter data on this table.
And i want to return some values on the other table.
In that second table i want to return the last date his name ...
- 11