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
0 answers
13 views

Array formula giving error of different sizes

I am trying to create a formula to count based on two different set of or conditions. If I remove the second array ({BB$3,BB$4,BB$5}), then the formula works but when I add the second condition it ...
user avatar
0 votes
1 answer
24 views

Tournament table Google Sheets

I want to make a tournament table and I need to: make A1 cell type amount of points based on score (B1 is player 1 score, C1 is player 2 score) I want it to automatically detect wins, draws and loses ...
user avatar
0 votes
1 answer
61 views

Google Sheets - efficiently aggregating one column for grouping, based on minimum value in another column?

I have a sheet with upward of 1000 rows with the following schema: GroupCol | NameCol | NumberCol Example spreadsheet (much smaller data) In the dataset, any given value in GroupCol is repeated 1-10 ...
user avatar
0 votes
1 answer
22 views

How do I make text in a column appear after the rows have been filled?

I have made this spreadsheet to help our mechanical workshop track labour. I have a problem on my spreadsheet where column K displays "follow up". The end users (technicians and foreman) ...
user avatar
  • 3
0 votes
0 answers
19 views

Calculating total number of VISIBLE checked (=TRUE) boxes in a range

I have a range of cells containing checkboxes that are manually ticked based on criteria met and applied to a total value calculation (TVC). The current formula =countifs(C16:16,TRUE) worked for what ...
user avatar
  • 1
0 votes
1 answer
15 views

I Need Help with Formula

Cell A1 displays the current date, A2 is a date 6 months in the future. I need a formula for A3 to display A2 when A1 is 3 months away. I'm new to making any kind of formulas, I have copied and pasted ...
user avatar
  • 1
1 vote
1 answer
73 views

sumif not working sometimes

I have read similar questions but unfortunately didn't help me. Here is a copy (with sensitive info redacted) of my data. Google Sheet In column U and V I am trying to sum columns D to O if T is "...
user avatar
  • 13
0 votes
0 answers
37 views

Average value by highest sums

I'm having trouble with writing the logic for the formula The results should be as above. Markdown table Closing TEU Average per day 23/3/2022 62 no 120 24/3/2022 49 no 120 25/3/2022 168 48 120 ...
user avatar
1 vote
1 answer
30 views

Highlighting a single cell when partial text shows up in a range of other cells

I work for a physician office and we keep track of which patients are ready in a room and which doctor goes in to see that patient. We use a google spreadsheet to track this information. I want to ...
user avatar
  • 13
0 votes
1 answer
25 views

Conditional formatting in Google sheets to indicate overages

To help identify issues faster I am trying to add conditional formatting for column Z to turn red if the amount in that column is greater than the amount in the columns AA or AB and only if column AA ...
user avatar
  • 3
-1 votes
1 answer
49 views

Count all cells with specific data between certain dates

I have a schedule of a team planned on multiple projects on certain dates. I'm looking for a formula that retrieves the amount of days (cells) on PROJECT-A until TODAY().
user avatar
0 votes
1 answer
66 views

ArrayFormula COUNTUNIQUE

How would I turn this formula into an array formula so it automatically populated when a new row is created? =countunique(Z23:AC23) I need the formula to count the unique properties in a row between ...
user avatar
  • 1
0 votes
0 answers
35 views

Referencing text in a specific cell in =SUMIFS formula

