Super User Stack Exchange
Super User Stack Exchange

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.

7920 questions
117
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…
Ray
  • 1,763
  • 4
  • 16
  • 19
116
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
  • 16,963
  • 69
  • 181
  • 260
97
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,052
  • 3
  • 16
  • 16
95
votes
12 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,018
  • 2
  • 8
  • 21
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
  • 26,874
  • 52
  • 105
  • 125
70
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,483
  • 3
  • 14
  • 21
68
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
  • 807
  • 1
  • 7
  • 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…
Adam
  • 601
  • 5
  • 4
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,279
  • 3
  • 16
  • 23
53
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 …
Mark Meuer
  • 1,621
  • 2
  • 13
  • 13
51
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
  • 971
  • 3
  • 11
  • 16
51
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
  • 954
  • 2
  • 8
  • 17
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
46
votes
5 answers

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 the colon with SUM function should help but I…
rukuto
  • 655
  • 1
  • 6
  • 8
45
votes
2 answers

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.5 0 0 3 4 0 0 4 5 0 …
Kevin Bell
  • 555
  • 1
  • 4
  • 10
1
2 3
…
99 100