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

**2**

votes

**2**answers

90 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

755 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

592 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

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

58 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

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

73 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

658 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

920 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

279 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

**1**answer

825 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

264 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

101 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

202 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

311 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

136 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

470 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

468 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 ...

**2**

votes

**1**answer

3k views

### Compare multiple columns (more than 2)

Okay, so let's say I have a chart with N columns of data. What I want to do is compare every column to every other column, and get a count of how many values are equal in both columns. I know I can ...

**2**

votes

**2**answers

165 views

### OpenOffice Calc regex returns wrong value - what's wrong with my regex?

I have a column of text with values concatenated by my bank, as in a typical monthly statement.
Here is an example (column A240):
OCT 26 BALANCEPROTECTOR PREMIUM $10.05
I'm trying to set the ...

**0**

votes

**1**answer

388 views

### Creating multiple plot per level of factor (trillis graph) in excel

Is there way to automate the process to make graphs like the following (also known as trallis graph) in excel ? I know there are some R interface with Excel (some of them need to be purchased), but I ...

**1**

vote

**1**answer

3k views

### How to find last and first value meeting criteria in a series of values?

This is a sample of my input:
Then, I have midnights in a consequent days and I would like to achieve this result automatically:
To put it in words: I have datetimes in one column and I would ...

**0**

votes

**1**answer

3k views

### Excel Formula for filtering data from one sheet to another

I have table that has the following columns (Agent Name, Client, Policy #, Premium) with the name of agents repeating several times since they have multiple clients.
I don't want to give all the ...

**-2**

votes

**2**answers

124 views

### MS Excel IF THEN formula

I am trying to write a formula such that, if T5 is 1, and AB5 is 1, then LOW;
OR if T5 is 1, and AB5 is 2, then HIGH;
OR if T5 is 1, and AB5 is 3, then EMERGENCY
etc., for the whole chart..... T5 is ...

**0**

votes

**2**answers

115 views

### Modify formula to work with conditional values

I am currently using the below formula to calculate enablement costs for meters
=SUM(SUMIF($D74,{"New Business"}),$U74*5500)
This worked fine as the fee of $5500 was standard, however I now need to ...

**0**

votes

**2**answers

2k views

### How can I turn a Calculated Column back into a data column in an Excel Table?

I have a large Excel Table (44 columns, 1000+ rows), but one of the columns has been accidentally turned into a Calculated Column (See Here). The result being that now when the users try to add new ...

**0**

votes

**3**answers

496 views

### change link cell formula across sheets

I have a workbook named "stu", it has 1 to 30 sheets. In cell H4 I have linked a formula:
=[sub.xlsx]Sheet1!$B$2
in sheet1: cell H4 the formula in cell H4 should be =[sub.xlsx]Sheet1!$B$2
in ...

**1**

vote

**2**answers

1k views

### How is OFFSET supposed to work on filtered EXCEL data?

Consider:
+---------------+
| Column A |
+---+---------------+
| 1 | Milan |
+---+---------------+
| 2 | Paris |
+---+---------------+
| 3 | London |
...

**0**

votes

**2**answers

3k views

### Excel formula to search cell values from one column and add values from another cell if true

I don't think this should be too complicated for Excel, but I am trying to figure out how to:
1) Determine if a cell in column A contains the text "FF"
2) If so, jump over to the corresponding cell in ...

**0**

votes

**3**answers

1k views

### show cell value if another cell is true, else go to next row

It's my table in Sheet1:
A B C D
------------------------------------------
1 220 John Good
2 210 Jack Bad
3 300 ...

**0**

votes

**1**answer

106 views

### Ms Excel Formulas for Date and Day

