All Questions
Tagged with microsoft-excel worksheet-function
7,317
questions
125
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,832
104
votes
9
answers
866k
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,202
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
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
465k
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
204k
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
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,034
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