Questions tagged [formulas]
Formulas used in spreadsheets edited by web applications like Excel Online, Google Sheets, among others.
1,986
questions
0
votes
1
answer
1k
views
Average value using different sheets on Google Sheets
I have a data set that's separated by year (so, the first year is Sheet1, the second year is Sheet2, and so on. There are 18 years in total). I want to average the data from different years. At first, ...
CommunityBot
- 1
2
votes
1
answer
1k
views
Conditional Formatting across a range of cells with a formula in Google Sheets
I have a calendar put into Google Sheets with the data put in as dates such as "9/24/2018".
I have a separate sheet with a data set that has rows that classes end such as "9/24/2018".
I want to ...
CommunityBot
- 1
0
votes
1
answer
16
views
How to filter an spreadsheet using filters + drop down feature in google sheets
I've created an spreadsheet and I'd like to filter the information in the rows using a drop down filter. I've managed to create the drop down menu in A3 but now I'd like to show the information in the ...
CommunityBot
- 1
2
votes
2
answers
81
views
How to make sequential list of months?
I want to fill column names with a sequential list of the last day of each month. For example, 3/31/2018, 4/30/2018, 5/31/2018, etc. Is there a formula that can do this? If cell B1 = 3/31/2018, I ...
CommunityBot
- 1
0
votes
1
answer
47
views
Google Sheets formula
I'm using Google Sheets make a spreadsheet with the name in column a, the company in column b and in column f, I need their email.
I have 3 companies, and they have slightly different email formats. ...
CommunityBot
- 1
1
vote
3
answers
2k
views
How to create a formula in Google Sheets that can sum specific cells based on multiple criteria
I am wanting to create a formula that will look at a dozen different cells and sum to the total value of those cells but some cells will not be numbers and I want to exclude those cells in the sum.
...
CommunityBot
- 1
3
votes
1
answer
3k
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).
...
CommunityBot
- 1
1
vote
1
answer
71
views
Assigning a value to a name from a list
I have items that I'm selling, and I've named these codes with the item I'm selling. I want to give these codes the price they should display. I've already got a formula to show the product of the ...
CommunityBot
- 1
0
votes
1
answer
27
views
How to make an alternating value in a single cell?
I want to make a cell swap between two values every couple of seconds, but I don't know how.
EX: A1=1, then a second later A1=0, then a second later A1=1, and it repeats like that, does anyone know ...
CommunityBot
- 1
0
votes
1
answer
96
views
Find whitespace and replace with string from formula in Google Sheet
I have list of cities and I want to create URL for each of them in Google Sheet.
Scenario is most of the part of the URL is same only at the end city name is different so I created a formula
=...
CommunityBot
- 1
1
vote
1
answer
19
views
Jump to cell address mentioned in other cell
A1 shows address of last cell un a column (e.g. A572 or A577). Every time address will change if adding new row (there can be blank rows in between of rows with data).
I need a link in B1 to click and ...
CommunityBot
- 1
-4
votes
1
answer
2k
views
Simple row references in Google Spreadsheets and resorting data
This is my scenario
Users are entering data into rows with several columns
Last column is calculated on values of few previous columns (so it's row number independent).
While entering new and ...
- 43.2k
0
votes
0
answers
28
views
VLOOKUP or INDEX/MATCH used on a HTML Import [closed]
A little background, some friends and I wanted to start a Fantasy Golf league. To start we would pick our teams and fill in manually. I did some research and started to try and automate the process. ...
1
vote
2
answers
77
views
Google sheets range of sheets? [closed]
I have multiple sheets in my Google Spreadsheet. Let's suppose that they are named "1", "2", ... "n". I would like to sum the contents of the B4 cells in all of these ...
- 43.2k
1
vote
1
answer
32
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 ...
CommunityBot
- 1
1
vote
2
answers
54
views
Show each n-th column, hide others in Google Sheets
I have several big tables, and I know that each 4th column is interesting for me, i.e B,F,J,N, etc (The total number of columns is different in different documents)
Is there an easy way (some sort of ...
CommunityBot
- 1
-1
votes
1
answer
78
views
I am trying to find a custom function to find the min value of coloured cells ONLY for a range where some cells have a specific color [closed]
I am trying to find a custom function to find the min value of coloured cells ONLY for a range where some cells have a specific color.
The best I could find is to count the cells with a certain color.
...
CommunityBot
- 1
0
votes
1
answer
15
views
SUMIF sometimes not summing when criteria is met
SUMIF is not summing some of the cells despite the logic being met. For example, cell B9 on sheet 3 should result in 25, but it is coming back with 0.
I've tried
sumif(Sheet2!$B26:$O26, "*MW*&...
- 2,189
1
vote
1
answer
33
views
Need To Randomize Selections From A List But Ensure Uniqueness
I'm building a tool to randomize setup for a very large modular card game. There are several lists from which I need to select 2 to 6 non-duplicate entries, and each list is on a separate tab from the ...
CommunityBot
- 1
0
votes
2
answers
746
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 ...
CommunityBot
- 1
0
votes
2
answers
23
views
How to get data from table to another when you can't re-order columns
Having trouble putting this into words, reading countless threads describing issues similar to mine, but not this exact one. So here's a super simplified version of what I need done.
I have numbers ...
CommunityBot
- 1
0
votes
1
answer
184
views
Extract Data from 2 columns if it matches condition
This is how my main data sheet looks like
And for any item that has customization, for e.g. - ORGANIC, I want to extract/copy just those data, the meal name & total units to another sheet.
And ...
CommunityBot
- 1
1
vote
1
answer
118
views
Can I populate Google Sheets cells adjacent to a target cell?
I have a maintenance tracker in Google Sheets for lighting fixtures. One column is a type of fixture "B", the column adjacent is the problem "C". I'd like to find specific fixtures by name and copy ...
CommunityBot
- 1
0
votes
1
answer
110
views
Conditional formatting with multiple names in list
Col A2:C15 contain a list of names. I need to enter those names in various combinations into one of 15 other cells on formatted like:
H3 = John, Steve, Bob, Dan
Is there a way to use conditional ...
CommunityBot
- 1
1
vote
2
answers
30
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 ...
CommunityBot
- 1
1
vote
2
answers
3k
views
Sort by IP in Sharepoint list
I have a column in Sharepoint containing IP-addresses.
I can't figure out how to sort them correctly.
They are being sorted like this:
192.168.1.1
192.168.1.10
192.168.1.100
192.168.1.2
Rather than
...
- 43.2k
0
votes
1
answer
202
views
Google sheet - highlihghting cell if in the same row other cell not empty
I'm trying to make a worksheet where I log shift hours.
I made columns with dates and to each date a row of hours
I want the date text to be green if in the same row a cell is not empty..
The code I ...
CommunityBot
- 1
0
votes
1
answer
1k
views
Generate "cards" with info in Google Sheets
I have a sheet for an event and I need to generate identification cards like in the image for the pieces on the contest, around 1000 pieces, so no way to manually do it.
I did a similar thing years ...
CommunityBot
- 1
0
votes
1
answer
331
views
How do I calculate weighted rates in Google Sheets? [closed]
I would like to calculate the cost on $D "Rent" with a weighted cost when the respective cell in $B="PB". The calculation would be based on the formula 7x+1.3x=1252 where 7 is dependent on COUNTIF(A2:...
CommunityBot
- 1
0
votes
1
answer
14
views
Can the formula of a cell be conditional based on the dropdown selection of another cell?
Is there a way to create a formula where D9 is included/excluded from the formula based on the answer to the dropdown selection of D13 (Financing Repairs? Yes/No)?
Spreadsheet
I've spent hours ...
CommunityBot
- 1
0
votes
2
answers
37
views
"IF" Functionality Inquiry
So I am trying to figure out how to whenever it's a "Y" and "INCOMING", then it will equal a dollar amount.
So as if a + b = 1 then equals $35
=COUNTIFS(F3:F100,"Y",B3:B100, "INCOMING")
I did it a ...
CommunityBot
- 1
0
votes
1
answer
443
views
Google sheets join arrays
What I'm trying to do is to join few arrays of dates.
For start, I have start date and amount of months I want to generate.
Which I did by using =ARRAYFORMULA(DATE(YEAR(A2), MONTH(A2) + SEQUENCE(C2, ...
CommunityBot
- 1
3
votes
3
answers
3k
views
How can I use ARRAYFORMULA (or something similar) with GOOGLETRANSLATE?
I'm learning a new language and I recently discovered Google Sheets' GOOGLETRANSLATE function. I thought it'd be neat to have a personal dictionary of translations which I can even annotate. So I ...
- 99
0
votes
1
answer
60
views
How to make formula based off a withholding table?
I have been attempting to make a formula to easily calculate federal income tax rates. The picture attached is the withholding table that I've transferred onto a sheet. These are the thresholds I am ...
CommunityBot
- 1
-1
votes
1
answer
19
views
Two drivers, two calculations, one cell [closed]
I have a trucking business with two drivers. I receive a value ($) for each load my business delivers.
I pay a share (%) of that value to the driver of the load.
The share for "Driver1" is ...
- 2,189
0
votes
1
answer
214
views
Conditional Formatting in Google Sheets doesn't work in certain cells with custom formula
Custom Formula doesn't work on certain cells.
A little background, I am making a timezone sheet for a discord where someone input the time they wanted and checks who is and is not available at that ...
CommunityBot
- 1
1
vote
1
answer
467
views
How to get weekly averages for workout tracker data in Google Sheets?
I am making a personal training app that uses data from Google Sheets. I want to create a formula that takes the data from a sheet of workout tracking inputs and creates a weekly average for each ...
CommunityBot
- 1
0
votes
1
answer
76
views
Need a cell to be BLANK - NO ZEROES - if no data is input into the FORMULA RANGE above?
I'm trying to make it so that a column of data sum when data is input into the column above, but remains BLANK if there's no data.
I've been able to accomplish this with AVERAGING, using this formula ...
- 43.2k
0
votes
1
answer
129
views
Google sheets reference Text in Cell in Array Formula & Countif formula
So, I have a Array Formula and Countif formula in place because I need to know the total of entries in a combination of ranges. I need to know the sum of the number of cells that have "X" ...
CommunityBot
- 1
1
vote
1
answer
2k
views
How can I change the formula of one cell based on the value in a different cell?
COLUMN D: Dropdown list of values
COLUMN E: IF function returning different values depending on COLUMN D contents
I have the functionality described above working properly. My problem is that I want ...
CommunityBot
- 1
1
vote
1
answer
174
views
Google Sheets SUMPRODUCT and IFS on different sheets
=ARRAYFORMULA(SUMPRODUCT((Products!B2:B=D2)*(IFS(H2="x",Products!D2:D,I2="x",Products!E2:E,J2="x",Products!F2:F)*(E2:E))))
I have a spreadsheet with an Orders sheet and a Products sheet. Product ...
CommunityBot
- 1
1
vote
1
answer
41
views
Is there a formula to count occurrences of a string in another sheet when the string needs to be "looked up" in a different sheet? (Quiz example)
Sorry for poor title wording.
I am keeping track of quiz questions in a Google Spreadsheet. In order to generate question statistics I also keep track of some metadata about each theme in a different ...
CommunityBot
- 1
0
votes
1
answer
33
views
Import and SUM chosen (from drop-down) column to Sheet1 from Sheet2
What formula I can use in cell C2 to import one column from sheet 2 using a drop-down list?
Here's the document.
CommunityBot
- 1
0
votes
1
answer
2k
views
Time format when adding hours in Quip
After some deep research, I've been looking for a way to sum/subtract hours in Quip Spreadsheet (Salesforce subsidiary).
Apparently, time formats such as 25:00:00 are not listed in their menu Format &...
- 43.2k
1
vote
1
answer
3k
views
How to automatically set date in a cell when a different cell value is changed by a formula from data on another sheet?
I currently have a column, e.g. ColB, containing SUMIF functions with its data range from other sheets and a column, e.g. ColC, with dates that I want to automatically update to the last date ColB's ...
CommunityBot
- 1
1
vote
1
answer
32
views
How to add text conditional on numerical ranges in Google Sheets?
I'm using Google Sheets and I want to display certain text based on the numerical value of another cell. I am a teacher trying to create an assignment where students have to find the best zombie ...
- 248
0
votes
1
answer
27
views
How to extract desired text from a cell
As you can see below I have these data in single cell I want only car nos as my result
CommunityBot
- 1
1
vote
1
answer
2k
views
Auto hide row based on cell text (formula based)
My plan is to hide an entire row if a specific cell in the row showed specific value. I already found the script and the script work amazing. But the problem now, since the script use onEdit function, ...
CommunityBot
- 1
2
votes
1
answer
50
views
FILTER between columns
I have thousands of data in 2 columns
First one is ordered by export and the other by latest activity. The export columns has more queries than the latest activity. (can't change the export to the ...
CommunityBot
- 1
0
votes
1
answer
13
views
Google sheets. Compare the value in column on the 1st sheet with 2 others and then pull cells to y the right of matching value in the other sheets
I have one workbook, 3 sheets. Ultimately I'd like information from sheet 2 and 3 joined with corresponding information on the 1st sheet.
The 1st column has an ID number on each page. How can I make ...
- 10.7k