**0**

votes

**0**answers

9 views

### Create a student contact directory using google sheets?

I'm planning to create a spreadsheet containing the following student details:
names, chosen subject, group letter, time slot, student bio, student cover letter.
I would like to know if there is an ...

**0**

votes

**0**answers

15 views

### Google Sheets registering blank cell as zero [migrated]

I am writing a formula into google sheets and I do not want the formula to register a blank cell as zero. The formula is:
=ARRAYFORMULA(MIN(IF(A:A=H12, (D:D-C:C))))
It keeps giving me a zero value ...

**0**

votes

**0**answers

15 views

### Google Sheets: Find the most recent Time Stamp of a specific item when there are multiple entries per item [migrated]

I am trying to document the days a car was cleaned and return specific data about the cleaned car (Ie something is broken, location, etc). So, I want each car to report the most recent cleaning by ...

**2**

votes

**1**answer

51 views

### Google Sheets to Excel

I have this array formula.
=ArrayFormula(IF(P3:P="",,IFERROR(VLOOKUP(P3:P,sort({{C:C,L:L,A:A};{D:D,M:M,A:A}},3,0),2,0),UPLUS(V3:V))))
It works fine in google sheets. But when I converted my google ...

**0**

votes

**0**answers

10 views

### Google sheets: My timesheet is a pre-filled list of dates for the whole year How can I get quickly to today's date? [migrated]

Or at least, to the last entry I made?
remember - i may enter my hours on last friday, but the sheet already has next monday's date pre-filled, so ctrl/end doesn't work.
the year goes to March 2017....

**0**

votes

**0**answers

19 views

### Is there a constant to represent “last row of a column” in Google spread sheet? [migrated]

