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

learn more… | top users | synonyms (5)

2
votes
1answer
16 views

How to find and replace the character “*” in excel text values in cell, but NOT formulas

Very similar to this question: How to find and replace the character "*" in excel But I need to leave formulas untouched. I've got about 50+ sheets that have two types of cells with "*" ...
-4
votes
0answers
28 views

Can someone help me decipher this formula. It deals with a life insurance policy where there is an annualized cash value interest rate of 4.00% [on hold]

Here is the formula the "administrator" of the policy gave. (((1+i)^(m/12))-1)*P They gave only the following information. i=interest rate to yield m=number of months p=principal Background - The ...
-2
votes
0answers
20 views

Excel: How do I use multiple (3) cells to retrieve a value from the fourth column in a table? [on hold]

I am creating a worksheet where I need to use the values in three cells (K9, L12, and L13) in one workbook to retrieve a value from a multiple row and multiple column table in a different workbook. ...
0
votes
0answers
17 views

Populate information from on spreadsheet to another on same excel File

I am creating two sheets for one excel file for business conferences . One sheet will be an inventory list of items being sent to conferences, the other a sheet that contains details of the ...
0
votes
1answer
18 views

Count matches between two arrays

How can I count the number of entries two arrays have in common, using just standard excel formulae (i.e. no VBA)? Can't find an answer anywhere. e.g. A B +------------- 1| 1 10 2| ...
0
votes
0answers
19 views

Excel: Storing a Path to Access a Closed Workbook

I have a workbook (let's call it A) which accesses another workbook (let's call it B) which is closed. From A, I need to access data on B which may be on different worksheets. However, B contains a ...
3
votes
1answer
19 views

Checking a range of dates

I would like a formula that will return Yes only if a range of cells in the same row all have dates in the future, and if five other cells in the same row have the value "Yes". I tried to just check ...
0
votes
2answers
26 views

How do I get nothing to print when using ISBLANK nested formulas?

I have 6 columns and 3 equations : E*F=G, H*G=I & G+I=J I don't want zeros to print if a cell in F or H is empty. Currently I have these equations and everything is dependent on the F & H ...
1
vote
4answers
34 views

Adding Quotations and Brackets to format of cell conditionally

I have three columns: Word | Formatted Word | Type Chicken |................| Noun Running |................| Verb If the word in A2 is a Noun, I would like B2, the formatted word, to look like ...
0
votes
1answer
11 views

Nested Ifs for personal accounting sheet

I have a personal accounting sheet with 5 columns. C is Debit, D is Credit and E is Balance. I want to do 3 things but I've been confusing myself for the past hour. If C5 and D5 are both blank, ...
0
votes
0answers
23 views

ISO Weeknumber formula explanation

can anyone breakdown this ISOWeekNumber formula and explain to me in detail how it formula works? =INT((A1-WEEKDAY(A1,2)-DATE(YEAR(A1+4-WEEKDAY(A1,2)),1,4))/7)+2 and this one? ...
-2
votes
0answers
22 views

Formula wanted please [on hold]

A B C 705513390 AB-BC-123 10 705513390 AB-BC-123 10 705513390 BBBB-123 5 I need a formula that will return column C based on a search for AB-BC or BBBB and also ...
3
votes
1answer
38 views

Excel — Tell whether a string contains any value in a table column

Background I'm using Excel 2013. I have an Excel table named SiteCategories, with a number of Category columns, and then sites in those columns, like so: So we could get to those values using ...
1
vote
3answers
34 views

How to find average of first difference in one set of column excel/google docs?

I have one column with numbers eg. 130, 131, 135, 140, 120.., I would like to find the differences eg. 131-130, 135-131, 140-135.. and find their average without using another column to find the ...
0
votes
1answer
23 views

Excel 2010 VBA to remove duplicates and then sumif base on the unique data

My work requires data comparison from 2 different sources (sheet 1 is source 1 and sheet 2 is source 2). Both sheets contain duplicate values (the Delivery Order) Because I need to do this kind of ...
0
votes
1answer
10 views

Share Excel “Template Formulas” between sheets

I have a spreadsheet that takes user inputted values and performs some fixed computations to generate a number of cells. I plan to use these computations in a number of sheets with different user ...
0
votes
0answers
18 views

3 conditions in a nested Excel formula

