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

**0**

votes

**0**answers

4 views

### How to replace an array excel formula with another regular one to avoid long calculation time

EXAMPLE
by using an array formulas in range (E11:E16) According to the above example:
To count unique customers in range (E2:E7) that meet only the following criteria:
1- Purchased Product 1, ...

**0**

votes

**2**answers

3k views

### Lookup multiple results for multiple criteria

I've got a list of parent SKUs for items I need to create in my inventory system. This list has been finely paired down to the 165 products we would like to carry. However, each one of these 165 SKUs ...

**1**

vote

**3**answers

4k views

### Display multiple cells if data on another workbook contains “X”value

I export a complex report daily and I transfer that information manually into another excel sheet. My question is: In workbook 1, can I get a group of cells in a row to display the text in workbook 2 ...

**1**

vote

**0**answers

10 views

### Show MS Excel Syntax as you type Formula

I have Office 2013 on my work computer and Office 2016 on my personal computer. On my work computer in Excel 2013 when you type a formula you get the suggested syntax for formulas as you type, but on ...

**0**

votes

**0**answers

7 views

### Excel: Add new rows to a table with a subtotal row, when data added to a specific column on the last row

How do I get an Excel table (with a subtotal row) to automatically add a new row when I enter data in column C of the last row?
At the bottom-right of the table I have 3 cells with basic SUM formulae,...

**0**

votes

**1**answer

8 views

### Excel formula: Add entries in same month and year

This seems like it should have a fairly simple solution, but I can't quite find it. Any help would be much appreciated.
I'm trying to write a formula in Excel that will refer to an itemized list of ...

**0**

votes

**1**answer

6 views

### Creating a For All function in Excel

This might be hard to explain, but I am trying to create implement a formula that compares a bunch of values of "TRUE" or "FALSE" and then displays an output a corresponding cell that states if at ...

**-2**

votes

**0**answers

39 views

### Compare and group rows based on the sequence of their cell values in Excel 2013

I have one worksheet written to show packing contents for different products. This is how it is set up; Column A includes model numbers. Photos and part numbers are included in columns B through AH. ...

**0**

votes

**2**answers

38 views

### Excel: sum values corresponding to multiple matching or individual numbers

hoping someone can help with this one:
The numbers on the left (phone numbers) will be either individual or repeated.
Each row has a number in the second column
that is associated with that phone ...

**0**

votes

**0**answers

8 views

### Array formula behaviour inconsistent?

