excel-formula interview questions
Top excel-formula frequently asked interview questions
Lets say I have one cell A1, which I want to keep constant in a calculation. For example, I want to calculate a value like this:
=(B1+4)/(A1)
How do I make it so that if I drag that cell to make a calculation across cells in many rows, only the B1 value changes, while A1 always references that cell, instead of going to A2, A3, etc.?
Source: (StackOverflow)
I have this table and I would like to create a formula which would count values based on true conditions from column A and column C.
Example:
If in column A value is M (male), and in column C is YES, then it would count.
could anyone help me with this formula?
Source: (StackOverflow)
Is there an efficient way to identify the last character/string match in a string using base functions? I.e. not the last character/string of the string, but the position of a character/string's last occurrence in a string. Search and find both work left-to-right so I can't think how to apply without lengthy recursive algorithm. And this solution now seems obsolete.
Source: (StackOverflow)
How do I remove leading or trailing spaces of all cells in an entire column?
The worksheet's conventional Find and Replace (aka Ctrl+H) dialog is not solving the problem.
Source: (StackOverflow)
I am trying to isolate the issue causing
"excel 4.0 function stored in defined names"
What I am unable to figure out is the reason behind this.
This issue started coming recently and unable to figure out why.
Source: (StackOverflow)
In a cell in Excel sheet I have a Date value like:
01/01/2010 14:30:00
I want to convert that Date to Text and also want the Text to look exactly like Date. So a Date value of 01/01/2010 14:30:00 should look like 01/01/2010 14:30:00 but internally it should be Text.
How can I do that in Excel?
Source: (StackOverflow)
I have an Excel spreadsheet containing a list of strings. Each string is made up of several words, but the number of words in each string is different.
Using built in Excel functions (no VBA), is there a way to isolate the last word in each string?
Examples:
Are you classified as human? -> human?
Negative, I am a meat popsicle -> popsicle
Aziz! Light! -> Light!
Source: (StackOverflow)
Is there an in-built function to check if a cell contains a given character/substring?
It would mean you can apply textual functions like Left/Right/Mid on a conditional basis without throwing errors when delimiting characters are absent.
Source: (StackOverflow)
If I select a cell containing a formula, I know I can drag the little box in the right-hand corner downwards to apply the formula to more cells of the column. Unfortunately, I need to do this for 300,000 rows!
Is there a short-cut, similar to ctrl+space, that will apply a formula to the entire column, or to a selected part of the column?
Source: (StackOverflow)
I have a column of values that often appear as duplicates. I need to create a new column, of unique values based on the first column, as follows:
Column A Column B
a a
a b
b c
c
c
This Column B will actually need to appear on a different sheet, within the same workbook, so I assume it will need to work with the sheet2!A1 style format.
I have not had any luck with the Data/Filter menu options as this only seems to work on command. I need column B to update automatically whenever a new value is entered into column A.
Source: (StackOverflow)
I'm trying to do conditional formatting so that the cell color will change if the value is different from the value in the cell left of it (each column is a month, in each row are the expenses on certain object. I want to monitor easily changes in prices over months.)
I can do it per cell and format-drag it, but I would like a general formula to apply to the whole worksheet.
Thanks!
Source: (StackOverflow)
How can I construct the following string in an Excel formula:
Maurice "The Rocket" Richard
If I'm using single quotes, it's trivial = "Maurice 'The Rocket' Richard" but what about double quotes?
Source: (StackOverflow)
I need to return an empty cell from an Excel formula, but it appears that Excel treats an empty string or a reference to an empty cell differently than a true empty cell. So essentially I need something like
=IF(some_condition,EMPTY(),some_value)
I tried to do things such as
=IF(some_condition,"",some_value)
and
=IF(some_condition,,some_value)
and assuming B1 is an empty cell
=IF(some_condition,B1,some_value)
but none of these appear to be true empty cells, I'm guessing because they are the result of a formula. Is there any way to populate a cell if and only if some condition is met and otherwise keep the cell truly empty?
EDIT: as recommended, I tried to return NA(), but for my purposes this did not work either. Is there a way to do this with VB?
EDIT: I am building a worksheet that pulls in data from other worksheets that is formatted to the very specific demands of an application that imports the data into a database. I do not have access to change the implementation of this application, and it fails if the value is "" instead of actually empty.
Source: (StackOverflow)
This seems like a simple Pivot Table to learn with. I would like to do a count of unique values for a particular value I'm grouping on.
For instance, I have this:
ABC 123
ABC 123
ABC 123
DEF 456
DEF 567
DEF 456
DEF 456
What I want is a pivot table that shows me this:
ABC 1
DEF 2
The simple pivot table that I create just gives me this (a count of how many rows):
ABC 3
DEF 4
But I want the number of unique values instead.
What I'm really trying to do is find out which values in the first column don't have the same value in the second column for all rows. In other words, "ABC" is "good", "DEF" is "bad"
I'm sure there is an easier way to do it but thought I'd give pivot table a try...
Source: (StackOverflow)
Does anyone have an Excel VBA function which can return the column letter(s) from a number?
For example, entering 100 should return CV.
Source: (StackOverflow)