I am using 3 conditions in a nested formula, each condition has a number of variants. So far =IF(I14=60,IF(AND(H10<10,D75=1),0.5,IF(I14=60,IF(AND(H10<30,D75=1),1, and so on. This works ...
0
votes
1answer
19 views

Look up specific string in row (not column) and return value of cell x columns left of that location

My data looks like this: Sheet 1: A B C D E F 1 2 3 a b c 1 2 3 a b c 1 2 3 a b c Sheet 2: A B a b c Now on Sheet 2 in Column B I would like to return the result of: look up each value of ...
3
votes
1answer
26 views

Varying Format Part Number Sort Issue

(Current Sort Sample:) 2-1203-4 2-1206-3 2CM- 3-1610-1 3-999 … AR3021-A-7802 AR3021-A-7802-1 B43570- B43570-3 I am working on an 8000+ record parts list. The challenge I am running into is that ...
1
vote
3answers
35 views

How do I use the AND function and the MATCH function together to get something that is not “No” and has not been returned previously

I have the following data and need to return all values that aren't "No".  I.e., I want to create, in another column, a list of all the values from Column E that aren't "No".  There will never be ...
0
votes
1answer
27 views

Check which (multiple) cells in column A contain substrings specified in column B?

In column A I have M&A deal descriptions that mention company names. In column B I have company names, for each of which I want to see where it is mentioned in column A. What I'm having trouble ...
-4
votes
0answers
19 views

I want to develop the splitting one row into multiple rows in Excel

You provided an excellent answer on how to convert Excel data in rows into a vertical column or series of columns. The page is: How do I split one row into multiple rows with Excel? In that ...
0
votes
1answer
32 views

Autofill formula to return data from every tenth row

In Excel 2007, how do I autofill a function of =OFFSET(C2,10,0) to end up as: =OFFSET(C2,10,0) =OFFSET(C12,10,0) =OFFSET(C22,10,0) etc. as a pattern of referencing every 10th cell in the same row. ...
3
votes
1answer
35 views

Excel date month issue

I am working on projects that come in every 4 and a half month. I want to make a calender so I can see when the deadline is for each project. I have a formula that adds up 4 months and 14 days. (4,5 ...
0
votes
1answer
33 views

How to to divide column containing formulas and hard-coded strings into two separate columns?

I have a column that consists of hard-coded cells as well as formulas. Based on whether a specific cell is either hard-coded or contains a formula, I want to display it in two separate new columns. ...
1
vote
2answers
24 views

Returning a blank cell in MS Excel formula if 2 source cells are blank

My formula in B3 is =A3+B2-B1. I want B3 to be blank if both B2 and B1 are blank also. Could anyone help me with this please?
0
votes
1answer
26 views

Merging five colums but do not merge if row contains two values

I have 5 columns all with numeric data in. R S T U V The first three columns can be seen here so you get the idea. I want to merge them all into the first column "R" - in the vast majority of ...
0
votes
2answers
57 views

How to lookup a value and return multiple unique values to a single cell - comma separated

I am attempting to lookup a unique identifier (Product Number) which is held in several different stock locations. For example: __A__ __B__ __C__ 1 Item # Warehouse ...
-1
votes
1answer
37 views

If(ISERROR(INDEX combination produces “You've entered too many arguments for this function” error [on hold]

I have two working formulas and I want to combine them both: This identifies a unique item number in a list =IFERROR(INDEX($B$3:$B$16, MATCH(0,COUNTIF($E2:E$2, $B$3:$B$16), 0)),"") This ...
0
votes
1answer
23 views

Sum of Excel Range Between Dates

So I've struggled with this problem several times and usually just end up giving up on it, but this time I want to solve the problem once and for all. I have a straightforward table as follows: Date ...
0
votes
2answers
24 views

Fill one cell depending on value of another

I'm trying to create a Risk Assessment sheet in Excel. I have one drop-down with days at the moment and I want another cell to be populated depending on what day is selected. I have managed to get ...
0
votes
1answer
25 views

Excel formula related to conditional formatting

Column A has 10 numbers expressed as percentages and is conditionally formatted to highlight the top 40%. I want column B to show a "1" next to the corresponding highlighted top 40% of numbers in ...
1
vote
1answer
17 views

How do I take the information from 1 spreadsheet and count it on another spreadsheet based on a set range of values?

I have 2 spreadsheets in the same work book. On the first, I have dates names and others values (y/n, amounts). I need to be able to put in a date range on the second spreadsheet and have it take data ...
-1
votes
0answers
21 views

Why doesn't my formula doesn't copy until the end of an Excel table?

When changing the formula in an Excel table it doesn't copy all the way until the end of the column. I copied information from different spreadsheets to create a new one combining information from all ...
3
votes
1answer
77 views

Extract number from complex string if it falls in a given range

I need an Excel formula (or VBA macro) that will allow me to extract a value from a string. The string is a sequence of words, separated by spaces, in a single cell. I want the word (representing a ...
0
votes
1answer
26 views

VLOOKUP returning N/A because references contain “.” and “ ” in one table, not in other

OK I am trying to vlookup a table but the problem is the references on one table are slightly different to the references on the other table. I can get the VLOOKUP function working, but it is ...
0
votes
1answer
22 views

How to find number ranges (with text input) in Excel

In column A, I have some numbers, formatted as text due to leading zeros: +------+ | A | +------+ | 0001 | | 0002 | | 0003 | | 0012 | | 0013 | | 0015 | | 0017 | | 0019 | | 0020 | +------+ Using ...
3
votes
1answer
68 views

INDEX + MATCH function returning wrong values

I have a spreadsheet in Excel 2013 that uses an INDEX and MATCH function to search by an interval time (in I4) to pull values from the corresponding row where it is matched (in column A). It works as ...
0
votes
0answers
18 views

Sorting data by association?

That title may have been misleading, I don't know quite how to put it. I have a set of data where a number stands for a description. It currently looks like this: 10 Blue 11 Green 12 Purple 13 Red 14 ...
0
votes
2answers
39 views

IF statement with multiple criteria and multiple possible results

I have a spreadsheet and I need a formula to give me one of three possible results (good, neutral, bad) based on data from two cells. MTD and YTD on budget would be good, MTD on budget & YTD below ...
0
votes
2answers
40 views

Fill cell with sheet name Microsoft Excel

I have an excel file which has around 100 sheets in it. Each sheet is for a student, and each sheet name is the firstname_lastname e.g Mark_Hope so what I want to do is that in each sheet there are ...
0
votes
2answers
23 views

How do instruct a cell to display a minimum value?

I currently have the following formula in a cell:- =CEILING(E9*0.85, 1.25) I would like the cell containing this formula to display a value no less than 20 but only when the value of E9 causes ...
-1
votes
1answer
73 views

Calculating Stock Profit/Loss in Excel

I'm trying to create simple spreadsheet to calculate profit/loss after every complete transaction I perform on the stock market. This way I'll be able to track my portfolio performance at any moment ...
1
vote
1answer
29 views

Compare efficiency of excel formulas

Is there any way to directly compare for calculation efficiency side by side two different formulas for achieving the same goal in excel? Say I want to know which is more processor intensive (or if ...
0
votes
1answer
28 views

Insert pop out table into excel cell?

Is it possible to create a pop out table/dialog in a clickable cell in Excel 2010? I would then like the sum of the table to show up in the cell. Right now I am using a simple workaround, wherein I ...
0
votes
1answer
35 views

How do I set a dropdown in excel to populate different data in cells below based upon the option selected from the dropdown?

I am attempting to clean up a spreadsheet that we have that tells us what permissions to grant for certain roles in our ticketing system. I have used the Data Validation tool to create my dropdown ...
0
votes
0answers
10 views

Google Sheets: How to print information of user that is printing [migrated]

Well, I am creating a sheet on Google Sheets and I will share it with some people. The users online can see its contents, but, to maintain the data update information when someone prints that, I ...
0
votes
1answer
57 views

How can I pull specific rows in Excel into a new spreadsheet

I have several spreadsheets containing data I need to analyse. I have a randomized list of numbers that dictate which rows in each spreadsheet I need to code for my data sample. Is there a formula I ...
0
votes
0answers
8 views

Spreadsheet: Normalising multiple irregularly time-stamped data from sensors only roughly in sinc

I use the latest version of open office on a windows vista pc. I have .csv barometric data logged from two different sensors (pieces of equipment), that report when changes occur rather than at set ...
-1
votes
1answer
43 views

Show value from every sixth column with a formula that can be filled down a column

I have two Excel sheets. On Sheet A, I have several columns of data, and the average of each column is in row 41. On Sheet B, I would like to have a column that shows the averages from Sheet A. The ...