Questions tagged [google-spreadsheets]

Google Spreadsheets is a part of the Google Docs web app suite. Questions about Google Spreadsheets are off topic and should be asked on webapps.stackexchange.com, unless they specifically involve your computer or browser.

Filter by
Sorted by
Tagged with
0
votes
0answers
17 views

Google Sheets: Select random rows from sheet, but specifically allow for duplicates [closed]

I am trying to build a simple generator in Google Sheets, where I intentionally want the possibility that an item could be chosen more than once. My data set looks like this: +--------+-------+-------...
0
votes
0answers
9 views

How do I merge two rows based on a column match.(Google Spreadsheet)?

I have a sheet that gets values through two forms it looks something like that: ID |Name | Agreement 2324 |Lina | 2324 | |I agree I want to achieve this : ID ...
0
votes
1answer
121 views

EXCEL : Hyperlinked dynamic index

I found below formula to generate a dynamic index in spreadsheet. =IFERROR(INDEX(Sheet!$A:$A,SMALL(IF(ISTEXT(Sheet!$A:$A),ROW(Sheet!$A:$A),""),ROW(Sheet!A1))),"") Is there a way i could add ...
0
votes
0answers
7 views

How to print a very long row in google sheet? [migrated]

I have a Google Sheet that has a very very very long row. The problem I have is that when I try to print it, the row get cut and doesn't go over multiple pages. It there a way to force this? For ...
0
votes
0answers
28 views

How can I add data in a sheet in one sheet and the entire row is added to others?

I'm actually trying to do the following: 1. Sort a sheet automatically when there is a new entry (which the script already achieves, script is shown below) lets call this Master sheet. The master ...
1
vote
1answer
28 views

SUMIFS Using Multiple Ranges?

In the screenshot below, columns A-B are on one tab, D-F are on another, and finally H is on a tab of it's own. Columns A-B is a tab that I use to tally worked hours by each employee less any time ...
0
votes
0answers
10 views

IF used to search a Range in SUMIFS in Google Sheets

This is my first post so.. Let's begin. I am trying to total salaries contingent on data in two ranges. My first formula used 3 SUMIFS to check the range of pay, salary/hourly, full-time/part-time ...
-1
votes
0answers
7 views

How to reuse calculated field? [migrated]

I have a Calculated Field 1 that I renamed to MagicPiField. I have Calculated Field 2 in which I want to use the values of MagicPiField (instead of copying the formula of MagicPiField) But when I ...
1
vote
0answers
25 views

Excel - convert a list of data into a table

I am trying to find a solution that we have in relation to converting a list of information into a table. At present, we can only pull raw data from one of our systems into the following list: DATE 1 ...
1
vote
0answers
10 views

finding difference in two columns of a spread sheet?

lets say I have 3 columns A B C 1 2 2 3 4 5 and i will go on entering numbers in A and B, in C I need to have the difference between col B and col A, can I write a single formula for ...
0
votes
1answer
28 views

Need Formula: Conditional Formatting Using countif and drop down menu

I want to add/subtract two values in two different cell as per drop down option. For eg: If "Buy" is selected from drop down then subtract total price i.e G2 from credit balance H1 and mention the ...
0
votes
0answers
24 views

How do i make adjacent cell as dropdown with values from a defined named range? Please refer image

I have a Dropdown on cell C11 (options - Google, Samsung, Apple, iPad). If I select let say 'Samsung' my expected output on D11 is Dropdown with values from 'Samsung Category' named range Please ...
0
votes
3answers
83 views

Get highest/lowest average in a table

I would like to know the highest and lowest average in a table based on a range of specific values I don't know it that is clear, so I made an image like shown in red. I have tried many things but ...
0
votes
1answer
48 views

How can I conditionally format a cell in column E based on the value of column F?

I have a column of string data that needs to be manipulated with an arrayformula in order to be ranked. I can set this column up beside it and see the correct rankings. If my data is in column E and ...
0
votes
0answers
38 views

Mixing arrayformula and conditional formatting, how to adjust the indices correctly

I have a column of calculated values that looks like # (#) where # is any valid positive integer located in the range E10:E75 with differently formatted data above. I want to conditionally format ...
0
votes
1answer
23 views

How to identify with a formula, multiple occurrences within a criteria for each row?

What is a good way to distinguish when there are duplicate entries given certain conditions. Here is an example of the issue. The data exported (from Amazon Business) of orders creates multiple ...
0
votes
1answer
48 views

How to populate new rows with specific data?

