Questions tagged [worksheet-function]

Questions about using in-cell functions/formulas in spreadsheet software.

A worksheet function is a function or formula that is used within the spreadsheet of a spreadsheet program like or . The function can be a user-defined function via or a program-ready function like =sum() or =vlookup(). These functions are used to perform simple or complex calculations with the data from the spreadsheet.

Examples of predefined functions for Excel may be found here.

In Excel further functions may be created at will. These are termed User Defined Functions or UDFs.

8420 questions
132
votes
14 answers

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. 15 should be formatted as 15, NOT 15.00 14.3453453…
Ray
  • 1,902
129
votes
10 answers

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)?
Tim
  • 17,871
117
votes
9 answers

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 an excel formula that will give me True/False or…
joseph.hainline
  • 2,392
97
votes
14 answers

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 prevent this misleading behaviour?
Rodolfo Oviedo
  • 2,160
81
votes
9 answers

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 column C for every row? I use it in both Google Docs…
Jonas
  • 28,940
71
votes
4 answers

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 header of course) and I'd like to do it the "right"…
bwerks
  • 1,523
70
votes
2 answers

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 calculates all sheets in all open workbooks, and Shift…
Davuz
  • 837
  • 1
  • 8
  • 9
61
votes
2 answers

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 (call it A1) to to show a certain name (call it Joe)…
Adam
  • 601
61
votes
4 answers

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 the total. Something like this: Numbers 1 2 5 10 18…
Mark Meuer
  • 1,891
  • 3
  • 14
  • 13
60
votes
11 answers

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 historic exchange rate. Alternatively, the average for…
David
  • 1,289
55
votes
11 answers

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 estate) and I'd love to be able to create a smaller…
dwwilson66
  • 1,859
54
votes
8 answers

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 helpful for another project where I would like to be…
drapkin11
  • 1,024
53
votes
2 answers

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 them relative to each other? Say that there are 100…
Mestika
  • 991
  • 3
  • 11
  • 16
49
votes
3 answers

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 another sheet, I also have some data, again with…
rumtscho
  • 4,092
47
votes
11 answers

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
Sundhas
  • 587
  • 1
  • 5
  • 4
1
2 3
…
99 100