Skip to main content

Questions tagged [worksheet-function]

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

Filter by
Sorted by
Tagged with
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. ...
Ray's user avatar
  • 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)?
Tim's user avatar
  • 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 ...
joseph.hainline's user avatar
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 ...
Rodolfo Oviedo's user avatar
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 ...
Jonas's user avatar
  • 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 ...
bwerks's user avatar
  • 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 ...
Davuz's user avatar
  • 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 (...
Adam's user avatar
  • 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 ...
David's user avatar
  • 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 ...
Mark Meuer's user avatar
  • 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 ...
drapkin11's user avatar
  • 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 ...
Mestika's user avatar
  • 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 ...
dwwilson66's user avatar
  • 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
Sundhas's user avatar
  • 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 ...
rukuto's user avatar
  • 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 ...
rumtscho's user avatar
  • 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....
Kevin Bell's user avatar
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 ...
Torben Gundtofte-Bruun's user avatar
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 ...
learningAsIGo's user avatar
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?
Don Vince's user avatar
  • 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) ...
pulsar3424's user avatar
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 &...
Jackie Reid's user avatar
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 ...
Dheer's user avatar
  • 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,...
Jer's user avatar
  • 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 ...
Brian Hooper's user avatar
  • 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 ...
Highly Irregular's user avatar
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).
anol's user avatar
  • 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 ...
Erick Robertson's user avatar
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 ...
SrinR's user avatar
  • 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 ...
rcphq's user avatar
  • 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 ...
Jakeray's user avatar
  • 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 ...
Scott Mitchell's user avatar
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 =...
Jefferson's user avatar
  • 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 ...
glh's user avatar
  • 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 ...
Lea Cohen's user avatar
  • 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 ...
Boy's Rules's user avatar
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 ...
James Mertz's user avatar
  • 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 =...
joe_coolish's user avatar
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 ...
Phrogz's user avatar
  • 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. ...
Souradeep Nanda's user avatar
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 ...
user30752's user avatar
  • 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(...
user160917's user avatar
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?
Swati Sharma's user avatar
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?
Gh0sT's user avatar
  • 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 ...
user2463758's user avatar
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 ...
Canadian Luke's user avatar
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 ...
Tracker1's user avatar
  • 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 ...
Abbie's user avatar
  • 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 ...
Chris Farmer's user avatar

1
2 3 4 5
164