I have a function like this in Google spread sheet:
SUM(A10:A500)
I'm using 500, but if it's possible I want to use a constant that represents end of the column A (i.e. the last row of a column ...

**-2**

votes

**0**answers

10 views

### Google Sheets Format based on time of day and 2nd condition [migrated]

I have a sign-in sheet where attendees sign-in for their cases. I want to write conditional formatting for the cell to turn red if they have not signed in by 8:45am on the current date - so two ...

**0**

votes

**0**answers

31 views

### List of Subfolder names and file links in google sheets - script

so I'm trying to get a google sheet list of file links in folders and subfolder which I'm keeping in google drive. it should look something like this:
one main folder with a couple of subfolders ...

**0**

votes

**2**answers

41 views

### How can I sum with partial parts of cells?

Let's say I have a spreadsheet look like this.
A |B
----------------
1 |1.0 2016-05-07
2 |0.5 2016-04-12
3 |x.y yyyy-MM-dd
How can I sum those first values(1.0, 0.5)?

**0**

votes

**0**answers

12 views

### How to autofill the same date down a column in Google Sheets?

I'm using google sheets to work on a spreadsheet, there are values in columns A-M. In Column A, there is a date. This date changes about every 400 rows or so (it doesn't change by a consistent number ...

**1**

vote

**1**answer

41 views

### Google Sheets - Conditional Formatting based on column heading of cell value [closed]

I've had a problem that's been bugging me for days and I can't quite figure it out. Scenario:
Names: This is an unsorted sheet where there are a bunch of names.
Team: This is another unsorted sheet, ...

**3**

votes

**3**answers

7k views

### In a spreadsheet, is it possible to separate rows into groups that will not change order when filtered?

I am trying to make row subgroups in my spreadsheet rows so that I can sort my data but have rows in any particular subgroup stay together. This is probably easiest to show with a picture.
Sorting ...

**3**

votes

**3**answers

10k views

### How to freeze more than 5 column in google spreadsheet?

How to freeze more than 5 column in google spreadsheet?

**0**

votes

**2**answers

28 views

### Excel: Method for Calculating Averages

I'm working on creating the table below in Excel and I'm trying to find the best way to calculate the Size (944) / Rent (2,212) / Rent PSF ($2.34) values so that I don't need to modify these formulas ...

**2**

votes

**1**answer

39 views

### How to “Round half to Even”?

I need to "Round half to Even" (Bankers' rounding) a number in Excel. So:
4961,25 => 4961
9738,75 => 9739
4934,5 => 4934
4935,5 => 4936
How can I do it? It must also work "online", ...

**1**

vote

**4**answers

4k views

### excel / google spreadsheet: count substring occurrences within a cell

I've been scratching my head trying to figure out a way to count the number of occurrences of a substring within a cell.
For example:
| | A |
| 1 |John,John,Tom,David|
What ...

**1**

vote

**1**answer

83 views

### How to convert to insert mode when editing formulas on Google Spreadsheets?

Google Spreadsheet forces you to be in overtype mode whenever you edit an existing formula.
The only way out I know is to type gibberish. That is, for how every many characters you want to insert, ...

**5**

votes

**3**answers

2k views

### Pad numbers to specific length in Google Spreadsheet [closed]

I have a column with integer numbers like this: 1, 2, 3, 4, 5 and I want to create another column with these values padded to length of three like this: 001, 002, 003, 004, 005.
Is it possible to pad ...

**1**

vote

**1**answer

35 views

### AVERAGEIFS values that are not NA

I have the following AVERAGEIFS that averages column I grouped by the values in column B
= AVERAGEIFS(B:B, A:A, A:A)
Problem is, sometimes, the column B sometimes contains an NA() error. How do I ...

**4**

votes

**2**answers

2k views

### “Trying to connect” error in Google Docs on Safari

Beginning a few months ago, I started receiving a significant number of "Trying to connect" errors when using Google Docs in Safari.
I can load the page fine, and I can even start using it, but ...

**0**

votes

**2**answers

52 views

### Date stamp when editing rows in spreadsheet

I have a spreadsheet which needs date stamp for each row as soon as there are changes being made in cells within that row. For example, I change something on cell B14 or E14 or G14 and today's date ...

**0**

votes

**1**answer

43 views

### Using ARRAYFORMULA to auto populate a column for rows that contain data

The title just about says it all.
We have a spreadsheet that has a date in Column A and then in Column B it compares the value of Column A with today's date and spits out the number of weeks between ...

**0**

votes

**1**answer

31 views

### Aggregation of overlapping groupings in pivot table

I would like to aggregate a value of overlapping groups in a single pivot table.
An example of my data is:
Name, Value, Attribute 1, Attribute 2, Attribute 3
A, 1, True, True, True
B, 2, True, ...

**0**

votes

**3**answers

28 views

### Generate Contacts CSV from SpreadSheet

My goal
is to use a spread-sheet that has company employees info & to be able to generate a contacts csv/vcard such that when we create new emails I can simply import only the most current and ...

**0**

votes

**0**answers

25 views

### Google Sheets - Selecting latest date across multiple columns for a row that matches a criteria

Here is the spreadsheet setup
Now I'm creating a report where I can enter the Member_# in cell K1 and the latest date from columns D, F, and H appears in cell K2 as well as the test label from the ...

**6**

votes

**2**answers

4k views

### Extracting email from text string in excel/google spreadsheet

I'm looking to extract email addresses from text strings in Google Docs/Excel and email them automatically, where the content of the cells are currently unknown (until updated by user).
For example, ...

**9**

votes

**4**answers

23k views

### How to Label / Tag Items in a Spreadsheet

I'm a novice at spreadsheets, so I hope you'll forgive me if I'm asking a super obvious question.
Is it possible to use tags in Excel/Google Spreadsheet? I'm creating a spreadsheet to log all the ...

**-1**

votes

**1**answer

22 views

### How do I count the number of “+” in a function in google spreadsheet?

So my function in A1 is 0,5+0,5+0,1
What I want to do is make a formula in B1 that counts the number of "+" elements in A1, how can I do this in google spreadsheet?

**0**

votes

**0**answers

11 views

### How do I get the index, within a range, of a cell containing a substring `x`?

How do I get the index, within a range, of a cell containing a substring x? I tried having a look at match, but that seems to require the match to contain the entire searchstring. I also had a look at ...

**0**

votes

**1**answer

92 views

### Extracting data from an excel sheet into another excel sheet

I have a sheet with certain data arranged like this:
X8 - something - something2
X12 - somethingelse - asdfg
X8 - asft - sastaryraavt
X8 - random - random2
I need to extract only and all of the ...

**0**

votes

**1**answer

32 views

### I have a list of names, and tags per row (each tag is in a new column), how do I get a pivot of names per tag

My data is set up in such a way where I have
John alpha beta gamma
Mary beta gamma upsilon
Zack upsilon alpha omega
What i get is only the first column of data...
name Alpha ...

**-1**

votes

**1**answer

18 views

### Google spreadsheets [closed]

I would like to assign the value 1 to the word "retail" and the value 0 for the word "wholesale". So if I can multiply cell (A1*.08)* retail/wholesale so if B1 has has the word retail the value of C1 ...

**0**

votes

**1**answer

52 views

### Google Spreadsheets: how to generate a report for the most commonly used words

I have a google spreadsheet, I use to log programs that occur in my work everyday, I have a column, I use to register the names of each individual issue.
What I want is to somehow generate a report ...

**1**

vote

**1**answer

83 views

### Excel If cell is blank show nothing else carry out this IF statement

I have the following formula
=IF(N37="Y",5,-5)
At the moment it shows -5.00 if the cell N37 is blank however I have it to display nothing until the cell contains N or Y.

**0**

votes

**0**answers

14 views

### Add two more multiple row value in excel based on two column [duplicate]

i want to add two TimeDiff value for date UserId -16 and LoginTime - 2/2/2016
UserId LoginTime loginid elapsedTime TimeDiff
9 2/1/2016 104805 15321 15341
9 2/2/2016 105096 ...

**0**

votes

**0**answers

68 views

### Excel - Decision-Tree Budget / Shopping List

I'm planning the purchase of a bunch of A/V equipment. There are lots of different options for where we might end up. Some of the equipment is base equipment, we need it no matter what. For some of ...

**0**

votes

**0**answers

85 views

### Cleaning up data fetched from Google Spreadsheet to Excel

I've been trying to synchronize and build a local database with Excel data. I need to build the database and program to create user accounts, manage the packages, track and log the status of the ...

**0**

votes

**1**answer

126 views

### How to convert a duration into a number of hours in Google Spreadsheets?

Trying to organize a timesheet. I have a cell with a duration of, say, 84:30:00
(84 hours, 30 mins, 0 seconds), which has a number format of "Duration".
How can I calculate the total number of hours (...

**-1**

votes

**1**answer

24 views

### Google Spreadsheet - modify other cell

Is it possible for cell A1 to modify the contents or properties of cell B1?
For example: Change text or background color

**3**

votes

**2**answers

67 views

### COUNT all “P” from a row

I have a presence row in a sheet (Google or MS Excel, does not really matter), having values "A" or "P" for each name.
I need to count how many "P" I have, to make the cell RED if there is any "P" in ...

**1**

vote

**1**answer

37 views

### Populate Spreadsheet with files within a folder

I need to manage a lot of files within folder related to a project, at the moment these files reside in Dropbox. So, I will like to find a way in which I can have a Spreadsheet populate itself with ...

**0**

votes

**1**answer

51 views

### Total Unique items in col B for user ID x

Tried everything i can think of that google suggests, will copy and paste a few here for review, but simply put. I have a string of text as an ID say "id-1" I'm trying to count the number of unique ...

**0**

votes

**0**answers

25 views

### How can I compare a student's first name, last name, and day of detention to identify what lunch period they have in google spreadsheet?

I have a google spreadsheet that auto-fills last names in column B, first name in column C, and day (not date) of detention in column F.
In another set of columns I have the students' lunch period ...

**0**

votes

**0**answers

12 views

### Value to drop-down objects?

I think I might be over-thinking, but since I have no experience with any kind of spreadsheet, I just don't know where else to turn.
What I'm trying to do:
I want a drop-down list and each object ...

**9**

votes

**11**answers

24k views

### Can you convert an address to a zip code in a spreadsheet?

Given a column of street addresses with city and state but no zip in a spreadsheet, I'd like to put a formula in a second column that yields the ZIP code. Do you know a way to do this?
I'm dealing ...

**2**

votes

**2**answers

325 views

### How to create a data series for exponential decrease diagram

I want to create a pricing model this way:
If you need this product for 50 people, you should pay 2500 (units) per person.
.
.
.
.
If you need this product for 10,000 people, you should pay 300 (...

**46**

votes

**7**answers

134k views

### How to create a formula for every row in a column in Google SpreadSheet?

I use three columns. A, B and C.
In column C I have a formula every row =A1*(1.6*B1) and then for the next row I have =A2*(1.6*B2) in C2.
How can I do so I don't have to type in the new formula in ...

**0**

votes

**1**answer

340 views

### Google Sheets - How to find penultimate (second-last) non-empty cell in a column?

I have a column that'll have dynamic filled in fields but will be empty for the most part. I want to find the difference of last two values found in a column but nothing I've tried seems to work.

**0**

votes

**0**answers

14 views

### Depending Data Validation

I want to create a table with select boxes(dropdowns) where the content of the second select is dependend on the first select
Producer | Product | ...
I would like to avoid heavy if/else clauses ...

**0**

votes

**2**answers

58 views

### How to keep a columns formula applied to a newly added row?

I have a recurring formula down an entire column but every time I add a row the formula isn't applied to those new cells in that column. How can I make the formula permanent so it is consistently ...