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

learn more… | top users | synonyms (3)

0
votes
1answer
293 views

MS Excel convert cell matrix (1x15 -> 3x5)

What is the formula for displaying cells in a 3 cell increment at a time, like A1, A4, A7, A10? Moreover, would it be possible to transform below a list (1x15) of data into a table(3x5). Thanks. ...
1
vote
1answer
4k views

Excel cell references not updating when referenced cells are sorted

There are two tables, each with 75 entries. Each entry in the 2nd table calls an entry in the first table a parent. One of my 2nd table columns contains the "Parent Price", referencing the Price ...
0
votes
2answers
3k views

Excel Sum that excludes duplicates

I have data in the following format: Id Value ------------ 1 20 2 40 3 20 3 20 4 50 I want to sum the value column while excluding duplicates from the id column. In this ...
9
votes
1answer
10k views

allow two possible values in a Excel cell

I would like to create a cell format for one cell to only allow the value "yes" or "no" for this cell. How could I do this?
0
votes
2answers
260 views

Excel Matching problem with logic expression

I have a block of data that represents the steps in a process and the possible errors: ProcessStep Status FeesPaid OK FormRecvd OK RoleAssigned OK CheckedIn Not Checked In. ...
0
votes
2answers
659 views

Spreadsheet formula: lowest 100 values in a range

Is there any way I could sum up the lowest 100 values within a range? I know that min() would give you the lowest value but i need something to return the 100 lowest values. I just used 100 ...
1
vote
1answer
2k views

Excel: Summing values within a certain date range (> start date and < Finish date)

How do I include a 'Between these dates' criteria inside a SUMPRODUCT? =Sumproduct((RangeName=Cell A1)*(RangeDate=Between 01/03/10 and 31/03/10)*(RangeCost)) What's the way to manage dates ...
0
votes
1answer
767 views

Pivot tables: How can I total the subtotal?

My SUBTOTAL row shows Person A needs £115, Person D £234 and Person G £789, but how do I SUM those totals and get it to show the total on the same ROW? The Rows are subscription names. The Value ...
2
votes
2answers
5k views

How can I change a cell's background color based on the cell's current value?

I have a column with function values of True or False: =IF(ISNA(A1), false, true) from row 1 to 5000+ rows. I would like to mark all the False as red background color or text as red. Is there any ...
2
votes
1answer
516 views

Excel: ROUND & MOD giving me strange DATE results

This is sort of related to a previous question. My formula, which seemed to work fine yesterday now gives strange results. Today is the 30th of March (30/03/10). It's 10:11am on the clock that the ...
1
vote
2answers
2k views

Excel 2007: how to work out percentages of groups (top 10% of…)

I've recently read the following paragraph, and wondered: how you would organise the data (possibly Column A = country, Column B = salary, Column C = tax paid) but what formulas/calculations are ...
4
votes
1answer
2k views

Excel 2007 - How can I write “use this cell” or IF BLANK “use this cell”?

I am trying to show the Days between NOW() and the dates (dd/mm/yy) in, either Column B or Column C - depending which one is NOT blank A B C 29/03/10 01/04/10 29/03/10 ...
1
vote
1answer
1k views

Excel: VLookUp solves a potential Nested IF problem

I manually enter numbers on one cell according to text values in the cell adjacent to it. Is there a way to use the IF function to help me manage this? The text is automatically generated with a ...
1
vote
1answer
747 views

Concatenate cells that change daily automatically?

I use concatenate to pull data together from different cells in my spreadsheet. Since my data changes daily, I want the formula to also change daily without having to manually input the new cell in ...
2
votes
2answers
3k views

How can I write an excel formula to do row based calculations?

Could someone please help me with the following excel formula. Each row in my spreadsheet contains details about a product. I have one column 'Quantity' (Col B) and 'Profit' (Col F). I want to use ...
3
votes
5answers
27k views

Excel: copy cell formatting in equation

If I make an equation: ' on cell A2 =A1 Is there a way to make A2 have the exact same formatting as A1 and not just the value? EDIT: i.e. I want a FORMULA that copies both the value and the ...
2
votes
2answers
13k views

How do you include an OR inside a SUMPRODUCT function, in Microsoft Excel?

How do you include an OR inside the sumproduct function in Excel? I currently use two sumproduct formulas because I have two variables I want it to count: ...
0
votes
3answers
19k views

Excel 2007 formulas not autofilling when I drag down

If you write a formula like: =sumproduct((something=$A1)*(somethin=$B1)) Giving a result of 8. Usually the references and results change as you drag it down into other cells. Well,the references ...
0
votes
2answers
664 views

How to make an excel formula which totals several agecent rows based on cell values

I have an excel sheet with three columns: date, person and percentage. I would like to put in a data validation that flags cells if the total for a given data/person combination do not equal 100%. Is ...
0
votes
1answer
602 views

Ctrl + F9 Does not update Formula Text on Excel 2009

