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

**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

662 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

599 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

781 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

360 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

76k 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

286 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

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