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
1answer
11 views

Calculate specific contents of a cell that contains a string of numbers and text

I have a sheet that contains cells that have lists of data within them, for example: One cell contains: 1xExample, 2xAnotherExample, 5xThirdExample The cell below contains: 1xAnotherExample, ...
0
votes
0answers
10 views

Help formatting data on sheets from google Forms

I am trying to format the sheet so that all the "items and quantities" list vertically for each time stamp. I have manually edited how i want it to look under the raw data https://docs....
0
votes
0answers
9 views

Reorganize csv data

I work in a school that has multiple data entry forms and formats and I am trying to find a way to automate one example of data duplication. We put student report card grades in on one online system, ...
0
votes
1answer
13 views

Query function with variable in the Col1=

Here' the formula: =query({{$P$3:$P$34;$R$3:$R$34;$T$3:$T$34;$V$3:$V$34;$X$3:$X$34;$Z$3:$Z$34;$AB$3:$AB$34;$AD$3:$AD$34},{$Q$3:$Q$34;$S$3:$S$34;$U$3:$U$34;$W$3:$W$34;$Y$3:$Y$34;$AA$3:$AA$34;$AC$3:$AC$...
0
votes
0answers
11 views

Using a vlookup to pull from a table of a team min and max values for specific stats

So I am working on creating a stat sheet and learning a lot as I go along and using a lot of different tutorials and videos to learn how to write my own formulas, but I seem to be stuck on this one. ...
0
votes
0answers
7 views

I am trying to figure out how to change a column of many dates so that any date that is "less than" 1/1/2021 will automatically change to 1/1/2021

I have the conditional formatting set to change the color of the cell. But I need the actual value to change to 1/1/2021. But only if it's less than. If it's more than that, then I don't want it to ...
0
votes
0answers
10 views

How to add languages I speak on Facebook profile?

I try to add onto my Facebook profile the languages I speak. I selected from the Facebook options the most used tags, for every language, but the changes are not kept. I save the languages, they ...
0
votes
0answers
8 views

Combine Multiple Cells with Dates into one Single text sting cell [migrated]

Setting up and invoice system and i'm trying to make as much of is automatic as possible. I've set it up with a week ending date/formula that auto fills the days of the week Monday thru Sunday. My ...
0
votes
1answer
16 views

How to sum cells that are in hexadecimal format

First of all, read 32, 1023 with the 0x32 and 0x1023 to identify that it is a hexadecimal number. When we only have numbers the sum is done with decimals and it seems to work. The problem is that in ...
0
votes
1answer
38 views

Creating leaderboard in Google Sheets

Example: https://docs.google.com/spreadsheets/d/1vE5DSC5bQMuHGMk3IRtLqFpTlr6AsfFvk0hMYZLts7o/edit?usp=sharing Let's say I have the following table Table 1 is titled "Scoring" Tom Mike Carl ...
-1
votes
0answers
21 views

Google Sheets formula help. How would I create a conditional format for any 2 dates in a row that fall between 30 days of each other?

How would I create a conditional format for any 2 dates in a row that fall between 30 days of each other for any employee based on the infraction type in column B, dates are in column A? Example if ...
2
votes
1answer
65 views

How to pass multiple comma separated values in a cell to a custom function?

Suppose, there are values such as 1,A, 2,B, 3,D in certain cells. My intention is to add 1,2,3 (LHS) separately and A,B,D (RHS) separately. To achieve that, I have written certain custom function in &...
0
votes
0answers
14 views

Perform different operations depending on cell content in Google Sheets

I have a sheet where I would like to calculate the total weight of an object after a number of operations have been performed on it, either adding or multiplying the weight. Any of the modifications ...
0
votes
0answers
24 views

Formula to create Head to Head win-loss-tie record

I am in the process of creating a spreadsheet for a college golf team that will include a head to head matrix and record every player wins and losses against everyone else. However, I face an issue. ...
0
votes
1answer
7 views

Help with filter formula

So on Google Sheets I have this: =iferror(FILTER('RAW DATA 2'!$B$2:$G,'RAW DATA 2'!$B$2:$B>=$B$1,'RAW DATA 2'!$B$2:$B<=$B$2),""), the formula is working. However, I would like to skip ...
-1
votes
0answers
21 views

