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

**3**

votes

**1**answer

284 views

### How to make many column formulas ignore the same lines in Excel?

I have multiple columns and at the end of each one I have a SUM and MEAN lines.
Now I want to ignore some lines thus making =SUM(A1:A15) into =SUM(A1:A13;A15) if I want to ignore line 14. Doing this ...

**3**

votes

**1**answer

959 views

### Absolute reference all cell references in a Microsoft Excel formula?

For a Microsoft Formula like this:
=IF(Data!H5=3,0,IF(Data!H5<=Data!H19,3,IF(Data!H5<=Data!H20,2,IF(Data!H5<=Data!H21,1,0))))
Is it possible to mass convert all the relative cell ...

**3**

votes

**1**answer

1k views

### Request: Excel equivalent of Google Spreadsheet ARRAYFORMULA + CONTINUE functionality

Google Spreadsheets has a syntax permitting a user to specify an entire row, column, or area with a formula in a single cell. For example, entering ...
=ARRAYFORMULA(ROW(A3:A6))
... into cell C1 ...

**3**

votes

**1**answer

549 views

### How can I disable the 'your formula has an error' pop-up in Excel 2007?

This is driving me absolutely crazy, while I'm in the middle of typing my equations into a cell I like to pre-create the parenthesis sometimes when order of operations is being explicitly set, however ...

**3**

votes

**1**answer

5k views

### Cross Referencing Data between Excel Sheets, plus a conditional statement

I have an Excel workbook with 2 sheets.
1st Sheet: Old User Data
2nd Sheet: New User Data
I want to add a new column to the 2nd sheet that contains the old user ID from the 1st sheet. To get this, ...

**3**

votes

**2**answers

3k views

### Excel conditional formatting based on a formula

