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

learn more… | top users | synonyms (5)

0
votes
1answer
31 views

Find cell address for MAX+IF function based on category

I have a large set of data, which has been divided into 9 categories using numbers 1-9. The data is not sorted by category, but the categories are defined in a column as data. Using MAX+IF, I have a ...
0
votes
2answers
72 views

Assign category to data based on range

In Excel, how would I take numbers from one column and assign them to a category in another? E.g., if I set ranges that correspond to certain words 1-10=Beginner, 11-20=Intermediate, etc. How would I ...
1
vote
1answer
52 views

If statement that returns original function if false (similar to iferror, or substitute)

If I use IFERROR as opposed to IF(ISERROR I can save typing the condition twice. i.e. =IFERROR(Long lookup,"not found") is much preferable to =IF(ISERROR(long_lookup),"not found",long_lookup) ...
1
vote
1answer
120 views

Compare Two Dates and If Less than TODAY Add Date till TODAY and Add 6 Months to it [closed]

I have data like this in Column H 6-Feb-14 5-Oct-14 27-Dec-14 5-Feb-15 17-Feb-15 2-Apr-15 I'm trying to add 6 months to each date with the date being up to today. Like if ...
1
vote
1answer
33 views

fit a table in a middle of an excel sheet

I have a table in a sheet like this: I want to place the table in the middle of page. but the number of column is odd (5) and the number of column in the sheet is even (8). there is 2 cols after ...
-1
votes
1answer
43 views

BOM comparison sheet

I have been working on Comparison sheet, where i need to compare Base BOM1 with other BOMS. I added formula & prepared the attached sheet & I am able to highlight the mismatch values. But i ...
0
votes
2answers
78 views

Populate missing entries from one sheet to another

I have two Sheets in Excel. For this example, Sheet 1 and Sheet 2. Sheet 1: 100 records with Name, Location, Gender Sheet 2: 100 records with Name, Location, Age The sources of the data are not ...
0
votes
0answers
23 views

Search for ID column in all worksheets and then search within those columns

I have multiple workbooks with multiple sheets on each. I am looking for a way to return the value under the column ID#, like to search all the sheets in the workbook. The header ID# might be in a ...
0
votes
1answer
44 views

Modify Concatenate Formula to add an additional column (Mac Excel 2011)

I would like to modify this formula to ADD another column: =IF(LEN(AK2),AK$1&": "&AK2,"")&IF(AND(LEN(AK2&AL2)>0,COUNTA(AK2:AL2)=2)," _ & ","")&IF(LEN(AL2),AL$1&": ...
1
vote
0answers
26 views

How to quickly reference the same cell across different sheets [duplicate]

I have 60 Sheets with the same information but for different employees. I was wanting to create a summary sheet but without manually linking. In the summary sheet, I want cell A6 from each sheet to ...
0
votes
1answer
43 views

Excel formula for checking if a string is a substring of another string and if so, creating a hyperlink

So I've been working on this problem at my office now for awhile. I have an excel file with a few thousand lines representing files that the office has either opened, scanned in, closed or destroyed. ...
0
votes
0answers
43 views

Lookup ProductID on another sheet and return the lowest cost and its column header

I have recently been working with formulas and larger amounts of data than usual and need a little help. What I am trying to accomplish is the following. I have two workbooks (which I could make ...
0
votes
1answer
50 views

Excel formula from VBA difficulty

I have a formula that I want to generate in VBA and put in a cell. The final formula is: =IF(ISNA(MATCH(B6670,ProcMIRs!B1:B235,0)),"","Found in ProcMIRs row " & MATCH(B6670,ProcMIRs!B1:B235,0)) ...
0
votes
2answers
54 views

Make formula depend on key word entered in another cell

I would like to know how to get a set of cells to auto generate a specific date when another cell has been entered with one of four specific key words. (Key words: AFAM, AFCM, MSM, MSM/FW) Ex. If ...
0
votes
3answers
53 views

Adding a date to a formula in excel [closed]

=$M2="COMPLETED" I am using this formula to shade a row when COMPLETED is written in column M, but I would prefer the 'date' or 'COMPLETED and the date' but can't seem to work out how to do this. ...
0
votes
1answer
19 views

COUNTIF/COUNT Function help required to count the filtered data for the subsequent columns

I am using a normal COUNTIF Function to know the count of "Box 9" I have placed this in one cell "I12" and i am using the formula. I am filtering the data with the column J. 1) ...
0
votes
1answer
22 views

Excel 2010 Array (CSE) Formula: How to remove a part of contiguous (adjacent) range

I would really appreciate help on the following question: Is there a way to write a formula that removes a segment from a contiguous range? For example, say there is a formula ...
-1
votes
1answer
38 views

Excel formula to Show (1,0) for specific ranges

The problem I am having is I only want to look at Order number(Column A) if they have more than 4 value. When they have 4 values I want to only look at the last 4 values in that order. In column D I ...
1
vote
1answer
26 views

How do I create a summary of the same few cells on many different sheets in Excel 2013?

I have an Excel workbook with 140 sheets. I'm trying to summarize the same 3 cells in each sheet on a summary sheet. How do I grab the value of the respective cells on each sheet without manually ...
1
vote
2answers
68 views

Excel 2010 Array Formula (CSE) Dynamic cell range/reference; index? offset?

This might be an easy question to some but I am completely lost so I would really appreciate any help out there! A 1 10 2 20 3 30 4 40 5 50 6 60 7 70 ...
0
votes
1answer
15 views

Counting in Excel until a special integer comes up [closed]

I am trying to get column C to say what column D says (I have typed the value in column D). The problem I am having is that I can not find a way to tell Excel to stop counting when it comes across the ...
-1
votes
1answer
63 views

Formatting - conditional - vlookup- values from one cell to another different worksheets based on value

I would like to be able to enter a part# in column L of sheet 2 and a formula would look through column A of sheet 1 for a match, and then return their price from column F of sheet 1 into column K of ...
1
vote
2answers
71 views

openoffice calc: sum of a function of elements of a row

If I want to sum the elements of the first row (from the column a to x) of a calc document I can use =sum(a1:x1). My question is about the possibility to calculate something like this ...
0
votes
1answer
76 views

How do I Reference all non-blank cells after row A6 for a Formula

I want to use the following formula: INDEX(A6:A94,MODE(MATCH(A6:A94,A6:A94,0))) Except the exact number of rows is ambiguous (imported using VBA). How can I reference all non-blank cells starting ...
0
votes
0answers
33 views

Can someone help me with an excel function that would make merging sheets easier?

I have about 61 spreadsheets that I want to merge into one big workbook. Int the one work book I have tabs that are named the same as the spreadsheets that I want to import the data into. There ...
0
votes
2answers
45 views

Excel function to find the last three items by date

I am looking to obtain the 3 most recent transactions in relation to the date.  (The problem is that they are in the spreadsheet out of order, and I don't want to do a sort.) Date Amount ...
0
votes
2answers
54 views

Prevent formula input in all cells in a column, but allow whole number input

I am in a bit of a sticky situation. There is a public spreadsheet that some of my coworkers are required to use for their job. They all collectively use it during their shifts to track product ...
0
votes
1answer
56 views

Why is this IF, OR, AND function not returning TRUE?

Why is this IF, OR, AND function not returning TRUE in A848? See image below to view screen shot. I'm using this algorithm to search for " not " and " valid", "not valid", and "invalid" in the ...
1
vote
1answer
60 views

Work out Number of hours still to Complete [closed]

I have a spreadsheet to keep track of training which I do. As part of the requirement, I have been given a set number of hours to complete per month (20 hours). The formula I require is to work out ...
1
vote
1answer
92 views

Formula to show date of the last day of the month specified in another column in Excel

For a month number in column A, I want the LAST day of that particular month in column B. Right now, I'm working with year 2014, so: A1 number is 1 so B1 date is 01/31/14 A2 number is 2 so B2 date ...
0
votes
1answer
24 views

Recurrent excel count

Is there a more elegant way to do this in excel? =(IF(ISBLANK(O3); 0; O3-P3) + IF(ISBLANK(O4); 0; O4-P4) + IF(ISBLANK(O5); 0; O5-P5) + IF(ISBLANK(O6); 0; O6-P6) + IF(ISBLANK(O7); 0; O7-P7) + ...
1
vote
3answers
53 views

Excel 2010: count if using a formula

I've a table with actual stock in column C goods in arrival in column D auto-reorder in column F I need to count the number of rows when (C+D+F) > 0. Every row must be counted only one time and ...
1
vote
3answers
69 views

AVERAGEIFS with OR conditions

I've got a series of blood pressure measurements. I want to determine the average pressures during the day and during the night. The first half of the problem is trivial: ...
0
votes
4answers
166 views

Excel: Sum values on a column based on criteria* on other columns *(complex criteria)

See below for rewrite. Please refer to the data below for the question. The very top A B C are columns and the far left numbers 1 through 15 are rows. The rest are cells. A B ...
0
votes
1answer
65 views

Obtain a row number from 2 criteria (with 1 fuzzy search)

I am attempting to create some graphs with dynamic cell references as indices. These graphs are based upon time stamps generated from a testing machine. I have a column of Boolean values (0 or 1) that ...
0
votes
1answer
28 views

EXCEL: Icon Conditional Formatting Based on Range of Values

I am creating a spreadsheet for work and I need to format cells such that I get a green check mark when the value in the cell is between 100 and 101, yellow ! if it is between 97 and 100, and red X if ...
1
vote
3answers
61 views

Writing excel formulae that reference columns by number rather than letter

Is it possible, and if so what is the syntax, to write formulae in Excel using the column's integer number rather than the lettering system that is the default? I know it is possible to change the ...
1
vote
1answer
42 views

Getting a webpage title into Excel

I'm attempting to create a cell that will pull in a hyperlink based off of what is filled out in another excel cell. I have the hyperlink portion working, but I'd like to have a better label for the ...
1
vote
2answers
52 views

DATEVALUE() gives different values for similar data

In one column, I have timestamps in the format Jun 10, 2015 11:36:51 AM So, to get the date from that column, I created another column and used the formula =DATEVALUE(CONCATENATE(MID(D2,5,2)," ...
2
votes
1answer
39 views

Not getting desired output with VLOOKUP - Need assistance

I have Table 1, which looks like this - A1 B1 C1 1 X Date11 2 Y Date12 3 Z Date13 I have another table, Table 2, that looks like this - A2 B2 C2 1 M Date21 2 N Date22 3 X ...
1
vote
1answer
47 views

Excel - how do I retrieve corresponding values from another worksheet?

I have a "DETAIL" worksheet which tallies numbers: DETAIL WORKSHEET (Column A) (Column E) (Excel Row #) DATE TOTAL ... 700 6/8/2015 =sum(D701:D709) 701 702 703 ...
0
votes
0answers
11 views

Matching data between two separate worksheets and transposing the data from another column next to the matched data

I have two worksheets. I am trying to input the stock number next to the matched claim number. How would I go about doing this? The first sheet is my inventory list. The second is a list we get from ...
1
vote
1answer
89 views

How to hide formula results in a cell until conditions are met - percentage discount [duplicate]

My question relates to this post, but with different formula criteria: How to hide formula results until conditions are met. Here's the setup for a spreadsheet to calculate the percentage discount ...
1
vote
2answers
68 views

Is there an excel function which displays a string but outputs a value when referenced?

Let's say if the function called "LABEL". Then in cell A1 I would enter =LABEL(4,"Four Apples") and it would render as: Four Apples But if I performed a calculation, say =A1+2 I would get 6, not ...
-2
votes
1answer
32 views

Need to sum values associated with specific words, in Excel [duplicate]

I want to sum values associated with specific words. For example, calculate Sum for Food: Column A Column B Food $8.50 Food $5.10 Maintenance $12.00 Laundry ...
0
votes
0answers
26 views

Microsoft Excel 2011 concatenate each row in a cell

I am trying to use an if function for each row for a result in a single/destination cell. The if function checks if a row meets a criteria, if it does, it adds the row details to the destination cell ...
0
votes
1answer
45 views

VBA Code Isn't “holding” ThisWorksheet Parameter

I want to retrieve one column of values from another workbook and populate a tab in my current workbook. This will ultimately go into a much longer module I've written that works great. I just wanted ...
0
votes
1answer
50 views

Excel 2013 - Referencing multiple checkboxes on a different sheet

Good morning, Superuser! I am facing a pretty tough problem at work this morning...my supervisor wants me to do something I have never done before, nor can I find a solution online. The spreadsheet ...
0
votes
1answer
37 views

What formula would you use to add up a column based on results from two drop down menus? [duplicate]

I would like to calculate a column based on selections from two other columns with drop down menus. For example : In one column, "C" you have 3 options (A team,B team,C team), In column "F" you ...
0
votes
1answer
46 views

Want to get the unique combination of data for dynamic number of rows/columns

I have a few rows (the number of rows should be dynamic) of data that I would like to combine, and get each unique combination. I am able to do that for a specific number of rows of data, but when the ...