All Questions
350
questions
33
votes
4
answers
5k
views
AND function gives TRUE result for empty, BLANK cell - why?
I have blank B2 cell.
=ISBLANK(B2) gives TRUE
A simple logical test on it returns FALSE
=IF(B2,TRUE,FALSE) gives FALSE
Yet when directly used in an AND statement, it returns TRUE
=AND(B2,TRUE) ...
- 333
9
votes
5
answers
12k
views
Combining first two letters from first name and first two letters from last name
I have a spreadsheet of usernames.
The first and last names are in the same cell of column A.
Is there a formula that will concatenate the first two letters of the first name (first word) and the ...
- 93
9
votes
3
answers
35k
views
Excel TEXT function does not work properly
Let say I have the following values in the cells
A1 : `=Now()` which displays 17.03.2014 13:45
A2 : 12.03.2014
Both are in date format and I have the function in another cells like this:
B1 : `=...
- 753
8
votes
5
answers
276k
views
Excel Formula To Get First Non-Zero Value in Row and Return Column Header
I am attempting to find a formula where I can look at a column range within a row and return the column header in that row where the first non-zero value occurs, moving left to right.
Below is a ...
- 376
8
votes
7
answers
19k
views
How to combine a range and cell into a new range within a formula in Excel
I have a formula in Excel that takes an array/range of values as one of the input.
I have a range of values, let's say A1:A6, and a single cell C11. I want to combine the range with the single cell ...
- 255
7
votes
4
answers
25k
views
Average numbers if another column contains a certain substring text in Excel
I'm looking for a way to average the numerical numbers in one column, say B, only if the string in column A contains a certain text.
For example:
(In column A) (In column B)
Hello table . . . 1
...
- 71
7
votes
3
answers
19k
views
How do I download a stock price in Excel 2013? Is there an equivalent to Google Doc's GoogleFinance spreadsheet function?
In a Google Docs spreadsheet, I can use this cell formula:
=GoogleFinance("GOOG", "price")
to download the latest price of a stock. Is there an equivalent function in Excel 2013?
Earlier versions ...
- 777
6
votes
3
answers
2k
views
How to find and replace the character “*” in excel text values in cell, but NOT formulas
Very similar to this question:
How to find and replace the character "*" in Excel
But I need to leave formulas untouched.
I've got about 50+ sheets that have two types of cells with "*"
...
5
votes
2
answers
26k
views
How to interpolate intermediate values for arbitrary data in Excel
I have data tables like this example, nine entries at A1:B9 in this case:
A B
-- ---
1 2.9
2 5.06
3 7
4 8.84
5 10.87
6 13.24
7 16.22
8 20.25
9 36.7
The above ...
- 69
5
votes
3
answers
593
views
Excel: Get range address from single input (looking for compact formula)
I'm trying to get an absolute range address from single input. Something like this.
Formula
=SOME.MAGIC(A1:B10)
Output (string)
"$A$1:$B$10"
Attempt 1
I'm able to get this result but using ...
- 885
5
votes
1
answer
9k
views
How to Index-Match upwards from a specified cell until non-blank
I've got data in sort of a directory tree layout (See picture)
Basically I want to use one lookup code as a reference point and have excel look upwards in the respective columns to display the "...
- 53
4
votes
2
answers
4k
views
Why is this Excel formula returning two different values?
I am attempting to use Excel (2013) to look up whether the value 1 turns up in a given set (using the CHOOSE() function, then, if so, printing the value in the cell directly to the right of it using ...
- 41
4
votes
2
answers
524
views
Converting bar-codes to specific format using IF, FIND & MID function of excel
In Excel I have a number of columns containing characters of different types such as:
WS-S5-S-L1-C31-F-U5-S9-P14
WS-S5-S-L1-C31-F-U5-S8-P1
WS-S5-N-L1-C29-V-U16-S6-P6
I want to convert these to 8 ...
- 43
4
votes
2
answers
3k
views
Bizarre COUNTIF/COUNTA behavior in excel for counting text cells that are not ""
I have a column of cells filled with formula that give "" on error, and a text string if no error. In a second column I'm giving a formula with:
=COUNTIF(range,"<>")
as well as:
=COUNTA(range)...
4
votes
1
answer
32k
views
How can I get the number of rows in an Excel pivot table for use outside the pivot table?
I have a pivot table in Excel of the form:
Country Server Status
Australia AU1 1
AU2 1
AU3 4
New Zealand NZ1 4
NZ2 ...
- 443