formulas interview questions
Top formulas frequently asked interview questions
Any recipes out there for a "smart" number formatting formula for Google Sheets that's "scale-aware," a la Rails ActionView's distance_of_time_in_words method?
I would expect to enter a number like: 1,816,724 and see 1.8M (or enter 2,394 and see 2.4K).
Source: (StackOverflow)
I'm trying to find a good tool for teaching someone maths online. Some kind of a online whiteboard that the student can be invited to.
I need to be able to draw graphs and handwrite using my drawing tablet.
I also need to be able to type text and formulae.
Something with similar features to the Windows application iDroo would be great!
What online tool would be most suitable?
Source: (StackOverflow)
I'm having a bit of a struggle. I'm creating a spreadsheet which uses plus signs (+) regularly. I want a semi-permanent fix for +'s turning into addition formulae. See this YouTube video for a demonstration.
Source: (StackOverflow)
Is there a function that will tell if a specific cell has a plain (manually entered) value or has a formula?
Source: (StackOverflow)
I currently have a spreadsheet to keep track of scores in a card game. There can be between 2 and 5 players. I have the following:
| Players |
|----------|
| Dave |
| Paul |
| John |
| |
| |
At the moment I use
= JOIN( " vs " ; C10:C14 )
But the problem is I then end up with "Dave vs Paul vs John vs vs". Is there a way to make it say "Dave vs Paul vs John", but if I had more players, Dave vs Paul vs John vs Rob with one formula?
Thanks
Source: (StackOverflow)
Which Google Spreadsheets formula do I use to get the domain part from an email address?
Example: bob@example.com → example.com
Source: (StackOverflow)
Is there any way I can check whether a cell is empty?
There is a function ISBLANK() but it returns false when there's a formula in a cell even thought the formula doesn't return anything (or an empty string). I would like it to return true when the formula doesn't return anything as well. So the cell seems to be empty without any value.
Source: (StackOverflow)
How do you do an IF ... OR ... THEN statement in a Google spreadsheet? In PHP I would do this:
if($C7 == "U.S.A." || $C7 == "Canada"){ ...
How to do the same as a spreadsheet formula?
At the moment I have
= IF ( C7 = "U.S.A." , "The Americas" , "Europe" )
I'd like to say if C7 = "U.S.A." OR "Canada"...
NB: I should have been able to (and tried extensively to) Google this, but the search engine ignores the words IF and OR
Source: (StackOverflow)
I did some searching on this and came across this post.
However, this solution uses an onEdit() trigger. I would like to see a solution that uses a formula approach, so in the OP's example from that post, the formula would be like so:
=setcolor( if(A1=1,true,false), RGB(255,255,0) )
How can I create a formula that does this?
Source: (StackOverflow)
I have a spreadsheet of product id codes that I'd like to turn into links, and from everything I've read, the concat or concatenate functions in Google Docs should work for that, but so far everything I try results in an error.
What I want to get:
Brand Product Code URL
AP X527W http://example.com/search.php?Code=X527W
AP X522M http://example.com/search.php?Code=X527W
AP Y500M http://example.com/search.php?Code=X527W
What actually happens, when I use the formula =concat('http://example.com/search.php?Code=', B2)
or =concatenate('http://example.com/search.php?Code=', B2)
or ='http://example.com/search.php?Code=' & B2
is
#Error
So how do I concatenate strings in Google Docs?
Source: (StackOverflow)
Reading the documentation for the =QUERY function1, 2, 3, some of it seems to imply that I should be able to use column headers directly in my query. For example, the =QUERY function takes a third optional parameter, HEADERS, which lets you specify a number of header rows.
Most of my queries would be a lot prettier if I could use column headers, thus not having to use column indexes, but I'm not able to make it work.
Example:
A B C
---------------------
Name Phone City
Vidar 12345678 Oslo
Rupert 32165487 Berlin
I'm able to query this by using column indexes:
=QUERY(Sheet1!A1:C; "select A, B, C where A = 'Vidar'"; 1)
... but not using column headers:
=QUERY(Sheet1!A1:C; "select A, B, C where Name = 'Vidar'"; 1)
... this gives me Error: Invalid query: Column [Name] does not exist in table
Is it at all possible to use column headers like this? If not, what is the purpose of the HÈADERS parameter?
Source: (StackOverflow)
I have the following IF condition in some cell:
=IF(A1>A2, "value is <C5>", "value is <D5>")
where <C5> and <D5> should be the values of cells C5 and D5.
I know it is a simple question but searching was not successful. I don't really know how to phrase this.
Source: (StackOverflow)
We have applicants who come in and fill out a form via Google Forms. I have fields that auto-populate from the Response tab into a separate tab that is organized to fit our trackers. I am looking for a string to use with `ImportRange' that would add conditions to what is transferred.
For example:
Book 1
Applicant Form Response
A B C D E F G
1 Jon Doe xxx-xxx-xxxx xx/xx/xxxx Detroit Michigan Pass
2 Jon Doe xxx-xxx-xxxx xx/xx/xxxx Detroit Michigan Fail
3 Jon Doe xxx-xxx-xxxx xx/xx/xxxx Detroit Michigan Pass
4 Jon Doe xxx-xxx-xxxx xx/xx/xxxx Detroit Michigan Pass
Book 2
Passed Applicants
A B C D E F G
1 Jon Doe xxx-xxx-xxxx xx/xx/xxxx Detroit Michigan Pass
2 Jon Doe xxx-xxx-xxxx xx/xx/xxxx Detroit Michigan Pass
3 Jon Doe xxx-xxx-xxxx xx/xx/xxxx Detroit Michigan Pass
I want to be able to take the info from this sheet(Applicant Form Response) in book 1 A:G and auto fill into a different book (Passed Applicants) only if G = Pass.
I understand the range formula =IMPORTRANGE("Spread_sheet_key", "Applicant Form Response!A:G")
and my Query function would be (if within the same workbook) =Query('Applicant Form Response'!A:G, Select * where "G contains 'Pass'") or something.
With this I am able to transfer all the data from one book to another. As stated above I only want the data of those who passed to transfer.
What other formula would be best to use and how do I nest it within the 'ImportRange' formula?
Source: (StackOverflow)
I have a set of data in one Sheet tab, and a counter on another tab in the same document.
I want the counter (in sheet 2) to read through the text in the table in sheet 1, and update how many times one name has occurred, counting only one per line. How would I do this?
I want it to end looking like in sample spreadsheet. Same example here: input data
name1 name2 name3 name4
name3 name5 name2 name2
name1 name2 name3 name3
name4 name1 name4 name2
name5 name3 name5 name1
name2 name4 name1 name5
Output (desired result) is:
name1 name2 name3 name4 name5
5 5 4 3 3
Source: (StackOverflow)
I couldn't find it easily in their FAQ: When does your link karma increase 1 point?
I don't think it's a one-one relationship with votes, as some of my stuff got upvoted and it didn't change immediately
What about comment karma? Is it the same formula but for votes on comments?
Source: (StackOverflow)