Questions tagged [formulas]
Formulas used in spreadsheets edited by web applications like Excel Online, Google Sheets, among others.
2,029
questions
0
votes
1
answer
3k
views
Displaying Indian Number system (Lakhs and Crores) in Google Sheets
I want a custom formula for Google Sheets to format both crores and lakhs in Indian Number System. I have the custom formula for positive, but couldn't figure out how to add negative number system in ...
0
votes
1
answer
102
views
Repeat a table for each row of another table
I have this table in Microsoft Excel Online:
Col A (Month)
Col B (Date)
January
1/1/2023
January
1/2/2023
January
1/3/2023
And I have another table:
Col C (Dept.)
Col D (KPI)
Dep1
KPI1
Dep1
...
- 25
0
votes
1
answer
39
views
Formula to randomly select the name associated with lowest value from data set & a random from the lowest options if tied with drop-down select filter
I have a data set on Google Sheets where for example,
Name
Scores
Region
State
First
Row
Row
Row
Second
Row
Row
Row
On another tab, I have a drop-down list to select a specific Region and State. I ...
- 19
-1
votes
1
answer
102
views
Are named functions able to be shared?
I created a custom named function in Google sheets to simplify a really long formula. If I share this file, will the named function still work for the other user?
- 181
1
vote
1
answer
317
views
Automatically record daily values from a column in a new row
I currently have a project tracker in a Google sheet which records a project name, status character column and three progress columns as below:
I would like to record a daily log of each progress ...
- 15
0
votes
1
answer
25
views
Search a list for current sheet then show specific cell in that sheet
I've been using the below formula which is supposed to search a list of tab names in range AK17:AK32 and then if the user is on that current tab it will then show the corresponding result of the cell ...
0
votes
1
answer
25
views
How to fill a cell using one formula or another
I'm trying to fill a cell(G7) using either the formula G5/B2 or (G6/(B2/1000)). I've done some searching but can't seem to find any information on how to make this work. In the sheet I'm trying to ...
- 3
1
vote
2
answers
41
views
Formula to increase and decrease amount by a percentage found in other cells
I'm trying to create a formula that will reduce and increase a cell value by percentages found in other cells and finding it difficult so hoping someone can help with what i'm trying to do.
I've ...
1
vote
2
answers
222
views
Google sheets - what function can I use to expand MAXIFS function to an entire column?
I'm trying to make the current column (most recent interaction date with client) display the max values (most recent dates) from ContactLog!b:b (dates of all recorded interactions), when the client ...
- 23
0
votes
1
answer
40
views
Google Sheets - Formula or Code?
I'm needing a formula or code that will allow the following to happen if possible.
First search column C for a value (Wk1,Wk2,Wk3,Wk4 or Wk5)
then show the sum of column O In cell X15
the formula ...
0
votes
1
answer
56
views
How to add a missing : to an input
I'm trying to make a military time converter using Slack Workflows. It works by pulling up a form when they press the button in the channel.
It asks them one question.
What is the time in military ...
- 13
0
votes
1
answer
18
views
Can I convert abbreviated duration info to timestamp info in Google Spreadsheets?
I have the following duration input in Google Spreadsheets and I'd like to get an average of all the inputs. But I couldn't find any way to do it. Does anybody know if it's possible?
The input is: 1d ...
- 3
0
votes
1
answer
101
views
Google sheets formula or query for employee schedule
I am currently using google sheets for employee scheduling. On the "Schedule" sheet Row 1 starting from column B I have the employee names listed across the top and column A starting from ...
2
votes
1
answer
678
views
Why does my conditional formatting not work with this formula?
Below is a formula I want to use in conditional formatting. Its purpose is to blur a range in a text concatenation table if a checkbox B15 is unchecked and if the left and right of the string matches ...
- 181
0
votes
0
answers
27
views
Extract data from investing.com to Google Sheet [duplicate]
I would like to get data by using the IMPORTHTML function, especially the Historical Data (monthly)
So with this formula:
=IMPORTHTML("https://ca.investing.com/equities/bank-of-montreal-financial-...
- 1
0
votes
1
answer
69
views
I want "Text starts with" conditional formatting but only if a checkbox is checked. How can I achieve this?
Stack Exchange! Today I wish to use Google Sheets' conditional formatting option "text starts with" to blur out a range of cells (by changing color of text and background). That is the easy ...
- 181
1
vote
1
answer
32
views
Is there a function on google sheets that can show where the information from a particular cell is on another sheet? [closed]
I am working with two sheets on drive and I would like to know if there is a possibility to make some sort of confirmation if the information from another sheet is on the other sheet o not and, if we ...
1
vote
3
answers
80
views
How to find value below a specific cell in another sheet
I have a sheet containing color names on several rows with associated numbers in the row directly below them.
In a separate sheet, I want to use a formula to return the color values in a column next ...
- 11
1
vote
1
answer
310
views
How to create Google sheets range off of 2 separate columns
I need to create a range off of two separate columns within a sheet.
Let's say column A is my index, while column D is third party index.
When using VLOOKUP i can lookup my index to find 3rd party ...
- 11
0
votes
2
answers
30
views
How to make a self-adjusting two-way concatenation table?
My question is best portrayed in the picture below. Thanks in advance!
- 181
0
votes
2
answers
429
views
FLATTEN data from SPLIT without helper columns
I have a column of strings containing data split by semicolons.
Example Strings
game1;video1;photo1
game2;video2;
event1;recape1;videoe1;photoe1
I would like to convert it into one column ...
- 11
0
votes
2
answers
48
views
What formula will allow me to increase or decrease a value and limit the total amount the value can be increased or decreased
I'm trying to create a quota tabulator that will adjust a quota value by .5 point for every full point increased or decreased AND will limit the total increase or decrease by a value of 3 points.
This ...
1
vote
1
answer
39
views
Match taking into account characters like ä ë ï ö ü, á, é, í, ó, ú
Hey I am running against a small problem that I do not know the answer of.
I have 2 lists:
List 1 has all my data, for example
Märio
Toad
Bowser
Pëach
Luigi
List 2 has data too (but this list doesnt ...
1
vote
1
answer
212
views
Concatenate two cells of data if and only if both reference cells contain data
I want the formula to concatenate two cells of data if and only if both reference cells contain data. If one or both reference cells is blank, then the cell with the formula should yield a blank value....
- 181
1
vote
1
answer
24
views
What function would give a TRUE value if either of two conditions are met?
I want conditional formatting to change the cell color of a selected range when the text is exactly one of two possibilities. I have a function which concatenates three cells of text together, and ...
- 181
1
vote
2
answers
35
views
I need help with an IF-AND formula
I need help in creating a formula (in Google Sheets) which concatenates three cells of data together in a specified order indicated by two dropdown boxes. Below is a picture of my test spreadsheet.
I ...
- 181
0
votes
1
answer
559
views
ARRAYFORMULA - Return of Text if cell is not blank and DATE earlier than TODAY
After some unsuccessfull workings hours, I definitely need your support.
I am trying to create an ARRAYFORMULA, so that new rows can be added manually with the wished formulas already implemented in ...
- 1
1
vote
2
answers
35
views
Google Sheets Formula Help
I had a formula that worked in Libreoffice that no longer works after transferring to Sheets, as it used the Aggregate formula. Someone else made it for me and I don't know exactly how to replicate it....
- 13
0
votes
1
answer
90
views
How can one calculate the average of last X values in a row, but ignoring a set number of cells
I've been trying hard to solve this but haven't managed yet.
I have this sheet I'm using to keep track of my expenses on a monthly basis and every month I create a new column to register different ...
0
votes
3
answers
242
views
Google Sheets - List values if date in corresponding column is the same as the date in Cell D1
In Sheet 1 I have a table of orders due for a delivery. Column A is a list of the order numbers, and column H is the delivery date. In Sheet 2, I want to create a list of the order numbers that have a ...
0
votes
1
answer
21
views
Return Average of a column if
Looking to return average if criteria is met, but I can't get the formula to work when one of my criteria exists in multiple columns. Does anyone have an efficient solution?
=AVERAGEIFS(Sheet2!D:D,...
0
votes
1
answer
20
views
Formula to count the text in a rang of cells (with formulas)
I’m trying to have an Excel formula that counts the number of cells filled with text from a range of cells. The problem I have when using =counta is that the cells I’m trying to count all have their ...
- 1
0
votes
0
answers
379
views
Using formula relations in Notion
I have downloaded a "Task" template for Notion and I keep struggling with an issue related to the use of "formula" property.
Basically, a database is created to list a bunch of ...
- 101
0
votes
2
answers
899
views
Formula to extract parts of a multi-line string in a cell [closed]
I have am using a formula that extracts part of a text string from a cell in Google Sheets based on this answer to an earlier question.
The formula is =LEFT(A2, FIND(" ", A2)+1)&" &...
- 21
0
votes
1
answer
40
views
How do I find the unique values of a dataset that has multiple lists in each cell and remove the values I already know are there?
Using Google Forms we have a list of info on what events people are interested in by allowing respondents to check off the boxes to the events they like or add their own events. This column of data ...
- 1
1
vote
1
answer
81
views
How to automatically apply a formula each time a row is added in Google Sheets
I have a Google Sheet set up so that a new row is added with the results each time an employee completes a review in another application. Columns C through H are number values, and I want to sum them ...
- 11
0
votes
0
answers
31
views
Filter multiple columns based on two criteria
So I have this 2 columns:
B (white background) A (white background)
A (white background) B (green background)
C (green background) C (green background)
And I would like to make a function ...
user305241
0
votes
1
answer
55
views
Google sheets: auto sorting based on color and alphabetic order
So I have this list:
B (white background)
A (white background)
C (green background)
And I would like to make a function where I sort it as follows:
C (green background)
A (white background)
B (...
user305241
0
votes
1
answer
373
views
What is the same-sheet equivalent of IMPORTRANGE?
Usecase
For a single google-sheet, let's say:
tab 1 has answers populated via google-form 1
say form-1 has two fields A and B
tab 2 has answers populated via form 2
form-2 has 2 other fields A and ...
- 103
1
vote
1
answer
91
views
Google Sheets: Search for matching words in a list based on the last word in a cell
UPDATE
Now it only need to scan the whole row instead of only the cell next to it.
I am quite new to google sheets and I want to achieve the following:
Lets say we have list 1:
Red apple
yellow ...
user305241
2
votes
1
answer
58
views
How could one use filter in multiple columns to use the maximum value and return the row name that it belongs to?
I am trying to edit this:
=filter(
G27:H60,
M27:M60
= max(
filter(
M27:M60,
regexmatch(I27:I60, "(?i)^(CADET|C/Amn|C/A1C|C/SrA)$")
)
)
)
So that it works across multiple columns/ranges. This ...
- 35
1
vote
1
answer
68
views
How would you successfully return the row name of the maximum value in a column based on conditions in the rest of the row?
=INDEX(G27:H60, MATCH(MAX(M27:M60),M27:M60,0))
This is the formula I want to use, but with the MAX replaced with something that will only take the max based on certain conditions, listed below.
=...
- 35
0
votes
1
answer
54
views
Google sheets array search
I am trying to write a formula where Cells A2:A16 are searched for numerical values and the results are output in cells C2:F2.
Within the data that will populate in A2:A16, there will only be 0-4 ...
- 31
3
votes
2
answers
592
views
Convolve two arrays on Google Sheets?
How can I take the convolution of two distributions on a spreadsheet such as Google Sheets?
Example 1: You want to multiply two arbitrary polynomials together on a spreadsheet symbolically (e.g. 2*x^4 ...
- 403
1
vote
2
answers
54
views
How to use a specific formula in every row in a specific column now and in the future in google sheets?
In Google Sheets I have a very simple formula to subtract the value in column A from the value in column B and show the total in column C.
Column C formula:
=(B:B-A:A)*24
I've selected column C by ...
- 177
0
votes
1
answer
111
views
Fix Errors using Custom Formula in google Sheet with Script (getRange)
I have a google Sheet where i want to get the background colors of the specify range (Cells) using custom formula with script.
within the Cell A1, i put the custom function: =gh(B1:D1)
In script:
...
1
vote
2
answers
20
views
Formula that combines name and true/false into a third column [closed]
I need help to make a Formula that combines name and true/false into a third column.
See image for more information.
I want the employees who are "Leader of the day", to have their names ...
- 19
0
votes
1
answer
65
views
Need help with formula. Do I use XLOOKUP? VLOOKUP? Something else?
I need help with this Google Sheet: https://docs.google.com/spreadsheets/d/1tL3rJlx-SupBoX3AtOTzpePQigA-EB_E_hv7i5kAd7U/edit?usp=sharing
The Master Report sheet is pulling data from the other ...
- 35
1
vote
2
answers
101
views
How can I effectively parse a Google Spreadsheet with data not aligned well within Columns and Rows?
I have a data export in a very inconvenient format. In sheet1, I have student grade data that I cannot figure out how to extract into sheet2, where I am attempting to separate and SUM grades in one ...
0
votes
1
answer
66
views
How do I convert this excel formula to work in Google Sheets?
In excel I have this:
=INDEX(PayoutID,MATCH(TRUE,COUNTIFS(INDIRECT("'"&PayoutID&"'!$D:$D"),$A3)>0,0)) in B3
It looks at cell A3, then using a named range PayoutID (a ...