How to check if every cell in a specified range contains a value and if its true go two rows to the right and add the value to a sum?

I have a lifting spreadsheet. In the spreadsheet, I track sets, and I also have a keycode system to track the muscle group that was used in each exercise. For each exercise I put down the keycode for ...
0
votes
1answer
29 views

In Google Sheets, can you repeatedly autofill until next non-blank cell or until a value in another column changes, whichever comes sooner?

I'm trying to format a CSV file that contains a huge list of products (8000+) that are on separate rows but need all the fields to be on the same row. The trouble is that some of the cells are ...
1
vote
1answer
12 views

Google Sheets Conditional Formatting rows based on color adjacent

I am using Google Sheets to make a homework spreadsheet and have used conditional formatting to arrange assignments by the due date and also each class has a color. When I type in a class it ...
0
votes
0answers
21 views

Using Offset for a dynamic range, Would like to include another duplicate range within formula

So I was given a formula which searches for values on columns B (buy/sell), C(ticker) and D(no of shares) with a reference cell A1 which indicates 'table' height. =UNIQUE(FILTER(OFFSET($C$3,0,0,$A$1),...
0
votes
1answer
17 views

Multiple conditions in same cell google sheets

=IF(A1=5, "green apple","red apple") Add to this new condition: =IF(A2=5,"green apple","red apple") Basically if A1 and A2 = 5 then "green apple", ...
0
votes
0answers
13 views

Google, Sheet, inventory / countif & unique function to lookup update lines

I'm working since few weeks with google sheets & encountered an issue, which i wasn't able to find a solution for. I have a small inventory file with tabs for each received qty (received) , ...
0
votes
1answer
11 views

Colour scale based on a changing value (Todays date - Entered Date) in google sheets

I'm trying to make a colour scale based on Today() between another entered date, basically a scale that starts green to yellow to red. I've attempted to try For green: =DAYS(TODAY(),$I2)=0, For Yellow ...
1
vote
0answers
8 views

Retrieve URLs from website tables [duplicate]

I'm trying to save table data from a website by using IMPORTXML and IMPORTHTML. These tables include text/numeric data, which I can retrieve with no problems, and hyperlinks wich I cannot retrieve. ...
0
votes
1answer
31 views

Count a consecutive streak of values in Google Sheets, if the values aren't all the same?

So, I have a Google Sheet with various readership/writing stats, and I'm using one column to log how many words I'm writing every day. I'd like to use that column to calculate writing streaks, i.e., ...
0
votes
0answers
6 views

How do you change the font color if the cell contains hyperlink on google sheet

How do you change the font color if the cell contains hyperlinks on google sheet? I have 1 cell that contains texts with hyperlinks. I want to change the color of the cell if it contains hyperlinks. ...
0
votes
0answers
7 views

formula to continue date range per row

I am looking to input a formula that I can have a date range of 7 days continue on the next date range of 7 days when I copy the formula down to the next cell. for example: I start the beginning of ...
0
votes
1answer
28 views

Add up my week hours from selected schedules

I would like to have my day schedule to be linked to its day hours so that it produces my total week hours. For example: Schedule is selected from drop down list which is connected by data validation ...
0
votes
2answers
24 views

Sum Column of Unique Rows from two Ranges

Hopefully I explain this well. I have a sheet with 2 tabs, Data and Report. I am trying to make a formula on the Report tab that sums up total paid by new Clients. Essentially: Look at Data!A:I, find ...
0
votes
1answer
11 views

Multiple Columns, Multiple Criteria Count

Sorry if I don't word this perfectly, but I'm trying to count multiple options in multiple columns. The current formula I've attempted is: =COUNTIFS('Data Dump'!AA:AA,{"Awaiting Copy","...
0
votes
1answer
30 views

Need help with Google Sheets!

0 I need help figuring out a formula. In this example: Column E is Start and F is Finish. I want to write a formula in column J that writes an "X" with the following conditions: E > 7:00 ...
0
votes
1answer
13 views

Google Sheets IFS Text result based on multiple conditions FIX & SORT

I'm very new to Google Sheets and I'm trying to make find the correct formula for a text result based on multiple conditions, here is my best crack at it: =arrayformula(IFS(B9:B="",V9:V=&...
1
vote
1answer
19 views

Using multiple IF statements to return a value based on multiple ranges

I'm trying to write an IF statement for whether an engine size is small (900 - 1400), medium (1401 - 2000) or large (>2000). The engine sizes are in the B column. I'm not really sure how to ...
0
votes
1answer
38 views

How to insert first numeric value in a column?

I'm trying to create a formula which will in the absence of data for itself, reference a column for the first piece of numeric data. However, I'm not sure what formula I should be using. Below is a ...
0
votes
1answer
16 views

How to make an output of a column that "filters" through another?

Essentially, I have a Column A with a bunch of websites, and Column B with a bunch of acceptable websites. I'd like column C to display the websites from Column A, except those that contain url's from ...
0
votes
0answers
30 views

How to create a google sheets formula to return a value based on % values in other cells & weighted average formula? (FIXED)

I've been trying to create a scoring system for expected stock market returns - Sheet I'm having problems creating a formula to firstly give stocks a score in multiple conditions for example SS I want ...
-1
votes
1answer
35 views

Get a single row with true and false if the string is contained in a range

First of, sorry for the title, I have no clue what to search for or how to name the question. I got an example sheet which might already explain everything: https://docs.google.com/spreadsheets/d/...
0
votes
1answer
27 views

Filter function in Google Sheets

I need help filter some data in Google Sheets. I want to create a FILTER() function that filters between 2 columns and also discards duplicates. Something like the UNIQUE() function between 2 columns.
0
votes
0answers
18 views

Auto populate comment

Is there a way to have google sheets auto populate a comment based on specific entries in a cell? I am trying to have google sheets auto populate a google form response if certain content is entered ...
1
vote
1answer
15 views

Conditional Formatting Help

I must be having an off day as I'm usually good with formulas - but I'm trying to finalize something for work and my brain is not getting me there.... I need a formula to color cell G75 Orange if cell ...
0
votes
1answer
12 views

Whats wrong with this formula in Google sheets? Im trying to get info from another sheet, but I need the cell to be dynamic

Heres the formula: ='Sector info'!(CONCAT("C", B2)) there's 2 parts to it, the part before the ! which references another sheet, and the second part, that lets me change the row im looking ...
0
votes
1answer
38 views

Google Sheets formulas: how to return where a MIN or MAX values within a range were found?

I'm trying to find a way to get the index of a cell where the value was found; more specifically, I want to return the numerical value of the row where it was found, as I need the number to be used in ...
-1
votes
2answers
27 views

How to add 2 cells if the value of 1 cell is >0, if its <0 then minus?

I tried googling but without luck. How do I add =ADD(E19,G19) but only if g19 is >0. If g19 is <0 then Minus?
0
votes
0answers
18 views

Reference same cell from multiple worksheets automatically with list going down? [duplicate]

I was wondering how can I make my main /master sheet more automatic so I don't have to input every data such as =sheet1!b2, =sheet2!b2, etc. I was thinking if there is some way to make it more ...
0
votes
2answers
53 views

Conditional Formatting 'Blocks' of cells

I am wondering if it's possible to use a custom formula in conditional formatting to highlight a single 3x3 block of cells - rather than a single cell, an entire row/column, or the entire range - ...
0
votes
0answers
21 views

Change cell colour in mastersheet if cell in secondary sheet contains any text

I'm new to this and trying to find a way to change the cell colour of a mastersheet in my schedules document on the basis of an individual schedule in a different sheet containing text (any text) in ...
2
votes
1answer
59 views

Can I have a drop-down list appear in a cell only if another cell contains a specific value?

What I mean by this is that I want a cell to show a drop-down list (the only kind I know of is from Data Validation) only if another cell (NOT containing Data Validation/drop-down list) has a specific ...
0
votes
1answer
26 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 ...
0
votes
2answers
30 views

Text EXACTMATCH + other characters

I have a list of items that have the main item name plus some others that have customization in them, like below. What I'm aiming for is to find how many that are M01 - Supreme Pizza + OTHER TEXT. ...
0
votes
1answer
36 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 ...
1
vote
1answer
36 views

Arrayformula to get closest non-numeric value above

I have Column A with mixed data types (numbers and strings) and in Column B I want for each numbers in Column A to display the string that is the closest above. Example: Title| 1|Title 2|Title Next ...

1
2 3 4 5
36