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

**1**

vote

**2**answers

53 views

### How to calculate values between three cells using conditional calculation

I am new to this forum and not very new to Excel (not in depth)
I have to perform a calculation for three cells by conditional calculation. so here is the scenario
Column Name: Total Area (Cell ...

**0**

votes

**5**answers

9k views

### How do you add extra value to a cell that already has an existing number in it

I find this hard to explain, but I'm stocktaking beers, spirits, and liqueurs and I want to keep one column for my ullage count for each product. However, as I go through the days, I often come across ...

**2**

votes

**1**answer

216 views

### Capitalize first word in each row of a column in mac

I want to capitalize the first letter of every word in each row of a column.
So I create a new column, type in =PROPER(B2), then drag that down the entire length of that column. It works great, it ...

**1**

vote

**2**answers

4k views

### Look up the next highest value in another column with Excel

I have two columns A and B.
A B
20 100
40 200
60 300
80 400
140 500
190 600
240 700
User inputs a value in a cell. I need to check ...

**-2**

votes

**2**answers

309 views

### Nested IF formula

I have:
in *Column F* in *Column S*
F2 Monday S2 Orange
F3 Tuesday S3 Mango
F4 Wednesday S4 Grape
F5 Thursday S5 Pawpaw
F6 Friday ...

**2**

votes

**2**answers

950 views

### Calculate average score for by category in Excel

Assume I have the following 2 sheets in an Excel workbook:
Sheet companies:
A B C
1 COMPANY COUNTRY SCORE
2 Foo Greece 10
3 Bar Greece 2
4 Baz Greece 3
5 ...

**2**

votes

**3**answers

5k views

### Include an OR condition in a COUNTIF?

This question has been asked before and the answer always seems to be to add together two COUNTIFs. This is fine for most applications, but won't work for me.
I have a column that can contain 0, 1 or ...

**2**

votes

**1**answer

224 views

### Calculating mean of value across random trials/refreshes in Excel?

Say I have a 1000 cells filled with random values and a particular cell that computes a final answer based on those 1000 values. Now I wish to track the value of the answer over 1000 trials i.e., I ...

**0**

votes

**1**answer

1k views

### In Excel I need to find data matches between two arrays - one horizontal and one vertical

In Excel 2010 (or 2007 - I have both, although my OS is only Win7 32 bit as a limitation for some legacy applications we run), I need to find how I can find and return the matching value from two data ...

**5**

votes

**3**answers

13k views

### Check if Excel cell text is numeric using formula only

I have to find out if my cells text is a numeric value and wanted to use an elegant non VBA method that doesn't impede on its current state or value.
What I've found is that the ISNUMBER() function ...

**0**

votes

**1**answer

205 views

### Issue with SUMPRODUCT Function

I am Having a issue with the SUMPRODUCT Function in Excel 2007. Here is my string and issue:
=SUMPRODUCT(($B$2:$B$800>DATE(2013,4,1))*($B$2:$B$800< DATE(2013,4,5)))
A B
1 4/1/2013
2 ...

**0**

votes

**1**answer

70 views

### Formula to search array and return values

I have a Quotation Book and an Order Book each with a column called Q#. In the Quotation Book the quote numbers are generated and in the Order Book quote numbers are entered without being in order.
...

**0**

votes

**1**answer

1k views

### vlookup and indirect and external shared workbooks

i have two excel workbooks that are both shared in our company folder.
excel1 is where each employee has his own page to fill information and column 1 of each page is the row reference
excel2 is a ...

**0**

votes

**1**answer

39 views

### Focusing on particular information within a cell when doing a calculation

When performing a calculation using two cells in Excel, is there a way to focus on specific information within a cell, when entering the formula?
For example, I have two time cells (start time and ...

**2**

votes

**2**answers

74 views

### Can I use Microsoft Excel to convert a “ranged” table to a full breakdown table?

Suppose I have a spreadsheet with the following rows and columns:
QTY MIN | QTY MAX | 1 Color | 2 Color | 3 Color | 4 Color
1 | 10 | $1.00 | $1.50 | $1.75 | $1.90
11 | 20 ...

**0**

votes

**1**answer

11k views

### If cell B1=1 then DELETE cell A1 (excel 2003 formula) - How to do it?

I work with Excel 2003.
If cell B1=1 then DELETE cell A1,
and if cell B=0 then UNCHANGED.
How can I do this?
Example:
http://oi33.tinypic.com/mwsm03.jpg
Many thanks :)

**0**

votes

**2**answers

719 views

### excel: how to leverage vlookup with count

I have some data in columns, several binary variables:
| A | B | C |
| 0 | 1 | 1 |
| 1 | 0 | 1 |
| 0 | 0 | 1 |
| 0 | 1 | 0 |
| 0 | 0 | 1 |
| 1 | 1 | 0 |
and then another variable with three values:
...

**3**

votes

**2**answers

7k views

### How to implement MATCH and INDEX functions together in Excel VBA?

I am trying to find the row number of the first cell that is less than zero.
So, by browsing through the net I was able to come up with this formula which works perfectly fine:
...

**1**

vote

**1**answer

92 views

### Using an array formula to add up several cells in another worksheet (using INDIRECT)

I have an Excel 2007 workbook. On one worksheet, I want to add up several regularly spaced values on another worksheet. I can easily do this with a very long sum formula without an array formula but ...

**1**

vote

**1**answer

3k views

### How do I add values in column B if column A contains the same employee surname more than once?

State of Data: I have column A which has names of employes and column B has number of hours. How do I add the hours if column A contains same employee surname more than once?
I have been using what I ...

**0**

votes

**1**answer

155 views

### Conditional Data Type based on Cell Value

I know that you can conditionally format the visual treatment of a cell using conditional formatting, but can I control the data type of a cell based on the value of another cell?
I'm building an ...

**0**

votes

**1**answer

42 views

### How to check for specific values in a column

I have a column that I'm working with that has a number of possible text options in it. The only two I care about are "Bad" or "Good."
I'd like to write a function that results in 1 for "Good," -1 ...

**5**

votes

**2**answers

3k views

### Highlight row if date is in current week

I'm working on a data table for finances. I want to highlight the row if the date is in the current week:
What it should look like:
The current condition formatting for the yellow fill spans ...

**0**

votes

**1**answer

1k views

### Excel Time Subtraction

I think there is a way around the time subtraction issue I have in Excel. The problem is that when the time in cell F (for example 11:29am) is less than the one in cell A (for example 11:30am), the ...

**0**

votes

**1**answer

463 views

### On Excel 2010, the =COUNTIF function displaying the value improperly

Well, here is the code I am using below:
=IF(COUNTIF(G5:W13,"10"),"","$10")
This code is working perfectly fine, however the number is not actually been treated as a number.
I have done a number ...

**0**

votes

**1**answer

416 views

### remove values less than a number in excel array

I have a value that needs to calculate stock based on the previous years sales:
{=CEILING(SUM(IF('Inventory Transactions'!$H$2:$H$7137=LEFT($B6,3),IF('Inventory Transactions'!$O$2:$O$7137='Do Not ...

**0**

votes

**1**answer

2k views

### Combine duplicates in Excel, sum some columns and weighted average the others

This question refers to an existing subjexct on our site. I need a variation on this solution. I have a spreadsheet with 8 data columns. The number of records can vary from a few hundred to about ...

**1**

vote

**3**answers

613 views

### fill an ordered series in column A only if its corresponding cell in B having any text

I want that if in column B having text then in corresponding cell of column A number should be filled according to previous entry or so called series.
I tried this for A1 =IF(B1=="","",1) but it is ...

**0**

votes

**3**answers

650 views

### How to update cell references when setting the Formula property in VBA?

Here is a snippet of my code:
For i = 1 To k
If Range("D2").Offset(i, 0).Interior.ColorIndex > 0 Then
Else
Range("D2").Offset(i, 0).Formula = Range("D2").Formula
The ...

**0**

votes

**1**answer

2k views

### Change formula font size in LibreOffice

I am trying to change the font size of the formula tool in LibreOffice. I have been told:
To permanently change the default size (12 pt) used in LibreOffice
Math, you must first set the size ...

**2**

votes

**2**answers

104 views

### Excel macro checks if there is formula

I want to make a macro that checks if that cell get its value from a formula. I cann't find anything similar. Any tips??

**0**

votes

**2**answers

824 views

### How can I easily combine multiple Excel columns to a single columns?

I have a dataset which has a seven possible conditions based on data in other columns. I had to split my formulae for those conditions across seven different columns. I would like to combine all seven ...

**1**

vote

**2**answers

741 views

### Passing Range to ABS function in VBA

I would like to perform a function which looks like this
Selection.FormulaArray = "=GEOMEAN(ABS((C646:E646)))"
Since my range keeps varying I would like to pass the range as a variable which looks ...

**2**

votes

**1**answer

3k views

### Hide “inconsistent formula” error in a selection in Excel

This may seem easy to more advanced users, but I can't seem to find this out there.
I have a complex Excel worksheet. It has various formulas/calculations throughout the sheet.
Unfortunately, ...

**0**

votes

**2**answers

64 views

### Time Difference In Formatted Text

How can i find the time difference from like "9-10:30" formatted cell.What is the formula to find the "1.5" hour or "90" minutes from that cell.

**0**

votes

**1**answer

8k views

### How to copy the value and format of a cell into another cell on Excel 2010? [duplicate]

I have been looking all over the internet for this question, but it has not been answered by anyone.
I am, on Excel 2010, trying to copy (not copy and paste) the format AND value of a cell, onto ...

**1**

vote

**1**answer

87 views

### Just get date stamp once in excel

To datestamp a cell in Excel I use the forumula : =NOW()
The problem is that as I datestamp new cells all other cells which contain the formula NOW() are updated with the new datestamp. Is there a ...

**0**

votes

**2**answers

975 views

### How to count number of rows excluding rows with duplicate data

I have a table in which the column "Doc. No." has duplicate info as below; please advise on which formula I should use to count the number of unique Doc. that I have (as reference to the below table ...

**-1**

votes

**3**answers

1k views

### How to lock a formula with self reference?

I'm trying this:
C9 formula: =-40+C9
What I want to is being able to enter some value in C9 that will not overwrite the formula and get a result in that cell. For example:
If I enter the value 140 ...

**-1**

votes

**2**answers

356 views

### How can I refer to a cell that is a constant number of cells away from a particular cell?

I have two identical tables on a sheet: one that contains text for reference, and a second that contains values.
I am trying to sum up some cells in the second table but using the addresses of ...

**-1**

votes

**1**answer

81 views

### Copy text from “joined cell” to another sheet's smaller joined cell

Normally, text appears in the cell when it contains a reference to another (=Sheet1!A1 will show the contents of sheet A1) However, if both target and destination are joined cells, and the target is ...

**2**

votes

**0**answers

239 views

### VBA - Arrayformula to a range stored as array variable

I need to apply a formula array to dynamic ranges in my spreadsheets, which for a fixed size range works fine using for example:
Selection.Offset(2, 0).FormulaArray = ...

**2**

votes

**1**answer

955 views

### Find last row in sorted table for matched value

I have a table in Excel, which for simplicity sake we will define as:
Order# | Date | Price |
123 | 1-1-2013 | 100 |
135 | 1-2-2013 | 150 |
145 | 2-4-2013 | 100 |
145 | 2-5-2013 ...

**2**

votes

**3**answers

340 views

### Counting frequency of comma-delimited tags in Excel

I have an excel spreadsheet with a list of seminars and classes that students have taken that (when abridged) looks like:
(A)___ClassName________|(B)_________Tags____________|
Astrobiology ...

**0**

votes

**1**answer

111 views

### VLOOKUP in Excel with IF statements?

I'm not sure whether IF statements are actually needed in this problem;
however, I'm totally stumped.
In the first worksheet I have a column for "years employed" (Column D6:D107) ranging from 0.1 to ...

**0**

votes

**5**answers

242 views

### Excel - Sum of values in a column given the order type available in another column

I have excel 2010 and I am trying to do the following (the table below has been done manually)
Customer Order type Revenue Sum
001 Pears 50
...

**0**

votes

**2**answers

434 views

### Auto orderlist Excel 2010

I'm using Excel 2010 to create a inventory of my shop.
Now I would like it to automatically create a order list in the second sheet.
My first sheet look like this.
Item: Stock: Min. Max.
...

**1**

vote

**1**answer

137 views

### Excel formula creation

I have a spreadsheet with a lot of job related data. One column has names of people in it and another column has the dates on which those people completed jobs. Each person may do more than 1 job per ...

**0**

votes

**2**answers

618 views

### Copy multiple cells from one to multiple worksheets

I have a workbook in Excel 2007 with 52 worksheet each representing a week of the year, each worksheet is laid out the same with 16 rows and 26 columns to give me 14 personnel and 24 hours with other ...

**0**

votes

**1**answer

547 views

### How can I get the hex value of an 8 row column with depending on background color?

I have a spreadsheet that I'm using to help visualize a character bit map for each ASCII character. For a 5x7 font, I use 5 cells wide (columns) and 7 cells tall (rows). I then marked the background ...