All Questions
Tagged with microsoft-excel worksheet-function
7,327
questions
12
votes
9
answers
171k
views
How to make Excel 2010 auto-fill new row with formulas?
I can swear that I have seen that behaviour where Excel automatically fills in a newly inserted row with the same formula as on the row above.
I know this can be done with some VBA, but if possible I'...
11
votes
4
answers
8k
views
Spurious 0.0000000000001 added to formula result
My wife and I have a spreadsheet to work out our finances at the beginning of each month and this month has a strange figure in cell F9.
The formula in cell F9 is ="+ £"&-'Sue''s ...
11
votes
3
answers
3k
views
One-digit and two-digit separation
I have a set of values some of which are one digit (e.g, 5), and some which are two digits (e.g, 55) in cells.
For the calculation I want to make, I want to use the first digit of the values. I don'...
11
votes
8
answers
66k
views
Generate a comma-separated list of cell contents, excluding blanks
To concatenate a certain four cells, I'd use:
=CONCATENATE(A2,",",C2",",D2,",",F2)
This would make it so that...
A2 = "Matthew"
C2 = "Mark"
D2 = "Luke"
F2 = "John"
would result in Matthew,Mark,...
11
votes
5
answers
65k
views
How to separate number by its decimal point in Excel/Calc?
I've got a number in a spreadsheet like so: 28.686279
When I open this sheet in either LibreOffice Calc or Microsoft Excel, is there a formula function that returns the "whole number", I.e. 28. Also, ...
11
votes
1
answer
43k
views
Shortest Method To Reference A Single Row/Column of a Named Range in Excel?
I have a named range (NamedRange1) and I need to reference the first row and first column of that range within a formula. I came up with the following:
First row:
INDIRECT("R" & ROW(...
11
votes
7
answers
34k
views
Using excel, how can I count the number of cells in a column containing the text "true" or "false"?
I have a spreadsheet that has a column of cells where each cell contains a single word. I would like to count the occurrences of some words. I can use the COUNTIF function for most words, but if the ...
11
votes
4
answers
99k
views
A blank cell not registering as zero in excel
I am running a complex IF equation in Excel 2003 (yes, the computers at work are that old too) and it is important to the equation that blank cells are valued at zero, which is, I understand, how ...
11
votes
5
answers
214k
views
How to combine values from multiple rows into a single row in Excel?
I have a data dump in Excel that consists of annual customer data for two different values. The data was provided with a separate row for value for every year and customer. I.e. it looks like this:
...
10
votes
6
answers
70k
views
Calculate number of Sundays in the month
In Microsoft Excel I am looking to determine the number of Sundays in any given month. I am putting together a budget spreadsheet and some of the sub accounts get extra funding when five Sundays are ...
10
votes
4
answers
74k
views
How can I get the displayed value of a cell in MS Excel ( for text that was converted to dates)?
How can I get the displayed value of a cell in MS Excel ( for text that was converted to dates)?
Excel File Origin
I have an Excel file that was originally a CSV file. At this time I don't have ...
10
votes
4
answers
189k
views
When I copy table data into excel, it ONLY pastes it into column A! How to fix this?
If I use the following link:
http://web.utk.edu/~dhouston/excel/exam1.pdf
to copy the table and paste it into excel, it will only paste the values into one long column in Column A (and each row has ...
10
votes
2
answers
231k
views
What is the difference between function and formula in Excel?
What is the difference between those two when speaking of Microsoft Excel?
10
votes
2
answers
759
views
Convert the multi-step sum of booleans into a single formula
I have a few sensor readings and I'm checking to see if the values are in the acceptable range.
To do this I use an IF to check if the values are > or < the calculated (average) value. The ...
10
votes
5
answers
93k
views
Using find() in Excel to remove text from string
I'm using find to remove some of the test string in lookup cell value
For instance I look-up the following in cell A2 Application: sales_rep_support and use find =RIGHT(a2,FIND(" ",a2)+4) to change ...