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

learn more… | top users | synonyms (3)

2
votes
2answers
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
2answers
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
2answers
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
1answer
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
2answers
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
1answer
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
1answer
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
2answers
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
3answers
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
2answers
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
1answer
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
1answer
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
3answers
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
1answer
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
5answers
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
2answers
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
1answer
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
2answers
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
1answer
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
1answer
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
2answers
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
1answer
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
1answer
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
1answer
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
2answers
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
2answers
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
2answers
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
3answers
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
2answers
1k views

How is OFFSET supposed to work on filtered EXCEL data?

Consider: +---------------+ | Column A | +---+---------------+ | 1 | Milan | +---+---------------+ | 2 | Paris | +---+---------------+ | 3 | London | ...
0
votes
2answers
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
3answers
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
1answer
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
3answers
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
3answers
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
0answers
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
2answers
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
1answer
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
2answers
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
2answers
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
3answers
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
2answers
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
2answers
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 ...