I have a lot of excel workbook that follows a template. Since each workbook has a lot of sheets. I usually just use the equal formula to refer to the heading of the first sheet for the succeeding ...
3
votes
3answers
4k views

Excel annoyance: How to maintain formulas in a ledger?

A common use of Excel is to create a spreadsheet which functions as a transaction ledger of some kind, where one or more fields in each row is calculated by adding a value or values from the current ...
4
votes
2answers
21k views

excel VLOOKUP, how to specify dynamic worksheet name?

I'm trying to create a VLOOKUP formula in excel, however, the table array may be in a number of different worksheets. For example, I have this forumula =Vlookup(a1, ...
1
vote
2answers
108 views

Software to real time display of tex formula available?

Texit is only available in mac Lyx is too complicated to setup,need to install several softwares. Is there a simple solution?
2
votes
3answers
33k views

How to set Function “formula” for whole column in Excel 2007?

I want to set "Formula" to all cells in certain column whatever the number of rows was. But i don't want to set this function to first cell "Header" of this column. How do i make it ?.
2
votes
2answers
4k views

What is an excel formula that will return me the last row number of a cell within a range that contains specific data?

I have a spreadsheet that will default with values of "No". Over time, these values will be changed to "Yes". I need a formula (no VB, please, VB is useless to me) that will get me the row number of ...
1
vote
4answers
2k views

SUM condition on one set of columns where another includes certain values

I have the following data in Excel where I want a formula that will give me the sum of all of the total rows: A B australia 10 australia total ...
13
votes
3answers
69k views

Excel: Replace occurences of one character with another in a String?

I want an excel formula that will change Haverford, PA to haverford,+pa. (Text is to lowercase, and spaces are replaced with plus characters.) The following will get the first space, but not all of ...
0
votes
1answer
784 views

Error 502 in OpenOfficeSpreadsheet formula

The formula failing is the following: =IF(TIMEVALUE(C2 & ":00") > TIMEVALUE(B2 & ":00"); 0; C2-B2) I previously tried =IF(C2 > B2; 0; C2-B2) but this also gives me "Error 502". ...
0
votes
6answers
18k views

Convert Excel Text string to Time

I'm trying to Export from an OLD System that stored Time in / Time out as a text string in 24 hour time. For example 3pm, is entered as 1500 (no colon) Is there a simple way I can convert these ...
4
votes
2answers
1k views

How can I create a reusable function in Excel 2007?

How can I create a resuable function/formula I can use in formulas in a spreadsheet? In particular I need a very simple DateDiff formula and would like a way to encapsulate this function: ...
0
votes
2answers
361 views

Excel string extract

I have a string in a cell: ABCDxxxxxEFGH ABCD and EFGH are constants, and xxxxx is of variable length. How do I write an Excel formula to extract xxxxx?
3
votes
4answers
40k 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 ...
17
votes
7answers
77k 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?
2
votes
3answers
2k views

Use regression equation in excel formula

I have a spreadsheet with a number of graphs on, and I have displayed the trend-lines / regression lines on these graphs. These are not simple linear regression lines, but are high order polynomial ...
2
votes
2answers
7k views

What's the difference between “vlookup” and “lookup” in Excel?

What is the main basic difference between VLOOKUP and LOOKUP in Excel? I read the main point is that you can specify a range of columns in VLOOKUP and then pick one to return the value. However, it ...
0
votes
2answers
289 views

Excel showing error message when typing “- do -”?

Why Microsoft Excel Show an Error Message when typing "--do--"? It show an message "Your Formulae is incomplete".
1
vote
3answers
799 views

Excel select column

In excel, if I want to perform a function with the column to the left, how do I go about this?
2
votes
3answers
477 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: ...
0
votes
2answers
507 views

Sort highest match in excel

How can in Excel sort multiple collumns with data? Column B = Subscribe date Column A = Subscribe name I have multiple columns with a lot of doubles names (A) and different subscribe dates(B) How ...
1
vote
8answers
5k views

How can I calculate the sum of all positive integers less than n?

I have the following function: f(n) = f(n - 1) + (n - 1) f(0) = 0 n >= 0 I have n declared on column A, and need the result of f(n) on column B. I'm trying to find the Excel formula equivalent ...
2
votes
4answers
10k views

Using two conditions with a SUMIF function in Excel 2003

For a given row on Sheet1, I use the SUMIF function to see if the value from the first cell of that row is on a list of historical values in Sheet2. That will basically summarize all the values on my ...
3
votes
4answers
5k 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 ...
0
votes
3answers
1k views

Sum Column, but only Filtered Rows

I have a large flat file of a bunch of ungrouped primary key values. I want to filter on a key and populate cell A1 with the value selected from the dropdown list. In the end, I want to see a sum of ...
6
votes
4answers
5k views

How do I get a reference to the last occurrence of a value in an array in Excel?

So if A1:A5 = {3, 3, 4, 4, 5} what function could I put into B1 to get a reference to A4 (the cell containing the last occurrence of 4)?