All Questions
3,347
questions
94
votes
9
answers
437k
views
How to turn a string formula into a "real" formula?
I have 0,4*A1 in a cell (as a string). How can convert this "string formula" into a real formula and calculate its value, in another cell?
- 1,115
67
votes
3
answers
62k
views
Excel: Scalar product of two ranges
I am trying to find a proper way to calculate the scalar product of two ranges. For instance, the product of A1:A3 and B1:B3 would be A1*B1 + A2*B2 + A3*B3. Is there a good way to do this? Hardcoding ...
- 3,679
44
votes
2
answers
183k
views
Determine if a cell (value) is used in any formula [closed]
I am trying to reverse engineer an ancient program written using VBA in excel and formulas in cells.
There are many cells with values scattered across everywhere. Is there a fast way to check if a ...
- 11.4k
33
votes
5
answers
130k
views
index match returns 0 for blank cell, want it to be "-"
I have looked all over and tried a bunch of different things and non are working.
I can get the error to show - but I also want a blank cell to return -.
Right now blank cells are returning 0.
...
- 561
32
votes
3
answers
100k
views
Using relative positions in Excel formulas
How do I create a formula that isn't made invalid when I delete a row.
For example in cell F12 I have the formula: =F11+D12-E12
This basically says take the value from the cell above then add the ...
- 1,327
31
votes
5
answers
95k
views
Getting formula of another cell in target cell
How does one cell obtain the formula of another cell as text without using VBA? I can see this question has already been asked many times and the answer is always to write a custom function in VBA.
...
- 2,325
29
votes
6
answers
36k
views
Get formula from Excel cell with python xlrd
I have to port an algorithm from an Excel sheet to python code but I have to reverse engineer the algorithm from the Excel file.
The Excel sheet is quite complicated, it contains many cells in which ...
- 1,697
25
votes
8
answers
112k
views
Simulate string split function in Excel formula
I am trying to split a string in an excel formula, something like I can do in many programming languages, e.g.
string words = "some text".split(' ');
The problem is that I can't be sure that there ...
- 12.1k
22
votes
6
answers
71k
views
Count Unique values with a condition
In column A I have list of different names. In column B, I have values either 0 or 1.
I want to get a count of all the unique names from column A which have 1 in column B.
Using below array formula ...
- 2,192
20
votes
4
answers
342k
views
Fill formula down till last row in column
I'm trying to draw down the formula that's in cell M3 to the end of the data set.
I'm using column L as my base to determine the last cell with data. My formula is a concatenation of two cells with a ...
- 377
20
votes
7
answers
47k
views
HTML to Excel: How can tell Excel to treat columns as numbers?
I need to achieve the following when opening an HTML in Excel (Response.contentType="application/vnd.ms-excel") :
force Excel to consider content of td cells as numbers
make the above so that any ...
Wheelwright
20
votes
2
answers
78k
views
Replacing one character with another in a string
I have a data like below:
A:B:C:D
and I want to replace the C with data (say, Z) so that it may look like
A:B:Z:D
How can I do it?
- 201
17
votes
5
answers
80k
views
How to define a non continuous range in COUNTIF
A B C
1 Β Β
2 Β Β
3 Α Α
4 Α Α
5 Β Β
6 Α Α
7 Α B
8 Β Β
9 Β Β
10 Α Α
11 Β A
12 Α Α
13 Α Α
14 Β ...
- 30.3k
16
votes
6
answers
227k
views
Using OR & AND in COUNTIFS
I would like to include an "AND" condition for one of the conditions I have in my COUNTIFS clause.
Something like this:
=COUNTIFS(A1:A196;{"Yes"or "NO"};J1:J196;"Agree")
So, it should return the ...
- 925
15
votes
5
answers
68k
views
Excel - Conditional Formatting - Cell not blank and equals 0
I'm trying to check if a cell is:
blank, and if false (not blank)
If value equals 0. I have tried:
=IF( NOT( ISBLANK($D:$D) ) & $D:$D=0 ,TRUE,FALSE)
=IF( AND( NOT( ISBLANK($D:$D) ),$D:$D=0) ,...
- 4,260