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

**0**

votes

**1**answer

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

**1**answer

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

**2**answers

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

**1**answer

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

**2**answers

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

**2**answers

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

**1**answer

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

**1**answer

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

**2**answers

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

**1**answer

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

**2**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**2**answers

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

**5**answers

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

**2**answers

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

**3**answers

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

**2**answers

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

**1**answer

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

**3**answers

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

**2**answers

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

**2**answers

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

**3**answers

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

**2**answers

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

**4**answers

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

**3**answers

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

**1**answer

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

**6**answers

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

**2**answers

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

**2**answers

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

**4**answers

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

**7**answers

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

**3**answers

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

**2**answers

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

**2**answers

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

**3**answers

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

**3**answers

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

**2**answers

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

**8**answers

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

**4**answers

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

**4**answers

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

**3**answers

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

**4**answers

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)?