In Microsoft Excel, I have User and Product data stored in a sheet. I may add/remove New User or New Product in this sheet. I have another sheet, which has to put all products to each Users. I need a ...
0
votes
0answers
27 views

Nesting multiple DATEDIFs in IF function?

Does anyone know how to nest multiple DATEDIFs in an IF function? I was able to get the result I wanted to achieve with one one date condition and return the value if it matched but I'm not able to ...
2
votes
1answer
28 views

Conditional formatting with different formats in the same cell

Here's the situation: I have a column with a completion rate from 0 to 138, followed by that value in percentage. ie: 66/138 (48%) I would like to use conditional formatting the change the colours of ...
0
votes
0answers
25 views

Importing Excel into Google Sheets cuts off file

I'm trying to import a very simple Excel spreadsheet (a cross stitch pattern for a friend) into Google Sheets because this laptop doesn't have MS Office and I don't want to download OpenOffice just ...
0
votes
1answer
26 views

Calculating subtotals between two occurrences in another column

I have a column with numeric values and another column with "checkpoint" indications (marked by the value "x") where a subtotal is needed. This subtotal needs to sum the numeric values since the last ...
0
votes
1answer
32 views

I want to increment a cell reference of a formula

I have a sheet with player names that change every 19 rows and I want to be able to quickly drag the row down and have it auto paste the formula with it adding 19 rows and pasting the data in for that ...
0
votes
0answers
63 views

How can I divide spreadsheet rows by a time period?

I have a list of transactions in Google Sheets, and each row has a timestamp. It looks like this: 21 Feb 07:33 - Transaction A 21 Feb 10:03 - Transaction B 21 Feb 19:11 - Transaction C 22 Feb 02:01 - ...
0
votes
1answer
62 views

Google Sheets / Excel : Opportunity cost from a set of cash outflows

Suppose I have a set of cash outflows at the end of each year. I want to compute the cumulative opportunity cost of the cash outflows each year. In the example above, suppose investments return 4% ...
0
votes
1answer
16 views

Automate filling cells in multiples based on another cell when comparing two columns

I have a screenshot of the tables below. What I am trying to get automated in google sheets is a cost comparison based on uses. Whenever the price from the small table gets transposed to the big table ...
1
vote
1answer
16 views

Multiple values same line COUNTIF

I have a table as follows for a musical instrument casting sheet [titled Song Casting) Here's an MWE: +--------+------+--------+-------+------+--------+---------+ | Song | Bass | Guitar | Drums | ...
0
votes
0answers
18 views

Applying conditional formatting row-wise

I'm trying to create conditional formatting based on the following spreadsheet: My intention is that if Space is not empty and Note is, the cell will glow red with anger. I'm using the following ...
0
votes
1answer
68 views

How to sum values in a spreadsheet based on matching criteria in another sheet?

I have a spreadsheet where I track income and expenses. The spreadsheet has two different sheets which I am trying to craft a query between (example tables below). Transactions: Categories: The ...
0
votes
1answer
49 views

check if any text within cell contains the text of any cells within a range

I'm looking for an Excel formula (which ideally also works within Google Sheets) that checks whether any part of the text value of a given cell contains the full text value of any previous cell within ...
2
votes
0answers
37 views

Chromebook - Sort and Filter in Google Sheets while Offline

I have spreadsheets stored offline on a Chromebook that I would like to sort and filter without needing to go online. They were created by downloading from Google Sheets, but when I open them locally ...
1
vote
0answers
39 views

Use scientific notation in log spreadsheet labels

I have a chart with a normal X axis, but a logarithmic Y axis. The range for the Y axis is between about 10−19 to 1. After adjusting the Y axis label to show each power of ten, the Y axis label column ...
0
votes
0answers
53 views

Filter google sheets based on cell value

Is it possible to set up a google sheets filter based on a cell value? I want to use this to make my filter dynamic so i can filter the range based on a value added in a cell, for example I want to ...
1
vote
0answers
33 views

When I sort by A to Z by Column B (Warhouse), all if function don't work

=IF('Inventory Management'!C6="","",'Inventory Management'!O6-SUMIFS('Sales (Release/Transfer) Order'!$J$6:$J$65764,'Sales (Release/Transfer) Order'!$E$6:$E$65764,'Inventory Management'!C6)) C6 is ...
0
votes
1answer
67 views

Excel Formula: How to calculate amount to add, given target average price

Say I'm a potato trader. This is my transaction log so far: Quantity: 2 Price: $2 Total cost: $4 Average Cost: $2 Potato is trading today at $0.5. Now I'm interested in bringing my average cost ...
0
votes
0answers
16 views

