google-spreadsheets interview questions
Top google-spreadsheets frequently asked interview questions
I'm trying to combine multiple cells with email addresses into one cell where emails are separated by commas.
I've used this formula:
=P2 & ", " & Q2 & ", " & R2 & ", " & AD2 & ", " & AE2 & ", " & AF2
But only two cells have emails, so the result is this:
, name1@domain.com, , , name2@domain.com,
How do I fix this? (Remove the redundant commas; bypass the empty cells; other solution.)
Source: (StackOverflow)
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 sheets to excel it said sort formula is invalid. And I notice arrays work a bit different in excel. So how can I convert this formula from google sheets to excel without losing function.
Source: (StackOverflow)
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 with US addresses, but answers pertaining to other countries are interesting, too.
UPDATE: I guess I'm mostly hoping that there's a way to do this in Google Spreadsheets. I realize that you need to access a vast ZIP code database to do this, but it seems to me that such a database is already inside Google Maps. If I put an address in there without ZIP code, I get back an address with ZIP code. If Maps can do that lookup, maybe there's a way to make it happen in Spreadsheets, too.
Source: (StackOverflow)
I have a bunch of rows with a date and a dollar amount (expenses).
I want to produce a list of the days of the month and what the balance of the expenses is. So, for example the 5th entry in the list would be 8/5/2008 and the sum of all the expenses that occurred on or before 8/5/2008. Approximately this is =sumif(D4:D30-A5,">0",E4:E30) but of course that doesn't work (where the source data is dates in D4:D30 and the expenses are in E4:E30).
Notes
- source data can't be sorted for various reasons.
- must work in google spreadsheets, which is a fairly complete subset of excel's functions.
Source: (StackOverflow)
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 articles and books I'm reading. Say I'm reading "I, Claudius". I want to give it these tags: history, fiction, biography, disability, politics, drama. Then, if I want to display all the articles/books tagged with "politics", I can maybe search/display/pivot with that tag.
Maybe preferably, the tags should all be in one cell with each word separated by a comma. If each word were in a cell of its own, it would make the table really messy, I think.
I am open to other labelling solutions too.
Thanks!
Source: (StackOverflow)
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, they might read:
Hi,
My friend example@example.com.
Thanks,
Example Joe
OR
You should ask
example@example.co.uk
I've currently got this formula:
=IFERROR((LEFT(CELL,FIND("@",CELL)-1))&"@"&(regexextract(CELL,"@(.*)")))
It works for almost all cases, except
a) as in example 1, where someone puts a , or . at the end of the email
b) as in example 2, where the email starts on a new line, it comes in as eg.
ask
example@example.com
How might I adapt the formula to correct these?
Source: (StackOverflow)
I've always been frustrated when using spreadsheets by the inability to define my own functions when there is no existing function to (cleanly) do what I want. Does any spreadsheet offer this ability?
Source: (StackOverflow)
In particular, tabular data that can be graphed. What's your favorite source of interesting data?
Source: (StackOverflow)
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 within a few seconds I will often see the Trying to connect dialog. I'm aware of it happening across Google Sheets, Docs, and Drawings. I don't see the same problem when I switch to Chrome. I believe my network connection to be robust.
A reload of the page does not fix it. Clearing my browsing history will usually fix it for a day or two. Sometimes it goes away by itself. I'm typically logged into two or more google accounts when using Google Docs. I've noticed the problem on docs for Google Apps for Business, I haven't noticed it for my personal account, but that could just be because I use my personal account a lot less for docs. I haven't noticed any logging in Console.app when it happens.
Safari 8.0.2
OS X 10.10.1
Source: (StackOverflow)
I'm struggling with the best way to chart a series of times in Google Spreadsheets that involves a midnight crossover. The data in question is a graph of a bedtime, when the time to bed is after midnight the scaling of the chart becomes so great that important details and fluctuations in time are hard to see. Google Spreadsheets doesn't seem to support AM/PM format times natively in the y-axis, thus the use of 24-hour time.
Ideally, I would like to have the y-axis range from 6pm to 6am. Any thoughts or suggestions on how to do this?
Source: (StackOverflow)
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 that row.
Name 1 2 3 4
______________
John A A P A
Mary P P P A
Suzy P P P P
Dick A A A A
So I need red cell to Dick...
How to count the "P" symbols from a given row?
PS. Some restrictions:
I need the whole line starting with, say the second column "to the end", like B2:B?
Is there a way to have an universal formula? say =COUNTIF(B2:B, "P") > 0 to became =COUNTIF(B+ROW():B, "P") > 0
Source: (StackOverflow)
I have been looking everywhere for a few weeks now but can't seem to find anything so I come here to ask for assistance. Right now I am doing everything via a very simple Google Docs spreadsheet.
I need either a Linux-based program, LAMP-based script, or something that might work in google docs or excel. I plan on running it in a virtual machine.
Here are the details:
- There are 6 employees
- The work week is 24 hours a day, 7 days a week.
- Schedule will change like every 3 months, so don't really need anything complex.
- Only one person works at a time so there will never be more than 1 person on any shift.
- There is no standard shift length, like there is no 9-5... its just rotates around everyone's availability.
I need to be able to:
- List which days an employee can't work.
- List Min-Max amount of hours an employee can have per week and/or per day.
- Randomly assign employee shifts based on above requirements.
I don't need anything complex like don't need to keep track of days off, don't need to keep track of vacations, employees don't need to log into the system, I really don't need anything about the employee in the system except for their name and the information above needed to assign their shift. However if any of that is included with the software or script then it is no biggie. I can deal with something bloated as long as I am able to do the above lol.
Source: (StackOverflow)