I need help.
I have written a specific date, for eg 13/03/2013 in A1.
Now I want its corresponding weekday in A2 (i.e. 13/03/2013 (Wednesday).
In A3 I want it to show two days ahead (i.e. 15/03/2013).
...

**2**

votes

**3**answers

946 views

### How to format a date field based on whether the date has passed?

I am trying to create an Excel spreadsheet where, when I input a date:
if that date is within 30 days it will go yellow,
if the date has already gone then it goes red, and
if the date is more than ...

**1**

vote

**3**answers

2k views

### How to create an excel report by month

I have a list of data in excel with a date column. When I want to generate a report I typically create a new column in the data tab and type in a code in text format like "April08", then I put a ...

**0**

votes

**0**answers

142 views

### How to get new data to automatically slot into correct column in Excel?

I am trying to move new data into an existing database with set columns. The existing database has a column for every possible type of data, but not all new entries contain all columns. So I can't ...

**0**

votes

**2**answers

552 views

### Formula help for matching data from multiple columns in multiple worksheets

What formula do I need in my worksheet DFW column C that will do the following?:
On worksheet DFW take data from cells A2 together with B2 and see if there is an exact match in worksheet Feb columns ...

**0**

votes

**1**answer

203 views

### Get unique comma separated values from list of comma seperated values

I have a list of csv in a single cell, and I need to get the unique values from that list and put them in another cell. For example, in one cell I would have:
DIV-154, FOD-371, UDL-913, DIV-154
And ...

**1**

vote

**2**answers

1k views

### How can I filter specific rows from an Excel spreadsheet based on criteria?

I have a spreadsheet from which I need to select ONLY the first five rows in each category.
I only want columns A and B when C is less than 5 -- =IF($C1>5,$A1,) and =IF($C1>5,$B1,) -- but I ...

**0**

votes

**2**answers

461 views

### Sumproduct fails when range is only 1 row and part of the calculation is blank

I have the following formula that works when the ranges have more than 1 row, but generates a #VALUE error if there is only 1 row and one of the factors is blank.
=SUMPRODUCT(cost, units)
Cost ...

**0**

votes

**1**answer

425 views

### Unable to get the VLookup property of the WorksheetFunction class

I want to implement a barcode finder in excel, so I have item names and barcodes in separate sheets depending on what group an item belongs to. The user clicks on a radio button (selecting Group 1, ...

**0**

votes

**1**answer

149 views

### Complex excel formula help needed?

I have a complex formula i need to create to look up various fields, with percentages of totals to be given if certain criteria are met, and need some guidance.
In column P i have a monetary figure.
...

**0**

votes

**1**answer

1k views

### How to specify dynamic tab in a sumifs formula

I'm trying to get the below SUMIFS formula to update the worksheet it looks at depending a month referenced in some other cell, say A1. I have sales data from each month in a separate worksheet so ...

**0**

votes

**1**answer

737 views

### Formula to copy a specific character from one cell to another

I am using MS query to pull in data. I need to create a column that copies the first character in another column. For example:
Cell A1 - E00633
Cell A2 - B00842
Cell B1 - I want this cell to ...

**0**

votes

**1**answer

304 views

### Can cell addresses (vs. cell contents) be used as operands in Excel formulae?

Consider the following scenario illustrating the data I have vs. the data I want. Very simply, I have a single column that represents Field 1, Contents of Field 1,...etc. and is more appropriately ...

**1**

vote

**1**answer

123 views

### Guidelines for developing an Excel grading sheet

I'm trying to develop a convenient solution for preparation of a summary sheet comprising of lots of grading sheet underneath it.
I have a tabular excel sheet used for obtaining feedback from random ...

**1**

vote

**1**answer

257 views

### Complex formula in Excel

I am trying to apply a complex formula in an entire Excel spreadsheet column.
What makes it complex, apart from the fact that I am not experienced in that level of Excel, is the fact that the formula ...

**0**

votes

**1**answer

166 views

### Easy MS Excel - MIN column with IF condition checking another column

I'm analyzing some personal expenditures for somebody on Excel.
I have a column for calendar dates and a column for expenditures on that date.
Of course, there are days when there are no expenditures ...

**0**

votes

**3**answers

466 views

### In excel, I need to compare the “rank” value of items in two sheets, and print the original rank in the new sheet

I've got two reports, pulled in as separate sheets in one excel file. This is the approach I came up with, but I don't know how to implement it:
For each cell in column "merchant_name" in sheet 1, ...

**1**

vote

**2**answers

145 views

### Subtracting percent of each cell from itself then subtracting a constant from each of those numbers and adding all of them together

I have an excel spreadsheet with several PayPal payements in a column. I want account for the paypal fee; which is 2.9%+.30cents per transaction. How do I do that?

**2**

votes

**2**answers

383 views

### Is this the best strategy for displaying a negative time value in Excel?

I am trying to send up a calculated cell as duedate - now() in order to quantify overdue vs. time remaining (negative values = overdue, positive values = time remaining). Consider:
Col A ...