I am trying to use a sum with an array formula to be able to make a "sumproductif" :
my old formula :
=SUMIFS(OFFSET($B$2:$B$1915;0;$Z$22);$L$2:$L$1915;$Y$3;$B$2:$B$1915;X4)
I replaced it with
{=...

**0**

votes

**0**answers

36 views

### Getting data from same row as matched cell on separate sheet

I need to match A2 with Sheet2!A:A, but the value I need is a cell on Sheet2 in the same row as the match.
Let's say A2 matches Sheet2!A20. I need the formula to return what's in Sheet2!E20.
I ...

**0**

votes

**2**answers

18 views

### Is there a way to add together an arbitrary number of cells based on a specific criteria in Excel?

So I have a spreadsheet with a bunch of little mini-charts as seen below. The numbers in blue are user entered, and they are all added together in the chart with the gray cells on the top left.
Right ...

**0**

votes

**1**answer

37 views

### Get distinct count based on different parameters

I need to get the distinct count (how many times does a unique value occur) based on a few parameters. The table looks similar to this:
Getting the Amount is not a problem, I use SUMIFS() with a ...

**0**

votes

**0**answers

14 views

### Defining a range in excel for index/match

I'm trying to return a "best" value, but there are two criteria for a threading calc. One column has stress, which just has to be higher than a given value, and the other would be where you want the ...

**-2**

votes

**0**answers

11 views

### Excel pivot table grouping

I'm using excel 2010 and I want to create a pivot table that shows times between breakdowns in groups of 1 hour. The problem is that no matter what I try I get the cannot group selection error. I ...

**0**

votes

**0**answers

18 views

### Excel Power User Challenge - Advanced Workbook Split [on hold]

I need to split a workbook's multiple worksheets into multiple files (not based on worksheet name). Sounds easy, but just wait.
The project:
It deals with very sensitive HR/performance data, and I ...

**1**

vote

**2**answers

53k views

### Comparing two columns in different Excel spreadsheets

I have two Excel spreadsheets. In the first spreadsheet I have two columns. Column A has 240 Accounts entries and Column B has Values for each account entry. Once again Spreadsheet 2 has its data in ...

**-1**

votes

**0**answers

8 views

### Excel - Data Fill from another sheet in certain column if value is right

I want to copy certain information from a line in another sheet if in a specific column it shows a value different from Work Done.
Say in column F it has a value different from Work Done, then I want ...

**0**

votes

**2**answers

8k views

### Count from a certain cell until a blank cell

I'm trying to create a dynamic formula which counts all the rows from cell D3 downwards until a blank cell is reached. I want to be able to add new data everyday and have the range automatically ...

**-2**

votes

**0**answers

11 views

### How to automatically transfer Excel data with no fixed cell reference to a new worksheet or workbook with specified fields

How do I AUTOMATICALLY transfer mixed data to go to specific fields in a new worksheet or workbook.
All the Excel data are on separate worksheets.
The columns and rows are not similar.
The lables ...

**0**

votes

**2**answers

38 views

### Excel Formula Shows 0 and not null

I am using VLOOKUP() to populate a few cells, but when the value does not exist in the lookup table, I want the cell to show the original content, which is usually nothing. However, when the cell has ...

**0**

votes

**0**answers

44 views

### without iteration condition, =A1+C1 in C1 cell,

I've tried iteration condition, but it is not taking only the 'A1' value, if I put some value in some other cell, C1 is calculating like =A1+C1 in C1 cell,means
A1 value is 5, in C1 i put this farmula ...

**-6**

votes

**1**answer

54 views

### Equal zero if the difference is less than zero [closed]

This is probably an easy question for those of you who know Excel:
How can I make a formula that makes the cell equal the difference of three cells but have a minimum value of zero?

**0**

votes

**1**answer

3k views

### Updating another Excel spreadsheet from current spreadsheet

I used the VBA code in "How do you write an Excel formula that will paste a specific value in a different cell?" posted here in May 2013 and it works well to populate/fill another cell in the same ...

**0**

votes

**1**answer

17 views

### How do I return the last word of a string of text?

If any of you have been following the progression of my questions, then this may come as no surprise. Be that as it may, I have to ask it. I really did attempt to find an answer to this online and ...

**0**

votes

**1**answer

16 views

### Ticket report - Auto populate from other worksheets into specific form

Hello ladies and gentlemen, I'm quite a dummy in excel and programming in general and I have a question related to building a report:
I must present a ticket report of which tickets are opened, how ...

**-1**

votes

**1**answer

30 views

### figuring Golf Handicap

Presently we use the following formula and it works great:
=IF(COUNTIF(D4:AB4,">0")<3,C4,IF((((SUM(D4:AB4)-(COUNTIF(D4:AB4,">0")*36))/COUNTIF(D4:AB4,">0")))*0.9>20,20,(((SUM(D4:AB4)-(...

**1**

vote

**1**answer

20 views

### Excel Get minimum value, and If two cells match from a row, return values from the consecutive 3 columns

Here's a simple explanation of what I'm having trouble with.
Get minimum value, and If two cells match from a row, return values from the consecutive 3 columns.
SUPPLIER A
F4: PRICES SUPPLIER A
...

**-1**

votes

**1**answer

22 views

### Need to filter out some names from a list of Total names

I have a list of names.
I need to separate the names which contains characters g,j,p,q,y.
The names can start with G,J,P,Q,Y i.e the characters as first letter. But the characters g,j,p,q,y shouldn't ...

**0**

votes

**1**answer

18 views

### How can I remove a cell from a range for a property lookup array

Lets say I have a value, A123456, in a column A, row 1, and i want to check if I have the same value in other rows of the same column, regardless of the length of the values as long as it has the same ...

**0**

votes

**2**answers

4k views

### AVERAGEIF on multiple work sheets (via 3D Reference) in Excel

I have a question when using excel averageif functions:
I have a workbook containing monthly information (stored as numerical values) in 12 different worksheets (named Jan, Feb,..., Dec). In each ...

**0**

votes

**1**answer

32 views

### Excel: How to highlight highest value cell where duplicate in another column

I have a report containing a log of members details and any actions carried out on their account.
As it is a log, there are duplicate membership_numbers in column A. Column B is the action on account ...

**0**

votes

**1**answer

16 views

### How do you return the last letter in a string of text in an adjacent column?

Rather than do 600+ entries manually before seeing if some superuser might be able to answer my question, I'm going straight to "the Oracle" first this time. By the way, @Scott Craner answered my ...

**0**

votes

**1**answer

16 views

### How do I set up a formula that will return a certain value dependent upon a portion of text in a cell?

I've got a column of text. If the text of the cell contains
el
I want it to return "M" in the adjacent column. If the cell contains
la
instead, I want it to return "F" in the adjacent column.
...

**1**

vote

**1**answer

32 views

### Can't make the import sheets from directory function dynamic?

Can I modify the code below into a dynamic import of sheets,
so I can run it in any sheet from my personal macrobook instead of a sheet called import-sheets.xls?
Found the code for importing below.
...

**-1**

votes

**0**answers

14 views

### How do I transfer data values from a chart to a table on Excel?

I have data that is listed in a chart, for example
Hello1
World2
Biscuit3
I would like to transfer this to a data table, but the numbers follow a specific pattern. For example, the table would be:
...

**0**

votes

**0**answers

13 views

### Excel, Populating a cell based on what in types in another cell

I am currently working with excel and I am having a bit of difficulty.
I will try to say basically what I am doing and what I would like to do.
I am making a sheet that I would like to be able to ...

**-1**

votes

**0**answers

32 views

### Excel External Referencing Across Work Network

I've invested a large amount of my time over the past week to building a list of document types and descriptions into a series of Excel workbooks which rely heavily on external referencing (to each ...

**8**

votes

**5**answers

99k views

### How to combine values from multiple rows into a single row in Excel?

I have a data dump in Excel that consists of annual customer data for two different values. The data was provided with a separate row for value for every year and customer. I.e. it looks like this:
...

**0**

votes

**1**answer

19 views

### Formula wont copying with reference because it links to another file

I am using Excel 2013 and have run into an issue. I have large amounts of information to go through with only a single repetitive way to do it. I am using the formula "=rept()" which links to another ...

**0**

votes

**1**answer

49 views

### Excel 2010 formula?

I need help with a formula for the following:
L6 needs to show a date which is the date in K6 + the value (days) in J6. However if there is no data or only text in either K6 or J6, cell L6 should be ...

**-1**

votes

**1**answer

24 views

### how to calculate the area of the hysteresis loop

I have a dataset, where the output will be hysteresis loop, if I execute it in Excel. But, i could not find the method in excel to calculate the area of the loop.
Anil

**0**

votes

**0**answers

13 views

### Excel 2003 - How do I link sheets in different workbooks when there are path issues?

I've created several Workbooks relating to the same project and now want to create a master workbook that contains the results from each of the others.
The problem that I have is that the formulas do ...

**0**

votes

**2**answers

20 views

### Incremented h:mm:ss Timer in Excel

I am trying to create a new column that counts up in the h:mm:ss format such that E2 displays 0:00:00, E3 displays 0:00:01 ... E62 displays 0:01:00 and so forth.
I have tried inputting a properly ...

**0**

votes

**1**answer

10 views

### Counting the repeated text in another sheet

I have to count the repeated text of a cell which is in another sheet.
Like
How can I do it?

**2**

votes

**2**answers

36 views

### Searching an array from bottom to top

I have a table array $E$14:$G$851. Column E just contains unique identifiers, lets say a/b/c/d etc, column F contains hundreds of numbers all less than or equal to 20 and column G contains the values ...

**0**

votes

**0**answers

26 views

### Excel If() function not working properly

I'm trying to set up a royalty payment that is the greater of $2000 or 30% of net revenue and the formula checks out, but does not render the correct value of $2,000 when the condition is met. Rather,...

**0**

votes

**1**answer

16 views

### Need formula to add enough zeros to total 10 characters

I need to tweak the below formula, so that instead of always adding 5 zeros, it adds enough zeros to equal 10 values (characters). I'm using this in a system (iCIMS), and the result (depending on the ...

**0**

votes

**2**answers

22 views

### How to calculate revenue equally based on start and end dates?

My company phases revenue based on slightly obscure start/end dates and I need calculate how much revenue falls into the current year, based on these dates.
If a contract starts on or after the 16th ...

**8**

votes

**1**answer

9k views

### Shortcut to accept suggested formula in Excel

I'm working with a lot of formulas in Excel 2007 and I would like to know what shortcut key you need to press to accept the suggested formula.
When I type in =DEG the first suggested formula is =...