Questions tagged [excel-formula]

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...
Cameron Cole
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...
jufg
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:...
user7377353
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...
hjh93
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!$...
Jose
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...
SELVA
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...
Noman Amir
-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...
novawaly
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...
Dramasailor
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...
Bobby Caplette
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...
user10471443
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...
Philip
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...
jsamler
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...
Mischa Urlings
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...
mgae2m
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.
Sergey Karpushin
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...
K Sudbury
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...
user3127554
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...
Techno Dabbler
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...
Leon
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...
Catalin Vasilescu
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...
Faz
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...
adam
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...
user1933047
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...
Chen Guo
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...
Thomas Nguyen
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...
sifar786
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...
Forhad Zaman
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...
Mohammed
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...
idomeneus
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...
Fubudis
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...
Condar87
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
Lukáš Tůma
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 '...
Tony
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...
Malikx
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...
mikerosd
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...
Brent
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...
Will
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
holly olly

View additional questions