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

**0**

votes

**1**answer

22 views

### excel - inserting a value based on a range of values

I need to link a batch code to a specific unit number. The batch codes are the same for a range of unit numbers. The first table below shows the unit numbers; the “?” marks are where I need to enter ...

**2**

votes

**2**answers

28 views

### dragging to populate cells in Excel

I am aware that you can fill the cells in Excel as a series by dragging the square in the bottom right corner.
for example typing "1" in Cell "A1" and dragging the box down to "A10" will give you a ...

**1**

vote

**3**answers

49 views

### Is there a quicker way to enter formulas?

In the firm I currently work in we have a very inefficient and time-consuming process for entering formulas. For example:
Y1=CHOOSE(B3,'[Flacflow balancesflacflow.xlsx]Data'!$A$77, '[Flacflow ...

**1**

vote

**0**answers

31 views

### Excel: Keeping references/links when transposing

I have a worksheet in where I have table (let's call it the summary table) with references to a another table (among others) which is 2x40 (rows x columns). I would like to tranpose this into a 40x2 ...

**0**

votes

**1**answer

31 views

### Copy the content of one cell, and insert within 2 characters, in the middle of a text content from another cell (Excel)

I am trying to batch copy the content of the cells in column A, and insert it within the "><" of the text in the column B. The result should look like the output in column C. Here is an example:
I ...

**0**

votes

**1**answer

155 views

### Get the index of selected value in Excel drop down

I have a drop down list generated in Excel from row values. I want to select from that drop down, get the index of that value in the drop down array, then use that for an offset value to select the ...

**0**

votes

**0**answers

152 views

### Return value from table using Multi-dependent drop-down list

I am trying to obtain the value of a column header (or topmost row value for a specific column) based on the selection of properties from 2 drop-down lists.
...

**0**

votes

**1**answer

46 views

### Hide sheets in shared excel workbook.

I have a shared workbook on which I have hidden some of the worksheets prior to saving.
I have closed the workbook. If another user opens the workbook the worksheets I had hidden are visible. If I ...

**-1**

votes

**1**answer

32 views

### Excel searching column and copying the cell next to it

i need to find the value from column C in the row B, and copy the value from found result in column D. If it's possible with formula and to copy formula to all cells in column D ? without VB?
Thanks ...

**1**

vote

**0**answers

23 views

### Naming worksheets automatically [closed]

I have a spreadsheet that I input data into everyday of every month; each worksheet represents one day of the current month. I have all the formulas I need but each month I have to change the ...

**0**

votes

**1**answer

69 views

### Excel Formula Challenge

I have a fairly specific Excel problem and I am hoping someone is savvy enough to come up with a working formula for me. I am using Excel 2011 for Mac btw.
So, I have a list of about 134 ISBNS, for ...

**0**

votes

**1**answer

26 views

### count number of cells that meet certain criteria between two other cells with certain criteria

I am looking for a way to count the number of cells meeting a specific criteria that are located between two other cells. For example:
A B C D E
151867377 01:03.5 ...

**0**

votes

**1**answer

10 views

### Cell to relate to information depending on infrmation in another cell

I hope you can understand what I mean. I have tried to draw below to help explain.
I need column d to have the information in column a1 and column h to have the information in a2- Keeping it in the ...

**0**

votes

**3**answers

29 views

### Is there a way to combine duplicates within a large range in Excel?

I have a large (1.7 million rows) table of sales data on a sheet in Excel, many entries of which are identical other than a unique ID and a timestamp. I’d like to consolidate this data by combining ...

**0**

votes

**1**answer

17 views

### I am trying to match based on a dynamic cell reference with the match function

=MATCH(B$4,CONCATENATE("'",A5,"'!$E:$E"),0)
The above formula result is #VALUE!
Cell A5 has the value Sheet1, however, after testing this will change to a date e.g. 01-Jul-14
What am I doing ...

**0**

votes

**2**answers

44 views

### Excel - Formula to get total number of worksheets

I have 100+ sheets, it is hard for me to count them one by one.
Is there a one line formula to get the total number of sheets? The CELL or ADDRESS function perhaps?
I remember using something like ...

**0**

votes

**1**answer

574 views

### SUMIFS using “greater than or equal to” with text values

Using the following simplified example, I want to calculate the sum where Category="Graduation" and Date >= 1986-05-01.
Category Date Count
Graduation 1985-06-15 1
Graduation 1985-09-12 2
...

**3**

votes

**1**answer

135 views

### Excel 2010: Formula for Sum and Return Date

I have a workbook with a Summary sheet and a Data Set sheet with a linked table containing 700k+ records of financial data on investments. (In my particular case, it is real estate properties; but ...

**2**

votes

**1**answer

75 views

### How do I convert dates from Open Office format to MS Excel format

I am trying to save a file in Libre Office as an excel file but Excel sees the data displays it as a negative number using a series of ###### symbols.
I believe the problem is to do with the two ...

**0**

votes

**1**answer

27 views

### Combining IF and SIGN functions in Excel

So I'm trying to do something in Excel, in our lecture notes the teacher uses it, without explaining how.
For reference, this is the bisection method to find roots of an equation.
So I have 4 ...

**0**

votes

**1**answer

123 views

### Pie Radar Chart outline series not working

I have been trying to create a Pie Radar Chart as described in answer to this question.
Everything works until I get to the section that describes inserting a series to create the chart's outline:
...

**0**

votes

**1**answer

121 views

### Microsoft Excel 2007 - Drop down list that grabs multiple columns from another sheet

I've been scouring the internet for this but can't seem to find a solution.
Let's say I have 2 Sheets - "Items" and "Inventory".
Inside "Items", I have 3 columns:
Item Name
Item Type
Item Cost
...

**1**

vote

**3**answers

67 views

### Excel 2010: Calculate full months between dates

I have dates in two columns and I would need to calculate amount of full months between two dates.
I export the spreadsheet from SAP ERP in .xls format but opening with Excel 2010. DATEDIF function ...

**0**

votes

**2**answers

177 views

### Calculate number of Sundays in the month

In Microsoft Excel I am looking to determine the number of Sundays in any given month. I am putting together a budget spreadsheet and some of the sub accounts get extra funding when five Sundays are ...

**0**

votes

**2**answers

125 views

### Excel - Include cell in column count if previous cell in row contains certain text

I have searched for this but I have not had much luck. What I would like to do is below:
A B C D E
1 ob ob
2 ACR ob
3 ob
4 ACR ...

**0**

votes

**1**answer

43 views

### Edit current cell via keyboard?

In Excel for Macintosh 2011, when the cursor is in a particular cell of a spreadsheet and you wish for the focus to move to the edit line for that cell where the formula is displayed, is there a ...

**0**

votes

**1**answer

98 views

### Formula to find items in Excel and determine total quantity of each item

I have a steel cut list in Excel. There are over 1000 rows of information and probably 100 different part numbers. Is there a formula that can find the total quantity for each part number?
I am ...

**0**

votes

**0**answers

12 views

### How to use a value in a cell to reference another workbook? [duplicate]

I have a list of names for workbooks and their directory in a worksheet. I want to create a formula where this...
='C:\Users\USER\Desktop\[Book2.xlsx]Sheet1'!$D$6
I want to replace the ...

**1**

vote

**3**answers

49 views

### How can I have a cell flash green or red dpending on whether the updated value is higher, or lower? - In Excel

I am running an excel spreadsheet. Some of the values are being fed in from an external source. In this case, it is the price of crude oil.
The cell is updating in real time.
I would like to have ...

**0**

votes

**1**answer

93 views

### Excel: find the cell that another formula found its “answer” in

I made a formula that searches an array to return a value, and now I want excel to "show its work" and tell me in which cell it found that value. I've been fighting with MATCH and ADDRESS but so far ...

**-1**

votes

**2**answers

36 views

### Calculations for groups of data

I have 3 columns of data in an excel sheet. The first column represents zip codes, the second represents values in that zip code and the third column represents the frequency of that value in the zip ...

**0**

votes

**1**answer

57 views

### Phone number formatting in Excel 2010

I have so many numbers in so many format like 2123244559, or 364 7890, or 02246802121.
I want to write them in the same format like 0 ### ### ## ##, but the "format cell" tab isn't working.
I've ...

**-2**

votes

**2**answers

56 views

### Calculate average gas prices by year in excel [closed]

I have 3 columns, A=Date, B=Price, C=Grade in Excel. I want to calculate the average price of fuel for each year and an overall average of all years by grade.
The data table looks like this
...

**0**

votes

**1**answer

50 views

### Convert formulas in table to structural references

I have converted my range in excel 2010 into a table. However, the formulas which were before like that(see below) stayed the same and do not convert into structural references.
...

**0**

votes

**2**answers

30 views

### Showing name of row instead of excel cell name

I am having extremely long formulas over an extremely big sheet. At the moment I am tracking the formulas with the Formula Auditing Tool. However, my idea would be to just replace for example in a ...

**-2**

votes

**1**answer

82 views

### Excel - How to count matches in data?

I am looking for patterns in the user journey of converted customers. I have each customers details in a row and then each step of the journey in it's own cell in columns, with up to 12 steps for each ...

**0**

votes

**1**answer

109 views

### Excel Help: Search Column A, Sheet 1 for same value in Column A, Sheet 2, return value of Column B, same row in Sheet 2 [closed]

I'm surprised I couldn't find the answer on Google, maybe I am being silly.
I want to perform this function:-
I have two sheets in Excel, lets say they are Sheet 1 and Sheet 2
In Column A, Sheet 1 ...

**-1**

votes

**1**answer

240 views

### Compare the value change of data in Excel between two similar sheets without VBA?

I need to match data in the column A's of two different sheets with similar data and then compare the values in column C. sheet 1 features data from this month, sheet 2 from last month.
I have column ...

**0**

votes

**1**answer

71 views

### Months and Date keep being switched around when running VBA

I have a column on my sheet, Column F which is full of dates. Now I need these dates to be in this format "dd,mm,yyyy" but sometimes they are in text form, us/armenian format in the original data. Why ...

**1**

vote

**1**answer

69 views

### Clear contents from matched column of data in another sheet

I have a column of email addresses on sheet 2 col A (but I could put them on sheet 1 if it would make it easier / faster) that I want to remove from sheet 1 col D if matched (there may be 2 or more ...

**0**

votes

**1**answer

35 views

### =randombetween with a certain percentage between a narrower range

I want to randomly generate dates (within a four-year period) for 32 different records/rows in excel.
Additionally, I want 75% of those dates to be during one year in particular (within that four ...

**0**

votes

**1**answer

69 views

### Excel formula for collapsing adjacent rows with a sum

I have a spreadsheet with values like so:
blah 1 12
2 12
3 12
foo 1 6
bar 1 2
foo 1 6
2 5
Numbers in the middle column range from 1 to 8; ...

**0**

votes

**1**answer

83 views

### Excel - minifs function returning 0

I'm trying to write a function in Excel 2013 that returns the minimum of a set of cells that meets two criteria, but the function always returns zero.
On one worksheet, I have a table with several ...

**0**

votes

**2**answers

64 views

### Calculating time between 5a-1p or 530a-130p

I was wondering if it is possible for Excel to calculate time span if each time block is in 5a-1p format in one cell. That way I could add the hours for each week without changing too much.
Sample ...

**0**

votes

**0**answers

50 views

### Excel using INDEX and LIST to return multiple results [duplicate]

I'm trying to use Excel with the INDEX and LIST functions (rather than VLOOKUP) as I am trying to return multiple results from one worksheet into another.
I've got close with the following formula: ...

**1**

vote

**3**answers

165 views

### Excel function working like SQL group by + count(distinct *)?

Suppose I have an Excel sheet with below data
CODE (COL A) | VALUE (COL B)
==============================
A01 | 10
A01 | 20
A01 | 30
A01 | 10
B01 | ...

**8**

votes

**1**answer

109 views

### How does the NOW() function get evaluated cross time zones in Excel Online?

How does EXCEL ONLINE evaluate the NOW() function when a spreadsheet is accessed from different time zones? For example, if user PAUL is in London, UK and accesses a sheet at 5PM GMT and user SUE is ...

**0**

votes

**1**answer

40 views

### How to compute regularization date in excel [duplicate]

Hi I want know if there is a formula in excel. Let's day I was hired Feb 24, 2014 so regularization is 6 months so my regularization date would be August 24, 2014. I want to know the formula in excel ...

**1**

vote

**1**answer

22 views

### Date Arithmetic in Spreadsheets

I have a bunch of data in a spreadsheet where the left-most column represents a date in the format DD-MM. Here's an example for column A:
15-09
23-09
25-09
30-09
02-10
I ...

**0**

votes

**1**answer

114 views

### Excel SUM From Different Sheets IF Date Found

I have a workbook with separate sheets for each product (about 20 sheets, adding more on a regular basis). Each product is only available for a certain time frame, and has daily sales data recorded on ...