Questions tagged [worksheet-function]
Questions about using in-cell functions/formulas in spreadsheet software.
452
questions
7
votes
3
answers
42k
views
Split Excel Cell by last Delimiter
I need to split a cell by the first delimter (reading right to left) so that I can put that value in another cell.
For Example, my data in cell A1 could look like:
"something - more something - a ...
- 175
6
votes
2
answers
9k
views
Conditional concatenate cell content across rows
I have a worksheet with thousands of rows which contain a unique ID A, and one 3 different error codes M. Each individual ID may have 1, 2 or 3 errors. They will always be in the same order.
I have ...
- 165
5
votes
2
answers
16k
views
Excel row-wise multiplication
I would like to multiply, row-by-row, two columns in an Excel sheet (in order to take the median of the resulting column).
1 3
2 5
4 2
Should become
3
10
8
How can I do that in a formula (i.e. ...
- 747
5
votes
2
answers
73k
views
Conditionally format cells based on match in another sheet
I have an Excel spreadsheet with 2 worksheets. The first is just a header row and a single column of item names. The second is a list of item groups, with a header row and a title in the left most ...
- 153
5
votes
3
answers
32k
views
Excel function working like SQL group by + count(distinct *)?
Suppose I have an Excel sheet with below data
CODE (COL A) | VALUE (COL B)
==============================
A01 | 10
A01 | 20
A01 | 30
A01 | 10
B01 | ...
- 173
4
votes
1
answer
393
views
According to Excel, 4^3^2 = (4^3)^2. Is this really the standard mathematical convention for the order of exponentiation?
According to Excel,
4^3^2 = 4096
However, according to what I learnt at school, first I have to compute 3^2 = 9 and then 4^9 = 262144.
Who is right, Excel of my former teacher?
- 2,107
4
votes
2
answers
6k
views
Counting occurences of a character in an excel spreadsheet
Feel silly asking this question, but I need some help!
I want to count how many * appear in a range of 5 cells.
The cells will range from 0-7 * each.
I was thinking of using the =COUNTIF(A2:A:6,"*")...
- 179
3
votes
2
answers
14k
views
Deleting specific rows/columns from excel
I want to delete specific rows from Microsoft excel. My data set has around 900,000 rows, I want to get ride of rows/columns that are for example, from b325890-b500000 without dragging my mouse down ...
3
votes
2
answers
22k
views
Excel to count number of concurrent sessions based on start/end times
I have a massive set of data that I'm trying to work through. In Column A, I have a username, in Column B I have a session start date/time, in Column C I have the session end date/time.
I am trying ...
- 33
3
votes
1
answer
19k
views
Excel Large Function with Duplicates
I'm trying to apply a LARGE function to get the 5 largest values from a column ignoring duplicates. I did some searching around and found some proposed solutions but they don't seem to work for me. In ...
- 518
3
votes
1
answer
528
views
How to return in a cell all the matching values present in a column?
Suppose I have the following data:
A
B
1
idx1
VAL1
2
idx2
VAL2
3
idx3
VAL3
4
idx4
VAL1
5
idx5
VAL4
6
idx6
VAL1
7
idx7
VAL1
8
idx8
VAL2
9
idx9
VAL5
10
idx10
VAL3
For each "VAL[n]" ...
- 177
3
votes
1
answer
2k
views
Formula to create a cartesian product from two data sets in a spilled range
I'm after a formula that will create a cartesian product from two sets of data where either set of data could grow or shrink.
The answer given by @ScottCraner in this post almost gave me the answer, ...
3
votes
3
answers
13k
views
Multiplying values in Excel 2010 (Same cell)
Another excel question. I'm using Excel 2010.
If for example I have a value in cell A1, lets say 2.59
What I want to do is multiply that cell by 1.333 but in the same cell
I don't want to have to ...
- 137
2
votes
3
answers
35k
views
Lookups targeting merged cells - only returning value for first row
I have a master worksheet which contains data that I wish to link to another 'summary' sheet using a lookup.
However, some of the cells whose data I wish to include in the summary sheet are merged ...
- 29
2
votes
1
answer
14k
views
How to copy a column with conditional format by "value" - Libreoffice 4.1
I have a column that contains the data and a set of rules for the conditional format of the cells.
i.e. the conditional format changes the background colour of some cells if they are negative.
I ...
- 19.1k