Questions tagged [worksheet-function]
Questions about using in-cell functions/formulas in spreadsheet software.
8,196
questions
124
votes
14
answers
295k
views
Format a number with optional decimal places in Excel
I have numbers in cells in Excel. I want the numbers formatted so that if they have decimal places they show to a maximum of two, and if they have no decimal places it doesn't show any.
For example.
...
- 1,822
120
votes
10
answers
483k
views
Type math formulas in Microsoft Word the LaTeX way?
I wonder if there is some free solutions for typing math formulas the LaTeX way in Microsoft Word document (Office 2007)?
- 17.3k
102
votes
9
answers
863k
views
In Excel, how do I check if a cell is in a list of values (a range of cells)
I've got a range (A3:A10) that contains names, and I'd like to check if the contents of another cell (D1) matches one of the names in my list.
I've named the range A3:A10 'some_names', and I'd like ...
- 2,162
96
votes
14
answers
20k
views
For x=3 in Excel, why does = - x^2 + x result in 12 instead of -6?
Suppose my cell A1 in an Excel spreadsheet holds the number 3.
If I enter the formula
= - A1^2 + A1
in A2, then A2 shows the number 12, when it should show -6 (or -9+3)
Why is that? How can I ...
- 2,107
81
votes
9
answers
328k
views
How to create a formula for every row in a column in Google SpreadSheet?
I use three columns. A, B and C.
In column C I have a formula every row =A1*(1.6*B1) and then for the next row I have =A2*(1.6*B2) in C2.
How can I do so I don't have to type in the new formula in ...
- 27.8k
71
votes
4
answers
162k
views
Is it possible to sum an entire column without setting explicit cell boundaries in Excel?
I'd like to sum all the values in a column starting from a certain minimum value, but I don't want to give an upper bound because any value in the table will be appropriate for summing (minus the ...
- 1,513
69
votes
2
answers
464k
views
What is the Excel hotkey to re-calculate all formula in sheet?
I have a sheet that contains many formulas. But they do not recalculate automatically when I change input.
I'm looking for a hotkey, that can re-calculate the sheet. According to this page, F9 ...
- 817
61
votes
2
answers
8k
views
Excel - can I set a cell to equal a certain value, no matter what is typed in it?
This is to play a joke on someone...not sure if it's possible to do what I want. We are circulating an Excel file among friends to select players for a golf tournament. I would like to set a cell (...
- 601
60
votes
11
answers
168k
views
Formula for updated currency exchange rates in Excel or other spreadsheets?
What kind of spreadsheets support a formula like the following:
=exchangeCurrency("USD"; "EUR"; 3000)
The result should be 3000 USD exchanged into Euros. Possibly a date could be provided for a ...
- 1,279
59
votes
4
answers
219k
views
In Excel how can I sum all the numbers above the current cell?
I want to have a column in Excel that consists of a header, a bunch of numbers, and then have the sum of those numbers at the bottom. I'd like the sum to adapt to the insertion of new numbers above ...
- 1,751
54
votes
8
answers
203k
views
Excel function that evaluates a string as if it were a formula?
Suppose I have a text string like "11+5" or even "=11+5" stored in a cell. Is there a function in Excel that will allow me to actually evaluate that string as if it were a formula?
This would be ...
- 984
52
votes
2
answers
315k
views
Create a pie chart from distinct values in one column by grouping data in Excel
I have an Excel document containing information from a survey. I want to create a pie chart over location (countries).
How can I make Excel group all the distinct values together and then display ...
- 981
49
votes
10
answers
286k
views
Is there an Excel function to create a hash value?
I'm working with a number of data lists that are keyed by document name. The document names, while very descriptive, are quite cumbersome if I need to view them on (up to 256 bytes is a lot of real ...
- 1,769
47
votes
11
answers
723k
views
How to compare two columns and find differences in Excel?
I have two columns in Excel that I want to compare and find the differences between them.
Suppose:
Col A has 50 numbers, i.e. 0511234567
Col B has 100 numbers in the same format
- 587
46
votes
5
answers
11k
views
Is there a concise Excel formula to calculate (A1*A6)+(B1*B6)+(C1*C6)...?
I want to know how to give a concise formula for the following long formula in Excel sheet:
= (A1*A6)+(B1*B6)+(C1*C6)...
Do I use a sum function or is there another function for this? I think using ...
- 655
46
votes
3
answers
69k
views
How to simulate a full outer join in Excel?
Let's assume I have some data in Excel (and not in a real database). In one sheet, I have data, where one column functions as the ID, and I have made sure that the values in this column are unique. In ...
- 4,024
45
votes
2
answers
36k
views
What does "A1:A4^{1,2,3}" mean in an Excel formula?
What does the caret operand in Excel do to a range when it is followed by some other numbers in brackets? For example lets say we have the following table:
A B C
1 1.5 0 0
2 -0....
- 555
41
votes
8
answers
227k
views
How to count number of distinct values in a range?
I've got a large table that is already organized using filters etc. I'd like to add a summary underneath certain columns that contain the number of distinct values in that column.
There's no ...
- 18.4k
40
votes
17
answers
396k
views
How to get the current column name in Excel?
What is the function to get the current line number and the current column name for a cell in Excel?
33
votes
9
answers
15k
views
Limit Excel CPU Usage
Is there a way to limit how much CPU usage excel has access to when running? I have a VBA script that calculates a bunch of giant array formulas. The entire calculation takes maybe 20 minutes using ...
- 518
33
votes
10
answers
158k
views
How do I get the day name into a cell in Excel?
Given a date, how do I get the day of the week (e.g. "Monday") into a cell in Excel?
- 435
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
31
votes
4
answers
78k
views
How do I hide the #DIV/0! error while a referenced cell is blank?
In Column C I have Production. In column D I have Goal. In Column E I have variance %. My formula is =(D11-C11)/D11
However, how do you hide the cells down the sheet until you put something in D11 &...
- 311
27
votes
2
answers
67k
views
I need to find the last Space in a XLS Cell
I have an Excel sheet with text values, e.g. in A1: "This is the text". I need to find the position of the last space character. Is there an excel formula that will give me the result I want? I have ...
- 602
27
votes
3
answers
40k
views
In Excel, can I provide a "default value" in case my formula returns #NA?
For example,
Say the value of a cell is:
IF(ISNA(VLOOKUP(A3,somesheet!G:J,3,FALSE) * VLOOKUP(A3,someothersheet!A:D,4,FALSE)), 0, VLOOKUP(A3,somesheet!G:J,3,FALSE) * VLOOKUP(A3,someothersheet!A:D,4,...
- 1,104
27
votes
1
answer
124k
views
How do I put logical operators in an Excel =IF Formula?
I'm trying to enter a formula to display text according to an IF condition. The best I can manage is something like...
=IF(myval>=minval & myval <= maxval, "OK", "Not OK")
But this appears ...
- 1,955
26
votes
4
answers
208k
views
How to search for newline or linebreak characters in Excel?
I've imported some data into Excel (from a text file) and it contains some sort of newline characters. It looks like this initially:
If I hit F2 (to edit) then Enter (to save changes) on each of the ...
- 3,061
26
votes
4
answers
107k
views
Excel formula to convert boolean values into {0, 1}
What is the Excel formula to convert boolean values {FALSE, TRUE} into {0, 1}?
Supposing there is one shorter than =IF(cond,1,0).
- 1,771
25
votes
5
answers
82k
views
Can you do Regular Expressions in Excel without VBScript?
I'm looking for an Excel function that I can put in a cell to do a regex search of the contents of another cell. Is there anything available to do this? I'd prefer not to have to add VB Script to ...
- 758
23
votes
5
answers
157k
views
How to get time difference as minutes in Excel?
I have time values in two cells in an Excel sheet and I need the difference of
the times in minutes.
The cells in the Excel are like below:
A B C
1 6:38 8:23 1:45:00
A is ...
- 365
21
votes
4
answers
164k
views
Dynamically reference a Named Table Column (via cell content) in Excel
How do I reference an Excel table column dynamically in Excel 2007?
I want to reference a named column of a named table
and the referenced column will vary with the value of a cell.
I have a table in ...
- 321
21
votes
1
answer
88k
views
What would be the the mathematical equivalent of this excel formula? =PMT()
I need to create a JavaScript form that does the same calculation as this =PMT() function.
mortgage = 220000
rate= 4.75%
term = 30
The example formula I have is =PMT(4.75%/12,30*12,220000*-1)
What ...
- 313
21
votes
6
answers
59k
views
Sum contents in column from starting cell on down without setting an explicit last cell index
I have an Excel spreadsheet and would like to sum the numbers in a column starting at one cell (say, B5) and continuing down to the last number in that column.
I know I could just enter a large ending ...
- 699
20
votes
5
answers
18k
views
How do I get the =LEFT function in excel, to also take the number zero as the first number?
My data contains a column of room numbers. Each room number's first 2 digits is the floor number where the room is located. The =LEFT function works well for data that does not start with zero. The =...
- 201
20
votes
6
answers
108k
views
Check if Excel cell text is numeric using formula only
I have to find out if my cells text is a numeric value and wanted to use an elegant non VBA method that doesn't impede on its current state or value.
What I've found is that the ISNUMBER() function ...
- 523
19
votes
10
answers
194k
views
How to dynamically show the current time (and not date) in Excel?
I want to fill a cell with the current time, so it changes every time I open the file.
I currently use the NOW() function for that, but it returns both the date and the time.
I want to see only the ...
- 924
18
votes
7
answers
123k
views
Microsoft-excel newlines and tab
Current I use "CONCATENATE" to join string in Excel, but I want insert character newlines and tab in string joined by "CONCATENATE".
Example:
I've string below when I completed join string.
"Today ...
- 181
18
votes
3
answers
58k
views
How do I create a data validation list WITHOUT using cells?
I have a simple data validation dropdown that I'm trying to do, but I DO NOT WANT to use cells to populate my list.
Something similar to what's below, but this obviously isn't working.
What ...
- 26.4k
18
votes
1
answer
38k
views
Shortcut to accept suggested formula in Excel
I'm working with a lot of formulas in Excel 2007 and I would like to know what shortcut key you need to press to accept the suggested formula.
When I type in =DEG the first suggested formula is =...
- 275
18
votes
9
answers
74k
views
Excel function determine if a cell is visible
In conditional formatting I want to detect if the row above the current cell is hidden or visible. How can I detect if a cell is visible or not?
The only hack I can think of is:
Create a column with ...
- 1,030
17
votes
3
answers
73k
views
Fill a large range with a formula in Excel, without mouse-dragging to extend
I have 5000 elements in column A and 5000 in column B
I want the element wise difference and stored in column C.
The subtraction command I know is =A1-B1 then drag all the way down with a mouse.
...
- 275
17
votes
9
answers
195k
views
Change year in many date fields in excel?
So I've noticed in a worksheet I have many dates that are four years off, no doubt because of improper copy/paste from earlier question.
Is there an easy way to convert a bunch of dates I select to a ...
- 277
16
votes
10
answers
117k
views
Is there a function in Excel to find the maximum absolute value of a range?
I'm looking for a function in Excel that looks something like
= MAX(ABS(A1:A10))
except ABS() doesn't take a range of numbers.
The best that I can come up with is:
= MAX(ABS(MIN(A1:A10)),ABS(MAX(...
- 327
16
votes
4
answers
169k
views
How do I reference an Excel sheet name in an Excel formula? [closed]
How do I reference an Excel sheet name in an Excel formula?
- 179
16
votes
4
answers
133k
views
Excel-Overcoming #VALUE error with FIND function
In cell A1 I have entered "Apple". In B2 I enter the formula =FIND("Apple",A:A). However I keep getting #VALUE error. Can anyone explain this and how do I overcome it?
- 469
16
votes
7
answers
62k
views
Custom Formula Not Updating
I followed this guide to create the following custom function which counts the number of cells of a certain colour in my spreadsheet:
Function ColorFunction(rColor As Range, rRange As Range, Optional ...
- 163
16
votes
1
answer
7k
views
Reference Excel sheet with apostrophe in name
I have a large workbook with company names for each sheet. On some of the data sheets, I need to reference the other sheets. In doing so, I would start with an =sum( in the formula bar, then click on ...
- 24.4k
16
votes
2
answers
4k
views
In LibreOffice Calc is there a hotkey to edit the current cell?
I am wanting to edit a column of cells (for example, copy/pasted values have $0.005/hr and I want them to just be 0.005)
I can navigate with arrow keys, I can click to edit the existing value, but ...
- 863
16
votes
10
answers
206k
views
How do I make Excel recognise TRUE or FALSE text as boolean values?
I have been using the INT formula to convert boolean values TRUE and FALSE into 1s and 0s. However, I have copied some data I have been given into my spreadsheet and Excel hasn't recognised the text ...
- 405
15
votes
5
answers
125k
views
Best way to remove leading zeros from a non-numeric value in Excel
I have many cells in an Excel sheet, using 9 characters of 0-9 and A-Z, that have some number of prefixed zeros:
000000123
000001DA2
0000009Q5
0000L210A
0000014A0
0000A5500
00K002200
I'd like to ...
- 956