I would like to use Excel's conditinal formatting of a particular column based on values in two other columns.
I have values in columns A, B, C and D.
column A has text values (any value user ...

**3**

votes

**2**answers

3k views

### Summing a column in the same column without a circular reference

In Excel you can sum a whole column with =SUM(B:B). But of course you can't use that formula in the column you want to sum, because you'll get a circular reference.
Is there an easy way of summing ...

**3**

votes

**3**answers

1k views

### Is there a way to give an Excel table a name that is worksheet-scoped instead of workbook-scoped?

When you create a table in Excel (I'm using Excel 2007) you can give it a name that shows up in the Name Manager dialog. However, the name has workbook scope. I'd like to have a table with a name ...

**3**

votes

**4**answers

150 views

### How to identify duplicates within text in two columns in Excel

I have a worksheet where column A has various names in varying formats:
A1 John Smith
A2 Jones, Mary
A3 Sally Gomez
A4 The Gonzalez family
Column B has similar data:
B1 ...

**3**

votes

**1**answer

66 views

### Using a Defined Name in a Formula

I have a set of cells with values:
A1,B3,C5,D7,E11,F13,G17,H19,I23
I have a formula to select, at random, the value stored in one of these cells:
...

**3**

votes

**1**answer

2k views

### How to convert data in Excel to percentages of total?

Is there a quick way to convert all cell values in a table to a percentage of a total listed in a separate column? I tried posting an image of my data as an example, but my reputation here isn't ...

**3**

votes

**1**answer

556 views

### Replace cell references with values without F9 using macro

This is an Excel macro question. Given a series of formulas such as the following:
= A1 + A2
= B2 + B2
etc., ...
I would like to replace each of these cell references with the values without ...

**3**

votes

**1**answer

382 views

### Auto formatting new pivot rows to data bars

I have a pivot table that takes data from database and displays it as a data bars. Problem is, that the data is updated daily - every day new row is added so the pivot table is growing. Unfortunately ...

**3**

votes

**1**answer

109 views

### Match formula Excel case sensitive

I have an if(iserror(match formula in Excel. Just when I thought I was finished with my sheet I found out that the formula isn't case sensitive. How can I make my formula case sensitive?
Here is my ...

**3**

votes

**2**answers

91 views

### Define value of a column by another column in Excel

This seems like it should be so simple! I need to define column B by column A
If column A is equal to any value in the array "taco,burger,spaghetti" column B should read "food", if column A is equal ...

**3**

votes

**1**answer

229 views

### Input the same formula in excel across multiple worksheets to follow a sequence

I need to put the same Excel formula in Excel so that it follows on in the sequence. So sheet 1 needs to have ='Booking Sheet'!F1 then sheet 2 ='Booking Sheet'!F2 and so on.
Is there a way I can do ...

**3**

votes

**1**answer

417 views

### Openoffce Calc - Substitute space with line break by function

in my calc document for example cell A1 has the value
A1:"AB 0123"
and in cell B1 I need the same value with a line break instead of the space like
B1:"AB\n0123".
How can I archive this?
I ...

**3**

votes

**1**answer

4k views

### What does “calcs” formula do in Microsoft Excel?

I encountered an XLS file with this formula in some of its cells. I searched the web for information on the formula, but I couldn't find anything. Could you help?
The formula in question is:
...

**3**

votes

**1**answer

4k views

### OpenOffice Calc - how to insert BLANK in a formula?

In Calc, as in most spreadsheet applications, an IF() function exists that can be used to control behaviour, for example inserting this formula in cell C1.
=IF(A1>B1;"A";"B")
will display A in ...

**3**

votes

**1**answer

516 views

### Excel 2007 Function, last three items

My question is twofold, so bear with the wall of text. I'm making sort of a banking spreadsheet. I will input income/expenses in four columns (Date/Amount/Type/Description) and I need it to keep track ...

**3**

votes

**2**answers

3k views

### How do I make a random selection in a spreadsheet using more than one criteria

I have a spreadsheet created in OpenOffice (V3.3.0 in Windows 7 32 bit) that I use as a database of my DVD collection. After asking a question in SuperUser, I have it so it can pop up a random title ...

**3**

votes

**3**answers

7k views

### Formula in table header cells

I have a table in Excel 2007 that I want to summarise, in a similar fashion to a Pivot Table, but for various reasons I can't use a pivot table.
I like the "Format as table" features of sort and ...

**3**

votes

**4**answers

6k views

### Excel formula to display a string of text and numbers

I need one column to show the following text: prxyz,(data1 here),0699,ABCD(data2 here),xxx
data1 is in column A and data2 is in column B.
the format of data1 needs to be 5 numbers (sometimes ...

**3**

votes

**4**answers

965 views

### Averages spread out over several fields in Excel?

I have a table with a sequence of data.
This data is used in a graph.
There are holes in this data that I want to fill in with smoothed out numbers.
Take this table for example:
In the example ...

**3**

votes

**1**answer

244 views

### Excel 2010: Formula for Sum and Return Date

I have a workbook with a Summary sheet and a Data Set sheet with a linked table containing 700k+ records of financial data on investments. (In my particular case, it is real estate properties; but ...

**3**

votes

**1**answer

834 views

### Find last value in a cell range that meets a condition

I'm looking for a formula in LibreOffice Calc to find the last value in a range that meets a condition. Here is sample data:
A B C D E F
...

**3**

votes

**1**answer

247 views

### Excel macro copy same entries once in another sheet paper

I'm really new to VBA Excel.
Let's say I have paper 1 with in column A many alert types, and column B the place where it happened (I'm working for a security company). What I need to do is read ...

**3**

votes

**3**answers

103 views

### Automating a choosing process in Excel

The title says practically nothing, so I'll try to explain.
I have some calculation which is based upon some input parameters, doesn't matter now. Out of it (the calculation) I get a number, let's ...

**3**

votes

**1**answer

997 views

### Populating cells with data from another spreadsheet after just keying in a few letters

I have 1 workbook with 2 spreadsheets. Spreadsheet 2 column A contains a long list of company names, Columns B - H contain critical information about the company.
Spreadsheet 1 contains all of the ...

**3**

votes

**1**answer

2k views

### How to set 2 conditions / criterias for VLOOKUP / LOOKUP / etc in OpenOffice Calc (or Excel)

I have this spreadsheet that started as a silly aid for a game (Mafia Wars 2), but grew into a tricky spreadsheet question.
In the game your character have 9 "slots" for weapons and armors, 1 for ...

**3**

votes

**2**answers

5k 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 ...

**3**

votes

**1**answer

27 views

### Extract content from the formula in a referenced cell in Excel 2007

I have a cell A3 that says Name.
Cell F16 contains =A3, which displays Name, just like I want it to.
I need cell F26 to take the information from cell F16 and display the referenced location. In ...

**3**

votes

**0**answers

25 views

### Nested IF function returning tiny value instead of zero [duplicate]

Whilst creating a balance sheet for a lotto syndicate, I encountered this phenomenon;
Note the negative zero value in D5. With no formatting, its actual value is -0.0000000000000230926389122033 ...

**3**

votes

**1**answer

1k views

### Toggled Hyperlink with If:Then formula [duplicate]

I'm attempting to make a formula that will toggle a hyperlink on or off depending on the argument. Basically, I want to make it argue the following:
If there is text in (Tab A, Cell A), then create a ...

**3**

votes

**2**answers

186 views

### Flatten Excel Formula (2007)

Say cell A1 has the formula A2 + A3
and B1 has the formula B2 + B2
and C1 has the formula A1 + B1
I need an easy and quick way to create the formula for C1 in the flattened state..
i.e.
(A2 + A3) ...

**3**

votes

**1**answer

562 views

### Excel: Why is VLOOKUP returning inconsistent and unexpected results?

I'm automating an Excel column to assign account values based on the vendor paid. For instance, if AT&T gets paid, the account value is Utilities:Telephone, and when the post office is paid, the ...

**3**

votes

**1**answer

1k views

### Numbers conditional formatting based on a different cell in the same row

I have a Numbers spreadsheet, with "quantity required" and "quantity in stock" columns, then each part is on a row. I'm trying to use conditional formatting to turn a cell yellow when # required = # ...

**3**

votes

**2**answers

438 views

### Convert “Mon Aug 01 09:08:25 CDT 2011” to a usable date/time using Excel

I have a giant spreadsheet with computer names and the date stamp that they last reported into our PGP server that I need to convert to a usable date/time, so I can sort the column by the time the ...

**3**

votes

**1**answer

81 views

### Excel, how to use text functions on lookup array without putting in another column?

If I'm using a vlookup, or a match function but I need to do some mumbo jumbo on my lookup material, is there a way of doing this in the lookup array without simply adding another column?
Let's say I ...

**3**

votes

**1**answer

45 views

### Taking an indirect reference in Excel and making it a direct reference

For many somewhat complicated reasons – it has to do with transposing columns and rows because of third party software limitations, etc. – I have had to use the INDIRECT function in order to make ...

**2**

votes

**2**answers

14k views

### How do I get “yes” or “no” in Excel if the information comes from “true” and “false”?

first of all, I have to point out that I am a German visiting this forum (I couldn't find anything useful on the German websites), so excuse my English if it is bad here and there ;-)
Here is my ...

**2**

votes

**4**answers

4k views

### How to calculate how much I've worked per day?

I'm having a problem when I try to calculate how much time I've worked per day by using an Excel formula. It returns valid results for when I've worked until 12:00 PM but if I passes it, values come ...

**2**

votes

**3**answers

485 views

### How to update all functions in Excel 2007?

Is there a way to quickly fill down a column of functions. For example, if I change my function from
=3*B2
And then fill down so it does it for a lot of rows.
But then change this column to:
...

**2**

votes

**4**answers

64 views

### How to apply a formula to an entire Excel column using a keyboard missing its END key

I have a keyboard with a trackpad that's missing some of the normal keys available on a full-sized keyboard (among them Home and End).
Also, there are 30,000 rows in the sheet and dragging the corner ...

**2**

votes

**5**answers

2k views

### Does excel have an arrow key shortcut to skip blank cells?

In most versions of Excel (I'm using 2010), pressing ctrl+up or ctrl+down will take you to the edge of the current data region or the start of the next data region in a worksheet. Effectively skipping ...

**2**

votes

**4**answers

36k views

### Excel 2010: Add text to each cell of a column

How can I add @gmail.com or something similar to a column of usernames without having to type this into each cell? I am using Excel 2010. I have a list of several hundred usernames that need to have ...

**2**

votes

**3**answers

198 views

### Excel 2010 input blank for all 0 results

I use this
=IF(MINUTE(E59)+(SECOND(E59))=0,"",(HOUR(E59)*60)+(MINUTE(E59)+IF(SECOND(E59)<30,0,1)))
To calculate time into minutes like 00:06:13 = 6 as a result and 00:00:00 = blank as a ...

**2**

votes

**6**answers

40k views

### How to prevent Excel rounding numbers or adding redundant 0's?

I have a column of numbers that appear like this:
but the actual value of the shown cell is 20130.153334
Other values have a different number of decimal places. I don't want to add redundant 0's, ...

**2**

votes

**4**answers

4k views

### Can one list everything that uses a particular cell in Excel 2010?

I've been tasked with making changes to an Excel 2010 spreadsheet I know little about.
One of the biggest changes the client wants is to remove numerous cells that are no longer needed by his ...

**2**

votes

**4**answers

4k views

### Excel formula to get the penultimate value in a row

Imagine I have a row of data in excel, like this:
[A] [B] [C] [D] [E] [F] [G] [H]
[1] Data 5 8 12 32 15 9 89
I want a formula that will return the ...