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

learn more… | top users | synonyms (5)

0
votes
1answer
46 views

Count continuously until cell value changes

I have a work sheet with data like so: A A A B B C D D What I'd like to do is count each occurrence of A, B, and so on like so: A 1 A 2 A 3 B 1 B 2 C 1 D 1 D 2 Is ...
0
votes
1answer
32 views

how to filter “all rows where column x doesn't contain [0-9]”

I'm cleaning a dirty directory. A few (just a thousand or so) records have their postcode messed up, either blank or containing other address data. Usually that means city or county. A Postcode, ...
2
votes
2answers
24 views

How to sum two categories within a single column and show the totals to the side of the column?

I have a list of trailers with a column to the side that says if the trailer is full or a pup trailer. I can't figure out how to make the column total how many of each type of trailer and show the ...
-1
votes
1answer
33 views

Multiple IF AND statements excel

I want to know if it's possible to have multiple IF statements in one cell. What I am looking at is this. I have text in Columns A and B. What I want is to check whether Columns A and B match ...
1
vote
1answer
30 views

2010 Excel formula dilema [closed]

I am trying to set up an inventory spreadsheet using 3 worksheets. the first one has the items in inventory and their counts. the second sheet consists of the number of items used at an event which ...
2
votes
0answers
62 views

Excel “Smart” Dropdown / Data Validation

In Excel, is there a way to create a dropdown menu or data validation field that Allows the user to enter a value that is not currently on the list (like a combo box) Save that value in the dropdown ...
0
votes
0answers
29 views

Formula to reference a cell but not update with that cell

My workbook has data that is automatically loaded in from yahoo finance. The data is constantly being refreshed. I need a formula that can take the referenced cell and produce the number value of that ...
0
votes
0answers
37 views

Excel formula works on one sheet but not the other. Non-blank max number

I'm trying to calculate the max number between 2 non blank cells in column A, using the values in column B when B>C. There are no blanks in B:B. C is always blank unless C>B. ...
0
votes
0answers
18 views

Mode of text in excel

I have a spreedsheet with team numbers and some info about them (as text). Teams appear several time, and in a different sheet I have a summary for each team. I want to find the mode for one team. I ...
0
votes
1answer
29 views

Automated weighted counts in a Pivot Table (Excel)

I have data for sales people who work with different clients. Sometimes the sales person has multiple deals with the same client. I want to be able to work out the count for each sales person for each ...
0
votes
1answer
29 views

Complex coutif formula

I hava a dataset that on the headers has: (String Employee Name, String Day off, time Check-in, time Check-out). What I want to do is get a table that has row labels from Monday through Friday, and ...
0
votes
2answers
126 views

Excel Formula To Get LAST Non-Zero Value in Row and Return Column Header

I currently have a spreadsheet of transactions, and I want to write a formula that will help me extract from each row a customer's most recent transaction. Additionally, I want to then pull the ...
0
votes
1answer
46 views

Excel, pair 2 columns, then find all rows where column J is in two different Column D

SO I would like to have a formula or macro, to look at a table in Excel, pair two columns say J and M. Then find rows where M is connected to more then one value of Column J, Column J is Super Group, ...
1
vote
1answer
59 views

Align rows from different columns (which contain some cells that have identical values) using simple formula in Excel

I have 2 tables in Excel and I am trying (via Excel formula) to match/align some rows (ones that contain identical letters for the sake of the example included below). Btw, I don't mind aligning Table ...
1
vote
1answer
24 views

How do I match cells in one worksheet to another and print a result?

Not new to excel, but new to formulas. I have to say this is killing me. So what I am trying to do is take strings in one column, check if any of them are in another column of a separate worksheet, ...
3
votes
0answers
25 views

Nested IF function returning tiny value instead of zero [duplicate]

Whilst creating a balance sheet for a lotto syndicate, I encountered this phenomenon; Note the negative zero value in D5. With no formatting, its actual value is -0.0000000000000230926389122033 ...
-1
votes
1answer
50 views

Whats is the formula in Excel to display A2 if A1 =1

I need to display what is in cell A2 if A1 is 1. How do I do that?
0
votes
1answer
53 views

VLOOKUP where the column to look for is a drop down list?

I am doing a little format in which I call data from other cells, from other worksheet to fill it out. I am using VLOOKUP to do that. So as an example I have: Name: (Drop down list) Sex ...
0
votes
0answers
36 views

Excel performances: INDIRECT+ADDRESS VS CELL+OFFSET

Using Excel I can achieve the same result using two different approaches, here are the formulas: A) =INDIRECT(ADDRESS(5+MATCH($A$16,Aliquote!$A$5:$A$15,0),1,1,1,"Aliquote")) B) ...
0
votes
1answer
56 views

Excel max number between 2 non-blank cells A, in column B (given no blanks in B) [closed]

I need to calculate the maximum number between 2 consecutive non-blank cells in A, but using the values in B. ie. use a formula in column C that will give me the results I manually plotted in D. ...
0
votes
1answer
35 views

How can i create click function between A1 to A10 prompt inputbox for user to enter numbers in vba

