# Questions tagged [excel-formula]

4837 questions

1

votes

2

answer

436

Views

### Excel - Pull data from a cell on a different sheet and hyperlink

I know how to pull data from a separate worksheet in excel by using the formula =Sheet2!A2. That pulls whatever data I have in the A2 cell from my Sheet2. But is there away to pull the data from that cell and hyperlink it to the cell by using a formula? I know you can manually link it but I'm trying...

1

votes

2

answer

53

Views

### Counting number of leading zeros in cell in Excel

Trying to figure out how to count the number of leading zeros in an excel cell. The value within the cell can be comprised of both numbers and letters or just numbers or just letters. The column is formatted as a text column.
So far I have been able to use
=MIN(FIND({1,2,3,4,5,6,7,8,9},A1&'123456789...

1

votes

2

answer

31

Views

### How to lookup a value from another column and display Row number?

I'm setting up an excel file for the operators. They will scan the Barcode from the product and it will populate in one of the column of the excel sheet.
I have tried using LOOKUP formula in excel but it doesn't seem to be working right.
COL A : Data from Database
COL B : Data from Scanner
COL C:...

1

votes

2

answer

34

Views

### Excel formula - How to separate a formula value in a cell into 2 different cells?

In my Excel sheet I have a column which gets the transposed data from a row of a different sheet.
=TRANSPOSE(DATA!L170:CC170)
An example of a cell's data is 0/300 and is consistent throughout the column.
Now I plan to have 2 new columns next to the first column to show the numbers separate:
| 0/300...

1

votes

2

answer

25

Views

### How can I expand a dynamic range with blanks between cells?

I want to define a dynamic range that will expand the rows and columns of my range whenever a new row or column is inserted. My current formula does not want to expand to cell $T$13. My headers start in row $M$7. How can I adjust my formula?
Formula Being Used
=OFFSET(Sheet1!$M$8,0,0,COUNTA(Sheet1!$...

1

votes

6

answer

80

Views

### Extract numeric portion from an alphanumeric string in Excel 2010

I would like to extract all the numbers from an alphanumeric string in excel. I have an excel sheet with list of alphanumeric strings as shown below and I would like to extract all the numbers from the alphanumeric string and store it in a new cell
I already tried the below formula found online but...

1

votes

1

answer

38

Views

### Get Total Hours From Excel TEXT Function

I have a column in my worksheet that is a subtraction of 'End Time' and 'Start Time', giving me the total number of hours:minutes:Seconds during. Sample Input:
Start Time: 15/04/2019 1:38:00 PM
End Time : 18/04/2019 3:00:00 PM
Basic subtraction gives me the right value i.e.
73:22:00
However for...

-1

votes

0

answer

14

Views

### Averaging a Dynamic range in Excel

I have a list of values that looks like this. Every 30min the top row in my data is shifted down and a new latest value is inserted. I'd like to be able to take the average of all the values in the model column from 18:30 to the top most cell each day (so just the latest cell that has an 18:30 value...

1

votes

2

answer

16k

Views

### Dynamic Named Range based on second column's value without VBA

I'm trying to build an interface that allows a user to select a category of modification (17 total categories) and have the second drop down box list only those modifications (anywhere from 5-30 modifications) that apply to that category.
Normally, I would set up reference tables to indicate which...

0

votes

0

answer

3

Views

### Using Sumifs to Calculate percentage from two different tabs

I'm trying to create a production tracker for a Distribution Center and need to have the %to Goal calculated based off of criteria in a table
Column A or B Won't be needed they're just names
The following columns are the detail
Column C = Department (Picking, Putaway, Forklift)
Column D is the date...

0

votes

0

answer

10

Views

### How to highlight a range of cells in a row based the value of one cell? (C# Excel)

I have a conditional format set up where it will highlight a cell based on the values in a String List. That works fine but I need to highlight a certain range within the row not just the one cell.
I need the range of xlsNewSheet.Range[xlsNewSheet.Cells[x, 1], xlsNewSheet.Cells[x, 8]] highlighted i...

0

votes

0

answer

4

Views

### Set non-continuous PrintArea via Function / Name

I have been trying to set a dynamic non-continuous print area via Function, but I don't seem to be able to get it to work.
I have a defined name for the print area: Print_Area.
To this Name I am able to pass a range eg Sheet1!$E$2:$I$23 and I can dynamically switch to another range using the INDIR...

1

votes

1

answer

365

Views

### Return array from INDEX function in Excel?

I'm trying to use INDEX in array format in Excel but I'm running into problems.
From this question: Return array from INDEX function?, it seems that 'INDEX (like VLOOKUP) doesn't return an array of values (except in some convoluted circumstances)'
So I'm wondering what alternatives there are.
I'm t...

1

votes

5

answer

97

Views

### How can I get a list of all the sheets that are in a file on one sheet?

Is there a way to find the names of all the sheets as a list?
I can find the sheet name of the sheet where the formula is placed in via the following formula:
=RIGHT(CELL('filename';A1);LEN(CELL('filename';A1))-SEARCH(']';CELL('filename';A1);1))
This works for the sheet the formula is placed in. How...

1

votes

2

answer

68

Views

### In Excel calculate AVERAGE of an array result which in a cell

I have the below formula that Lookup the A1:A10 appropriated Score Number.
{=INDEX(Table1[ScoreNum],MATCH(A1:A10,Table1[ScoreWord],0))}
I need calculate the AVERAGE result of this entire array.
But when using this:
{=AVERAGE(INDEX(Table1[ScoreNum],MATCH(A1:A10,Table1[ScoreWord],0)))}
Returns the fir...

1

votes

1

answer

33

Views

### Is it possible to calculate this series sum in excel?

I need to calculate value of this equation for any given m and a (a > 1, m > 1). Is there a formula in excel that can do that?
p.s. I've tried SERIESSUM but with no luck, either I'm doing something wrong or it's not suitable for case like this.

1

votes

2

answer

27

Views

### Match words from array into a string

I have a cell which contains a description like the one below
'customer enjoys playing cricket and football.'
Then on another worksheet I have a list of sports
Cell / Sport
A1: Tennis
A2: Football
A3: Golf
A4: Boxing
A5: Hockey
A6: Cricket
I need iterate through the list of sports and match the firs...

1

votes

3

answer

46

Views

### Excel column to array list

I have a column in Excel with the following layout:
test.com
ba.com
test2.com
What i want to generate is a powershell array list so my result needs to be:
'test.com','ba.com', 'test2.com'
I wanted to start by concattening my string at the start and end but can't seem to do this.
What i do is this...

1

votes

2

answer

33

Views

### Sum values from subcategories

I have 3 tables (simplified example in the image below). Table 1 shows the relationship between the Main Codes and Sub Codes; each Main Code can have zero or more Sub Codes. Table 2 shows Sub Codes and a value; Sub Codes can have zero or multiple values. Table 3 shows each unique Main Code and the s...

1

votes

3

answer

49

Views

### How to get a specific output if a value is within a range

I have a range of multiple zip codes 'boundaries' with a specific cost attached to it.
I have a seperate file which indicates a zipcode. I have to use a formula which checks the table presented in the image, which checks in which zip code interval it belongs and then as a output gives the correspon...

1

votes

2

answer

72

Views

### Insert a new value if there is a duplicate

I have a table with multiple duplicates (Log in) for an id. Normally after each 'log in' there must be a 'log out'. If yes, then I do not have to do anything. If after a 'log in' there is no 'log out' then I have to create one at the and of the day (23:59:59).
I have the following table:
Id Sta...

1

votes

1

answer

26

Views

### Excel SUMIF looking for letters in an order

I have this set of data
A B
1 A768BC10 2300
2 AB7697C787 2030
3 AC879B87 1760
4 AB56779 1490
Now I want to get the Sum of the values where A is before B and C is after B I was wondering if there is a way to do it I tried using RIGHT() but the LEN() I was using to get the...

1

votes

2

answer

40

Views

### Look down an unsorted list to find a greater than or equals value

I have a list of non-sorted quantity in column A and their corresponding values in column B.
I need a non-array Excel formula that returns the first value that is greater than or equals to the input quantity.
For e.g. :
Minimum Qty: 6
Scenario 1:
Output Value: B
Scenario 2:
Output Value: B
Scenario...

1

votes

2

answer

15.2k

Views

### Excel formula for: If match then paste

I'm looking for a formula on excel to do the following.
If a value on a spreadsheet is a match to a value on another spreadsheet:
Then paste the cell next to the match from the first spreadsheet
into the cell next to the match on the other spreadsheet.
This is for a stock spreadsheet from my supplie...

1

votes

1

answer

1.1k

Views

### How to reverse search in Excel?

I have a text in a worksheet like:
The girl is very beautiful
I want a formula to perform a search from right to left for the word 'very', and if found then extract it to some other region of the sheet.
Note: Purpose of doing reverse search is because I want to implement it in my workbook which re...

1

votes

1

answer

596

Views

### Excel 2013: Condition Formatting with “IF(AND)” and “ISBLANK” and Data Validation Values

I have a formatted table and would like to use conditional formatting to shade in cells in a column (G) based on the following conditions:
Value in column (D) must say 'Pending Decommission'. The value is from a data validation list. If value does not match, do nothing.
If the above is true, then ch...

1

votes

1

answer

82

Views

### Custom Format decimal number as N Week N Day

I have a table of dates that i reference in another table that shows the number of weeks & days elapsed since TODAY().I use the DOLLARFR() function to get a decimal value that represents the weeks & days elapsed.
=DOLLARFR((TODAY()-M8)/7,7)
Where M8 = '22-Dec-2017' & TODAY() = '29-Dec-2017'. This gi...

0

votes

0

answer

12

Views

### How to calculate the sum of each day from my excel sheet?

A B C D E
SKU Date Sale Date Sum of Sale
b0001 01-Apr 1000 01-Apr
b0002 01-Apr 2500 02-Apr
b0003 01-Apr 5000 03-Apr
b0004 02-Apr 3500 04-Apr
b0005 02-Apr 3500 05-Apr
b0006 07-Apr 2900 06-Apr
b0007 07-Apr 3000 07-Apr
b0008...

1

votes

2

answer

48

Views

### Excel: count values on another sheet with dynamic column reference

I have a simple formula in cell B7 of a sheet called Summary Stats that references a sheet called Rolling Returns Data:
=COUNT('Rolling Returns Data'!C$11:C$17202)
I want to be able to dynamically reference the column in the formula i.e. I have letter C in cell B1 of the Summary Stats sheet and want...

1

votes

0

answer

377

Views

### How to Aggregate Strings Using Google Sheets Pivot Table Calculated Field

I have a Google spreadsheet in which I recorded text evaluations of various teams made by individuals. The sheet, named Data, looks like this
Col A Col B Col C
Rater | Team | Feedback
-------------------------
r1 Jim | A | Great
r2 Jane | A | Sucks
r3 Kate | A | Fabulous
r4 J...

1

votes

0

answer

387

Views

### Excel - Referencing a formula operator from another cell

Is it possible to reference text from various cells to construct a formula in a different cell?
Example:
A1 = 1
A2 = >
A3 = 0
=IF(CONCATENATE(A1:A3),1,0) returns 1
This obviously returns an error, but I'm hoping there's a function that does something like this.
Also I'd like to avoid using if st...

1

votes

1

answer

33

Views

### INDEX formula for criteria with multiple outputs

This is the formula I am using, however, it is outputting the incorrect account. It seemed to be working when I tested it on a single sheet but when I changed the output and input sheet it stopped out putting the correct accounts.
=IF(ISERROR(INDEX(Sheet1!$B$1:$C$1000,SMALL(IF(Sheet1!$B$1:$B$1000=A...

1

votes

1

answer

98

Views

### How to calculate skewness from frequency table in excel?

I would like to calculate skewness in excel from a table in which one column is an argument and second one is a number of this argument. Unfortunately, the excel formula is only for one list of data...
Can you help me?
Thx

1

votes

0

answer

39

Views

### Opening a tiff file from Excel causing loss of Cell Edit functionality

I have a spreadsheet, with different people using copies on different PC's. There is a macro triggered by Worksheet_Selection_Change that opens an image file. On two of the PC's, when the workbook is opened, the user can't edit a cell without double clicking in it. Also, the Selection_Change even...

1

votes

0

answer

485

Views

### Excel special symbols in formula

Folks,
I am trying to run DDE ( with MetaTrader Terminal 4 platform ) from Excel info using a defined syntax of '= MT4|ASK!USDCHF' and it works fine.
However, when I try to use it for a #GOOG symbol or for a ^Coffee, Excel returns it as a formula being corrupt.
The reason for it, obviously is that '...

1

votes

0

answer

69

Views

### Match Columns in different sheets and Update column in Excel

I have an Excel Workbook in which I've got a DataEntry sheet and lot of other sheets. What I have to do is on basis of columns (at least 2) matching between DataEntry sheet and Sheet1, I want to pick value of the third column of DataEntry sheet and insert it in matching a row of Sheet1 in the corres...

1

votes

1

answer

575

Views

### Excel Conditional Formatting and Rounding Issues

I am trying to find a way to use excel formatting to show the color red when a specific cell is not equal to another cell. However, there are times when the value is off by two or three numbers due to how excel rounds(ex: 593=594, 593=592).Is there a way to use excel formatting or any other way to m...

1

votes

1

answer

28

Views

### Excel formula to lookup blank value or maximum date time associated with a text value

I am trying to create an Excel formula that looks up a value (here, an animal type in column E) in a range (column B) and returns the latest associated date time value (column C) if there are no blanks associated with that value, and returns a blank if there are any blanks associated with that value...

1

votes

1

answer

78

Views

### convert days hrs mins secs strings to [h]:mm:ss in excel

How do I make a formula or vba that would automatically detect the days, hrs, mins and secs and convert it into the right [h]:mm:ss once I paste it into excel? I have some data need to process and they are in the following formats (string):
134 days 10 hrs 41 mins 23 secs
109 days 40 mins 37 secs
67...

1

votes

4

answer

54

Views

### array based on max and less than?

I'm not sure if I'm over complicating this...
basically I'd like to have a formula which is
if the c column is less than 6, then look up the max value in B but display the value of C
so far I have this but I'd like it to show 2, not 437
{=MAX(IF(C2:C12