Can I have a “bilinking” <blink> character in a Google Doc?

In a Google doc I would like to add a "blink" effect. Is there a way to do this. Either by editing the HTML directly or just adding an inline gif image? Thank you.
0
votes
1answer
50 views

Conditional Formatting a column based on specific condition on other column?

Given:I have table shown below | A | Total | % A | |---|-------|-----| | 2 | 5 | 40% | | 4 | 19 | 22% | | 3 | 10 | 30% | | 1 | 8 | 13% | | 1 | 3 | 34% | And I want to conditional ...
0
votes
2answers
52 views

How to conditional format ONLY the first row that meets a condition?

I have an amortization table with a date next to each row. How do I highlight ONLY the first row that is a larger date than today's date? Let's say the date column starts at A13. Obviously this... =...
0
votes
1answer
78 views

Conditional Correlation?

Given: I have columns something like | Location | Year | A | B | |-----------|------|---|-----| | Delhi | 1980 | 4 | 3.4 | | Mumbai | 1986 | 3 | 3.9 | | Delhi | 1990 | 5 | 4.4 | | ...
0
votes
0answers
11 views

Linked google sheets

I am linking Google sheets with a query import range formula - however the title row is not displaying in it's own row in the new sheet, the first row of data is in the same cell. I would like to ...
0
votes
0answers
102 views

Why don't Excel and Google Sheets allow filtering a non-contiguous column range?

A quick search around the web shows that there is a quite common question about both Excel and Google Sheets: is it possible to create filters for a non-contiguous range of columns? Suppose you have ...
0
votes
1answer
44 views

I want to print the number which occur at least in their respective last cell?

Given: I have Level 1, Level 2, Level 3 columns | Emp Id | Level 1 | Level 2 | Level 3 | |--------|---------|---------|---------| | 1 | E001 | | | | 2 | | E241 ...
2
votes
1answer
57 views

I want to make new column with Logic?

Given: Employee ID and Manager ID. | Employee ID | Manager ID | |:-----------:|:----------:| | E068 | E067 | | E071 | E067 | | E229 | E069 | | E248 | ...
0
votes
0answers
147 views

Setting Google Drive to open Google Docs files with Chrome on OS X

I would like to open locally synced Google Docs files (.gsheet, .gdoc, etc by double clicking in Finder on OS X, with Chrome, which is not my default browser. While solutions have been suggested for ...
0
votes
0answers
14 views

How do you make a Google sheets “=QUERY” with formulas

I have the following Query. =QUERY(ThisSheet!A2:V, "SELECT F, SUM(G),SUM("&ARRAYFORMULA(N("I")*24)&") SUM(J), AVG(K) Where F IS NOT NULL GROUP BY F") The values in Column I is a type "...
0
votes
0answers
23 views

I want to categorize column based on condition

I have two column as below: | I | J | |:------------:|:-----:| | 0.7525801225 | 29.04 | | 0.7413918015 | 31.19 | | 0.732754867 | 35.49 | | 0.4659390421 | 51.62 | | 0.3520797622 | 69.9 ...
0
votes
1answer
49 views

I want to GROUPBY in Excel

I have two column Code and Score. Like this | Code | Score | |:----:|:-----:| | M01 | 4 | | M05 | 3 | | M02 | 4 | | M01 | 5 | | M04 | 4 | | M01 | 3 | I want to get the ...
0
votes
0answers
24 views

COUNTIFS value doesn't changing

I have two Columns both are categorical columns. Like Age_group and Engagement_category. And I want to get count no. of each engagement_category in each Age_group. | Engagement_category | Age_group | ...
3
votes
2answers
83 views

I want to count IF both condition are true Logically count by AND excel

I have two Columns both are categorical columns. Like Age_group and Engagement_category. And I want to get count no. of each engagement_category in each Age_group. | Engagement_category | Age_group | ...
1
vote
1answer
16 views

How to conditional format a column

I am trying to create a conditional formatting rule for a column of dates. The column of dates is a list of move out dates. I would like dates that are within 30 days to be red, dates between 30 to 90 ...
0
votes
0answers
13 views

Dealing with duplicates/normalizing values in a MAX VLOOKUP and INDEX

I'm trying to compose a VLOOKUP that will help me score bar trivia. My table is simple, a list of teams on the leftmost column, and then score columns throughout, i.e. Team R1 A 3 B 2 C 8 D ...

1
2 3 4 5
7