Very new to Sheets so forgive any non-clear terminology. How can I use text from one cell, as a criteria value in a =SUMIFS formula? i.e. =SUMIFS('2021 Data'!D:D,'2021 Data'!B:B,"ABC Florist*&...
user avatar
0 votes
0 answers
7 views

Formula for - Throughput (copy per hour) totals, from start finish times and total copies

I am struggling to find a formula to give me total copies per hour throughput from the times on this sheet with no decimals. I cant change the format of the time as the sheet is locked in that format. ...
user avatar
  • 1
0 votes
1 answer
23 views

How to write multiple IF statements in 1 cell based on multiple dropdown choices?

L16-L19 are Yes/NO dropdowns How can I use multiple IF statements in L20? For example: yes yes yes no, “proceed” No no yes yes, “don't proceed” Yes no yes yes, “cannot move forward”
user avatar
  • 1
0 votes
2 answers
26 views

How to create Google Sheet app that retrieves a mix of random words

I have a Google Sheet with a lot of columns, each of them containing words from a category. I would love to create a formula that can retrieve random words and put them together. For example: Colum 1 [...
user avatar
  • 1
1 vote
2 answers
57 views

How Google Sheets ArrayFormula consider other column

In Row 1 of this sheet I have formulas like =ArrayFormula(sum(if(and(not(ISBLANK(C$4:C$49)),$A$4:$A$49>=$A$1),1,0))). What am I doing wrong? How can I change the formula such that the values in ...
user avatar
  • 602
1 vote
2 answers
40 views

How to sort a list-like text in a cell?

Suppose I have in a cell the following unsorted list-like text: - VAL03 - VAL04 - VAL01 - VAL05 - VAL10 - VAL07 - VAL08 - VAL06 - VAL02 - VAL09 I would like to sort that list-like text. That is, in ...
user avatar
  • 113
0 votes
1 answer
36 views

Allocate an amount in one cell amongst or between several cells based on the value of each of the several cells

Here is the link that relates to my question: https://docs.google.com/spreadsheets/d/1dnWuREv3Rf2EWyDQDnyHyZWZ8ItbyKt_EfxPQwWhTnA/edit?usp=sharing Cell C13 is a Retirement Annuity contribution of my ...
user avatar
  • 1
0 votes
1 answer
47 views

Google Sheets – Dynamic Calculation of Data Validation

My use case is to select between two times within a single day. The first cell ("from" time) has list of items data validation set up, where the options are as follows: 12:00 am,1:00 am,2:...
user avatar
0 votes
1 answer
39 views

Can I use SORTN to pull top 5 values from multiple subsets of data at one time?

I have subsets of data for stock symbols combined onto one spread sheet. Symbols are Column A and Performance is Column B. I want to choose the top 5 symbols of each subset based on performance, and ...
user avatar
  • 1
0 votes
1 answer
17 views

Google Sheets: Is it possible to retain an IF/ELSE formula *AND* enable custom number entry into a cell?

I'm using the IF/ELSE formula in a cell: =IF(logical_expression, "TRUE", "") My use case is the following: If the logical_expression is TRUE: A logic-derived value is shown in the ...
user avatar
0 votes
1 answer
22 views

Google Sheets link row [duplicate]

I am trying to set up a project tracker for my team at work to use. We want to have a Master sheet that contains all of the jobs, and then have individual sheets for each member of the team where ...
user avatar
0 votes
1 answer
23 views

How to fill in cells with data from two separate cells?

I have a big Google Sheet and I need to do something like this: x A x B x C A AA BA CA B AB BB CB C AC BC CC What formula would allow me to copy the info from the above and right cells into one cell? (...
user avatar
0 votes
0 answers
16 views

Changing Future Monthly Sales Goals Based On Previous Monthly Sales

What I have is a spreadsheet which needs to calculate future monthly required sales goals, based on an annual sales goal, and previous monthly sales. EXAMPLE: My annual sales goal is $1,200,000. This ...
user avatar
0 votes
1 answer
164 views

Add Check box in Google Sheets based on value of another cell

What formula would I use in Google Sheets to insert a check box into cell A2 if the value of cell B2 is "Signing"? I only need the check box if B2 is signing I can get column A to have all ...
user avatar
  • 1
0 votes
2 answers
30 views

Extract original text from cell with LEFT formula

I'm using the LEFT function like this: LEFT("Lorem Ipsum", 5) Is it possible to get hold of the original text ("Lorem Ipsum") from a cell in which the formula above is used? If I ...
user avatar
0 votes
1 answer
15 views

How to import data from one sheet to another based on 2 cell criteria

I am trying to manage my sheet by using filter formula using 2 criteria is this possible or do I do I need to use another formula. what I am using is =Filter('Form Responses 1'!B2:U,'Form Responses 1'!...
user avatar
  • 1
1 vote
1 answer
433 views

IMPORTHTML stuck on loading

Here's my problem, I've got an html page from my own web site which contains a lot of tables. I'm using IMPORTHTML in Google Sheets to import some of them in different spreadsheets. There's 4 ...
user avatar
0 votes
1 answer
21 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 ...
user avatar
0 votes
0 answers
17 views

Given a table of csv values is it possible to receive suggested formulas? Alternatively is it possible to "solve" a complicated formula given a table?

i.e. Given the csv table with A1=1,A2=2,A3=3,A4=4,B1=10,B2=5,B3=10/3,B4=2.5,C1=0.48,C2=0.24,C3=0.16,C4=0.12,C5=1 Row A B C 1 1 10 0.48 2 2 5 0.24 3 3 0.333 0.16 4 4 2.5 0.12 5 1 Only A1:A4 are ...
user avatar
  • 1
0 votes
1 answer
37 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

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. ...
user avatar
  • 1
0 votes
1 answer
31 views

Tie value from one cell to value of another

I need to find a way to assign the value of the highlighted portion to the value of the cell to the left of it, so that I can drag the url down and autogenerate the values. Is there a way to assign ...
user avatar
0 votes
1 answer
30 views

How to find duplicate entries based on 3 different columns in Google Sheets

I have this dummy sheet with data in Columns A through G. I have colour coded it so I can reference it in my explanation below: In column H I want to to show the word "Duplicate" whenever ...
user avatar
  • 7
0 votes
1 answer
25 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 ...
user avatar
  • 1
0 votes
1 answer
45 views

I need a formula to reverse the order of a column of variable length in Google sheets

The formula and custom function (created by doubleunary here) below repeat rows in an alternating pattern the number of times given in Column B. =arrayformula({RepeatRows($A$2:$A$6, B2:B6); If(SUM(B7,...
user avatar
  • 3
2 votes
1 answer
84 views

Add iferror() for every error that occurs in sheet but keeps formula in this cell

I have a sheet with a lot of formulas. some of them gives me an error. I'd like to make something similar to ctrl+h and replace values but I'd love to find every cell that is giving an error and ...
user avatar
  • 23
0 votes
1 answer
19 views

What's the syntax when using a formula instead of a value as the logical expression?

My SWITCH function returns false when the logical expression is a formula in a cell. Here's what works as expected: =SWITCH(B3,12,"DEC","NONE") output is DEC where B3 contains the ...
user avatar
0 votes
0 answers
7 views

Update a list based on data from corresponding cell

I've got a google form that updates a sheet for locker reservations, on the reservation sheet I have to manually add the locker number and assigned key status. I've got two columns on the main sheet '...
user avatar
0 votes
0 answers
23 views

Duplicate formula not working

My formula "=COUNTIF($C$2:$C , C2) > 1" is not catching all the duplicates while also highlighting not duplicate values. I don't know how to fix it.
user avatar
0 votes
0 answers
10 views

Google spreadsheet help for putting correct formula to automate operations

Your help can save my lots of time & energy, I am new to google sheet & don't know much abount coding, i have made a google sheet to automate our therapy clinic operations by internet learning....
user avatar
0 votes
1 answer
19 views

Formula for Daily Cash Balance Sheet

I have created a Google Form for cashiers to balance out their cash drawers at the end of each day. Currently I have the form set so that they only have to count the amount of each coin/bill and not ...
user avatar
0 votes
1 answer
27 views

Special function in Google Sheet

I can't come up with a combination of functions (MATCH,LOOKUP,etc.) in Google Sheets that would return the row number of the cell that matches the given rule in the given range. Moreover, I need to ...
user avatar
0 votes
3 answers
79 views

Google Sheets: keep track of days spent in different countries

I have prepared a Google Sheet that lists all flights I have taken in recent years, alongside the departing and arriving country: Date & time From To Outbound country Inbound country Days in ...
user avatar
0 votes
1 answer
24 views

How to add a total from a column in another sheet if it falls between 2 dates

I am trying to add a total from a column in another sheet if it falls between 2 dates and it is not working. Any help would be so much appreciated! Here is what I have: =SUMIFS(’2022’!AB:AB,‘2022’!A:A,...
user avatar
0 votes
1 answer
69 views

A Master Sheet / Template for Google Sheets similar to a Slide Master for Slides/Powerpoint

(updated to hopefully better explain the problem) How do I create a Master Sheet with formulas that, whenever those formulas are updated, updates those formulas in sheets made by copying it. So ...
user avatar
  • 1
1 vote
1 answer
63 views

Google Sheets UNIQUE not removing all duplicates

I have a table in which the headers should be all the unique start and end times that are in another table. However, when I use the UNIQUE function, the result still contains duplicates. Does anyone ...
user avatar
  • 13
0 votes
0 answers
17 views

What formula do I need to use to determine the % of events someone has attended since their initial date attending?

I have a spreadsheet with 2 columns; A has names and B has the date they attended the event. I created a pivot table with the data; the rows have the names and the columns have various date data. ...
user avatar
  • 1
0 votes
1 answer
51 views

Automatically apply a formula and a when a new row gets added via a form API

Hi I have a wordpress form, linked to a google sheet that transfers over entries as they come in, now I have some formulas applied, but I don't want to have to add the formula to each new row that ...
user avatar
  • 3
0 votes
1 answer
47 views

Averaging win/loss data by day in Google Sheets

There's been similar questions answered here but I've been unable to apply those tips to my scenario. I keep data on gambling wins and loses. I want to calculate averages based on the day of the week. ...
user avatar

1
2
3 4 5
37