I am trying to create input box for selecting range (A1 to A10), every time when i click any cells between 1 to 10, i want to prompt input box to enter numbers. That's what i did but it's not working ...
0
votes
0answers
30 views

How to combine two sheets based on their dates?

I have two sheets: one is stock index, one is air pollution value. I want to match the air pollution value to each trading day. How can I do it on Excel? the first sheet looks like: 01/01/1997 39 ...
0
votes
1answer
52 views

Function excel for split a cell, vlookup into a column and concatena

I've one range with the value in column 1 and I want to look for the cells in Column 2 if they are in the range of col 1. When they are in the range I want the function to give me the value is col 2 ...
3
votes
2answers
64 views

Round cell values on end of edit in Excel

I want to build a worksheet that when numbers are entered into a cell and the user ends the edit on that cell they are automatically rounded to the nearest 0.25. I do not want to have two columns, one ...
-1
votes
1answer
39 views

Formula issue Microsoft Excel

I need help urgently for a work problem. So basically, the summation of opening stock and production give total stock. While closing stock is ((opening stock+production)-sale)) and stock cover is ...
1
vote
1answer
21 views

Count number of cells that match certain criteria with formulae

Suppose I have column with dates: 2015-01-10 2015-08-12 2014-01-02 2015-01-28 2016-10-14 I want to count how many dates fall to January. I would supopse that formulae is like this ...
0
votes
1answer
21 views

Convert string Jan to January, Feb to February, Mar to March etc

I would like to be able to convert the string value Jan to January, Feb to February etc but I don't know how. I have a table, populated some how, with the months, which looks like ...
0
votes
0answers
22 views

All Data Option Filter Validation

I am creating a dashboard that has five inputs that filters my data by the custom data validation combination. My dashboard updates correctly based on the custom combinations, but I am unable to add a ...
1
vote
1answer
60 views

Copying cells to another sheet and inserting rows for the copied cells

I am trying to create a sub that copies information from one form (which has four cells) to another sheet. When it copies the information it also creates a new row. Each form has a maximum of ten ...
0
votes
1answer
24 views

How to compare differences for multiple possible pairs of cells in Excel

In Excel, I have 7 columns of data (numbers) spanning 100 rows. For each row, I want to check whether at least one number exceeds all other values by a certain value (say 0.2 or greater). If so, the ...
0
votes
1answer
65 views

How to use INDIRECT with a SUMIF formula for a date range

I'm trying to get a SUMIFS formula to dynamically work with multiple tabs in an excel workbook. I have numerous tabs in this workbook, each that contains client data I update daily. In the example ...
1
vote
2answers
40 views

Excel Formula difference between two dates

I have two dates as the following , I want to get the duration spent between them. 6/29/2015 12:18:04 PM Regards,
1
vote
1answer
82 views

Excel: Write a 1 or 0 value if condition is met

Sorry if this has already been posted, but I just started today playing with Excel macros and I need a hand. I have a data column with values and I would like that if the conditions were met, the ...
1
vote
1answer
49 views

Excel: Is it possible to copy cells only if cells match in other columns?

If it's an EXACT duplicate, not just some answer that MAY suggest an answer then please provide the link in a comment. Say, I have dates in column A of Sheet-1, and also dates in column A of Sheet-2. ...
0
votes
1answer
21 views

How to copy tab-contained text from Excel without quotes?

I've noticed that a cell that contains a tab is copied with quotes embedded. I'm looking for copying such kind of a cell without the quotes. Illustrative Example I've 5 cells; A1, A2, A3, A4, and ...
0
votes
1answer
21 views

Excel Formula change and remove

The numbers on the form are for two different references. 2015-##### (five digits after year) is the way it will be entered. When the user enters that I would like to enter in another cell the result ...
0
votes
0answers
20 views

Using Hlookup to populate a worksheet from a data sheet

I am trying to develop a worksheet that requires a list of equipment based on an area header. I would like to be able to pull the separate lists based on this area header. Here is sample data I am ...
0
votes
1answer
32 views

Google Spreadsheet =Filter in excel

I have this formula in google spreadsheet that uses the =FILTER functionality but I can't seem to find the corresponding syntax in excel. Specifically the formula is as follows: ...
2
votes
2answers
53 views

Need a formula to check the first x amount of characters if numbers

Is there a formula to check if the first 7 characters in the cell are numbers?
0
votes
1answer
55 views

Is there a way to exclude keywords in a VLOOKUP formula?

I have 3 sheets in Excel. In the 1st one there is a column with hotel names and another one with ID. I want to get that ID from the ID column on the 2nd sheet where I have the names as well. The ...
2
votes
4answers
67 views

How to apply a formula to an entire Excel column using a keyboard missing its END key

I have a keyboard with a trackpad that's missing some of the normal keys available on a full-sized keyboard (among them Home and End). Also, there are 30,000 rows in the sheet and dragging the corner ...
0
votes
2answers
289 views

How can I compare two columns and if match return a value?

How Can I compare the entries on TAB: SUMMARY SLS, column B with SUMMARY P&S, column B and bring back a "X" if a match is found in the cell with the formula? SUMMARY SLS and SUMMARY P&S are ...
0
votes
1answer
29 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
68 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
51 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
117 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
30 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
42 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
74 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
22 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 ...