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

learn more… | top users | synonyms (5)

3
votes
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
2answers
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
2answers
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
3answers
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
4answers
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
2answers
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
2answers
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
3answers
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
4answers
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
4answers
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
1answer
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
1answer
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
1answer
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
3answers
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
1answer
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
1answer
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
2answers
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
1answer
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
0answers
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
1answer
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
2answers
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
1answer
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
1answer
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
2answers
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
1answer
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
1answer
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
2answers
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
4answers
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
3answers
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
4answers
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
5answers
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
4answers
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
3answers
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
6answers
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
4answers
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
4answers
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 ...