**0**

votes

**0**answers

7 views

### AVERAGEIFS values that are not NA

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

**-1**

votes

**0**answers

9 views

### SUMIF formula in Google Sheets [migrated]

I am having trouble with a formula and maybe you can help me.
I have created a Google Form where I am capturing when and how much my baby eats, among other things.
I have a Sheets responses file ...

**0**

votes

**1**answer

28 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 ...

**-1**

votes

**0**answers

9 views

### Google Sheets Protected Ranges Not Working

I have used Protected Ranges in Google Sheets successfully for a couple of years but since last Friday it is no longer functioning.
This is the Help Topic but this procedure doesn't work anymore.

**0**

votes

**3**answers

23 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

**2**answers

37 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

17 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

**0**answers

8 views

### Grabbing every data from every sixth cell in a row for a sparkline [migrated]

I am creating a spreadsheet in google sheets where every 6th column is a "total" column. I need to creat a sparkline that will grab the value of every 6th cell in that row.

**1**

vote

**0**answers

22 views

### Don't disturb formulas on excel when cut and paste [migrated]

I have the formula:
=IF(Q5=time(0,0,0),1,0) on cell A3.
It works fine, but I need to "cut" and paste the contents on Q5 elsewhere and then Excel "updates" the formula changing Q5 for the new cell. I ...

**0**

votes

**0**answers

12 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 ...

**-1**

votes

**1**answer

20 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

9 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

56 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

27 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

36 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

47 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

54 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

45 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

24 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

21 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

53 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

25 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

**0**answers

24 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 ...

**0**

votes

**0**answers

43 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, ...

**0**

votes

**1**answer

230 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

**1**answer

47 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

13 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

48 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 ...

**1**

vote

**1**answer

126 views

### How to simplify Excel/Google Sheets formula with redundant method calls?

I have an Excel (Well, technically Google Sheets) workbook containing product inventory warehouse storage information. I figured out how to construct queries so it grabs the needed information from ...

**0**

votes

**1**answer

25 views

### copying bank account information into excel / google sheets, lists all info in 1 column instead of grid formation

My bank account informaiton on the website is in a grid formation (ie: name of transaction, value, total in bank)
When I copy and past this information into google sheets or excel, it lists all this ...

**0**

votes

**1**answer

41 views

### How to highlight cells with formulas in Google Spreadsheet

Is there a way to highlight all the cells containing formulas (in contrast with raw numbers) in Google Spreadsheet?
Thanks.

**0**

votes

**1**answer

36 views

### Reference a cell with input from another cell

The final objective is to reference the last populated (higher row number) cell from a column (its value).
I found a function that returns the row number of the last populated (higher row number) ...

**0**

votes

**0**answers

18 views

### Spreadsheet styling every X rows

In Google Spreadsheet, how can I change the background color at every X rows.
For example, Let say I need every fifth row to be in gray background, I can I achieve this?
I already know how to do ...

**1**

vote

**0**answers

102 views

### Shortcut keys in Google Sheet don't work?

Background:
I have version 45.0.2454.85 m of Chrome (the latest when writing this question). I have a Swedish version of Logitech MX 5500 physical wireless Bluetooth keyboard.
Problem:
My problem is ...

**0**

votes

**2**answers

429 views

### (Google Sheets/Excel) SUMPRODUCT but ignore empty cells

I'm building my girlfriend a Google Sheets spreadsheet that will allow her to automatically compute her students' grades based on grades she gives her students on different projects, exams, and ...

**0**

votes

**1**answer

412 views

### Fetch Google Sheets data with Excel Power Query

I am using Excel 2103 and Excel power query.
The fetch data from web works fantastically on most websites, however I cannot get it to work on Google Sheets
I have tried using the share URL as well ...

**1**

vote

**1**answer

55 views

### Creating a table similar to pivot table

I'm not sure how to explain it, but I hope this example makes it clear.
Data:
Yes/No | Place1 | Place2 | Place3
----------------------------------
Yes | Thing1 | Thing2 | Apple
No | Apple | ...

**0**

votes

**0**answers

31 views

### Create formula on the fly with Google Spreadsheet

I want to add a formula to each row in a database;
for example:
=query(a1:c5,"select A,'=5')
I want to see 5 and the cell, not '=5',
or
=query(a1:c5,"select A,B,'=regexpreplace(...)')
...

**0**

votes

**1**answer

19 views

### Summing the above 7 rows in same column based on a value

I have a series of values that repeat like days of the week doing down in the same column.
Monday : 2
Tuesday : 5
Wednesday: 6
Thursday : 1
Friday : 8
Saturday : 12
Sunday : 4
Monday : 3
...

**4**

votes

**2**answers

3k 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, ...

**0**

votes

**1**answer

22 views

### Format as currency within a formula

I am trying to sum a column of dollar amounts and label it as "Total: $xx,xxx.xx" in the first row of that column.
I have gotten as far as
="Cost - " & Text(SUM(B2:B), "$0.00")
BUT, it is not ...

**0**

votes

**1**answer

113 views

### Microsoft Excel: Merge two sheets and dedup

I have a pretty simple question and google can't seem to return what I'm looking for.
Essentially I have two sheets in one workbook, both contain the exact same columns. The primary key (and first ...

**2**

votes

**1**answer

41 views

### Excel autonumber range

Most of you know about this little feature of Excel, as well as almost any other spreadsheet software like Google Spreadsheets called Autonumbers. Example.
What I'd like to know if it's possible to ...

**2**

votes

**2**answers

225 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 ...

**0**

votes

**0**answers

54 views

### Google drive spreadsheet is showing black shadow on while typing letters

I mm using Google drive spreadsheet. It is showing a black shadow while typing letters in excel.
How do I resolve this?

**1**

vote

**1**answer

77 views

### Count number of cells that match certain criteria with formulae

Suppose I have column with dates:
2015-01-10
2015-08-12
2014-01-02
2015-01-28
2016-10-14
I want to count how many dates fall to January. I would supopse that formulae is like this ...

**0**

votes

**1**answer

13 views

### Excel - Signs in different columns

Given I have put by signs in a different column, making my data look something like:
How do I write a function to get the sum of these values (=198) with taking the signs into consideration?