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

**0**

votes

**1**answer

33 views

### Lookup formula isn't always accurate

Column A & B are examples from a worksheet that is automatically updated from a a historian data base (the data upload changes every time it is refreshed). Column F & G are examples from a ...

**-1**

votes

**1**answer

31 views

### Excel 2013: Time/Date Stamp upon change in cell value

I am using Excel 2013 on a PC running Windows 7. In Excel, if cell A1 changes from one value to another, I would like a date time stamp to appear in cell B1. Assume cell A1 could be blank or filled ...

**1**

vote

**1**answer

31 views

### Excel: Count the number of matches between lists

I have two lists, each on separate tabs. One list occupies B5:B10 (though the number of rows could change) and the other is named "proteins". I want to count the number of matches between the lists ...

**0**

votes

**1**answer

24 views

### How do I assign Enterprise numbers of Enterprises in one sheet to Enterprises in another sheet?

I have one sheet (e.g. Sheet 1) where there are two columns:
Enterprise Number
Enterprise Name
and in the second sheet (e.g. Sheet 2), I have only Enterprise Name column. The problem is that the ...

**0**

votes

**0**answers

59 views

### Inserting lines without messing up formulas in Excel

I am trying to keep track of all of my maintenance performed for all the HVAC, Reridgeration, etc. for my restaurant. I have created an excel workbook that allows me to input information such as the ...

**0**

votes

**2**answers

32 views

### How do I use an array as a condition in a SUMIFS formula?

Here's my formula:
=SUMIFS('February 16 Data Table'!$AD:$AD,'February 16 Data Table'!$AI:$AI,"N",'February 16 Data Table'!$AJ:$AJ,"N",'February 16 Data Table'!$AK:$AK,"N",'February 16 Data ...

**0**

votes

**2**answers

25 views

### Calculate date based on number of weeks

I'm working on a spreadsheet that has a start date, duration, and end date. I'm working on a project where dates shift and I'd like Excel to calculate the end date automatically.
In the example ...

**1**

vote

**1**answer

40 views

### How to compare a cell with text to 2 columns?

I've tried and researched a lot, but somehow I can't find a solution to my problem:
There's A1:A50 containing various names (e.g. Bond) and B1:B50 also with names (e.g.James)(-> not necessarily in ...

**2**

votes

**2**answers

33 views

### Index - Match command syntax

I want to scan through an array with say 10 columns and 17 rows which hold 0 in most of the cells.
I need to scan from top to bottom row by row and want to highlight the index(in row 1 as 5) at L3 ...

**-1**

votes

**1**answer

30 views

### Add timestamp excel with formula

In an Excel spreadsheet, I want to add a formula that when I type a text in a cell , it adds the current time after the text I type ( in the same cell ).

**0**

votes

**2**answers

22 views

### Filling in column based on column values

I have a set of values in a column as follows:
1
1
3
3
12
12
43
43
94
94
88
88
The spacing is arbitrary but there are always two of each value. What I'd like to do is fill in the blanks ...

**1**

vote

**2**answers

78 views

### How to convert a table into summary as shown

I have table as below. I want to add rows for a category and sum all (excluding null) same species (say lemon), same species length under each category
.
Now i want to consolidate the table as shown- ...

**0**

votes

**1**answer

24 views

### Excel - Trimmeans alternative

I am trying to run a trimmean function on a set of data, and it needs to be based on multiple input items.
The raw data has an average range, and 3 columns for criteria matching (Month, and two more ...

**0**

votes

**1**answer

44 views

### An empty column to enlist only non-blank values from another column

I have a list of entries in columns H, I, J. The column K is a helper column which concatenates the three. This is done to check the number of repeats within the range (Column L).
Now that I got the ...

**0**

votes

**1**answer

60 views

### How to sum and rank multiple occurrences in Excel

Basically, I have a list of names and values in two columns, respectively.
But they are listed according to date, like a logbook (hence the random order and multiple occurrences of names). I want ...

**0**

votes

**0**answers

9 views

### How do I get the index, within a range, of a cell containing a substring `x`?

How do I get the index, within a range, of a cell containing a substring x? I tried having a look at match, but that seems to require the match to contain the entire searchstring. I also had a look at ...

**0**

votes

**1**answer

39 views

### Excel formula with two conditions

One cell in each line contains a date in the format 01/01/2016. The cell next to that one contains a dollar amount.
There are from 10 to 20 lines during each month.
Each line is either no color ...

**0**

votes

**2**answers

37 views

### Calc, formula to find the index of the minimum value

In LibreOffice Calc, is it possible to write a formula which, given a column of unsorted data (see example below), returns the largest index of the minimum value greater than zero? The following ...

**1**

vote

**1**answer

82 views

### Tricky Excel Formula

Trying to build a screening tool in excel. I am using three different variables to produce a yes or no in the final cell.
The first two variables are Assets and Tier 1 capital. These are simple ...

**0**

votes

**1**answer

47 views

### Formula stopped working after changing date field to calculated

Why do I get different results for B2 and C2?
Here are the two formulas:
0:00 = =SUMIFS(tblTrack[Spent],tblTrack[Time Ended],">="&E9)
6:32 = =SUMIFS(tblTrack[Spent],tblTrack[Time ...

**0**

votes

**1**answer

37 views

### Excel, count unique values if criteria - HUGE data set

problem i have 300.000 entries
Colum A = YEAR
Colum B = month
Colum C = customer number
G5 to G13 = criteria
I want to count how many unique customers pr. year - I tried using an array formula - ...

**0**

votes

**0**answers

22 views

### Excel 2010 - Select Cell Content, based on the value in the same row

I have a list of equipment which has been worked on to a greater or lesser extent. Each item has a unique identifier in column A, and a row to itself. The work items are listed in columns across the ...

**1**

vote

**1**answer

32 views

### Excel INDEX array return multiple results, combined with IF

I have an list of items with various prices. I have a function that creates an array to show all instances an item occurs:
A B C
1 Oranges 10 Yes
2 Apples 12 No
3 Pears ...

**0**

votes

**2**answers

23 views

### How to find if 2 columns (or rows) in LibreOffice Calc are identical?

I have two long columns of data in LibreOffice Calc, something like below:
Jimmy | Jimmy
Bunk | Bunk
Lester | Lester
Prez | Prez
...
These columns are most likely identical, that is, they ...

**1**

vote

**1**answer

45 views

### Conditional multi-column INDEX MATCH

I have two worksheets, one with two columns of values like this:
Num1 Num2 Result
0.01 0.99
0.01 0.98
0.01 0.95
0.01 0.95
0.01 0.94
0.01 0.93
0.01 ...

**0**

votes

**0**answers

21 views

### Merge data with key column in Excel or access

I have hundreds of folders with 4 tab-delimited data files each.
Each file contains two columns. The first row is a header. The first column for each of the four files contains a time value in ...

**0**

votes

**1**answer

44 views

### How to format entire row when any cell of the row contains any value?

I want when there is any value (whether any alphabetic or numeric value) in any of the cell into the row from a1 to p1 then the entire row (from a1 to p1) should be formatted.
For example there is ...

**0**

votes

**1**answer

53 views

### some way to confirm an entry

i have a question which may or may not have an answer.
i have a cell, say A1, and if there is a "y" in A1, the values in A2 and A3 are added and the sum is in A4. If A1 is blank, A4 just has A3's ...

**0**

votes

**0**answers

21 views

### Finding a formula to match one word in two different columns

So in two columns I have the words good, bad, and equal. I only want a Yes if Good is in ColumnA and ColumnB. Everything else No. Do you have a formula for my example? See below
ColumnA ColumnB ...

**0**

votes

**1**answer

31 views

### MIN IF Array Function with Multiple 'is like' criteria

I think I have a good one.
I need a MIN-IF (and MAX-IF) on multiple criteria where near matches are required. I know how execute a MIN IF on multiple criteria using the product of many IF statements ...

**0**

votes

**2**answers

39 views

### Microsoft Excel Bitshift in formula

Is it possible to bit-shift a number in a formula in Microsoft Excel?
I want to do something equivalent to
=(a1<<2)
I know I could just divide/multiply by a power of 2 and round, but this ...

**1**

vote

**1**answer

72 views

### Cannot calculate times with 12:00 AM

I have a spreadsheet the successfully adds two times together, but that has one problem. I am adding two times to get a resulting time elapsed as in this sheet:
The formula Iâ€™m using (in column F) ...

**0**

votes

**2**answers

21 views

### How to create a dynamically updating variable in MS Excel

what I'm trying to do is- I've a column in my excel workbook called "Banked Amount" which varies from month to month. Now, what I would like to do is to calculate a total of all the "Banked Amount" ...

**0**

votes

**1**answer

24 views

### How to make hyperlink shell reference shift as its copied

I'm wondering whether there's any way to make hyperlink shell references shift with folders. For example, I have the hyperlink =HYPERLINK("NTB\0000000313", "Link"). If I copy it down, is there any way ...

**1**

vote

**1**answer

143 views

### Summing unstructured 'blocks' of numbers in Excel

I have a column of data in the following general format:
2000
1000
500
300
1500
c
a
1000
1500
1250
3000
4000
6500
3000
1000
1000
1000
f
4500
1500
3000
3000
2000
There is no pattern to the ...

**0**

votes

**2**answers

29 views

### Excel get sum of criteria based percentage

It was difficult to word this question, but basically I have some data similar to the following, that shows a list of roles, their base rate cost, and a percentage.
I have added the Actual Cost ...

**1**

vote

**1**answer

32 views

### Excel Online - Replicate date from sheet1 columnA to sheet2 columnB

Online, using dropbox excel online, I have a sheet opened named:
Product Nice
On that sheet, among others, I have two columns with a range of 30 values, and I wish to create an NEW sheet, ...

**0**

votes

**1**answer

58 views

### Excel 2016 Mac: Formulas are not copied with the fill handle

Imagine a workbook with two sheets, named "Hosts" and "Eligibility".
The Hosts sheet is laid out like this:
Name Status Eligibility
-----------------------------
Josh Ready Yes
Anita ...

**1**

vote

**1**answer

39 views

### Using Excel with VLOOKUP but then match value and return contents of adjacent cell

I have a spreadsheet as sample spreadsheet.
I need to use VLOOKUP to reference the id, then find Codes across several columns and return the mark from the adjacent column, any help would be great.
...

**1**

vote

**2**answers

33 views

### Using Excel Indirect with worksheet names

I have a workbook with multiple worksheets and have developed in the some of the worksheets a query/reference to a row in one of the worksheets.
I realized that when I add a row to the referenced ...

**0**

votes

**0**answers

11 views

### Excel Sumproduct

Hey guys I have an excel sheet with multiple days on it lets say in a random order. I want to sum product by supervisor and day. How can I make the equation so it only sums the "product" of the ...

**0**

votes

**1**answer

12 views

### Is it possible to use the contents of a cell to run a VBA script?

I know how to create a button and assign a VBA script to run when it is pressed. But, I need a script to run just because someone has entered text in a cell. So that once a cell becomes non-blank, a ...

**0**

votes

**1**answer

35 views

### Excel substitute function match entire cell contents

I have a VLOOKUP function that was returning a 0 instead of blanks when there was no match.
A B
1 Heading Heading
2 Data A The year is 2015
3 Data B 0
...

**0**

votes

**1**answer

34 views

### General questions about entering minutes into an Excel worksheet

I'd like to be able to enter minutes into Excel cells and have another cell that totals the minutes. I have no problem converting minutes into hours. However, when I enter information, I don't want ...

**1**

vote

**2**answers

59 views

### Excel Pass/Fail function with multiple variables

I have this table where I need to get Pass or Fail result.
For column A if yes or n/a: 50% pass / no: 0% pass
For column B if yes: 15% pass / no: 0% pass
For column C if yes: 35% pass / no: 0% pass
...

**1**

vote

**1**answer

48 views

### How to Index-Match upwards from a specified cell until non-blank

I've got data in sort of a directory tree layout (See picture)
Basically I want to use one lookup code as a reference point and have excel look upwards in the respective columns to display the ...

**0**

votes

**1**answer

31 views

### time format in Excel [duplicate]

How to get desired output in MS Excel?
note that the outage duration is in mins
Start time Outage Duration (in Mins) DESIRED OUTPUT
3:12 AM 60 4:12 AM
4:05 PM ...

**0**

votes

**0**answers

46 views

### Excel - reference to a last non-empty row

I am trying to make Excel find the cells from the last non-empty row, containing a certain character. I am attaching the picture and further explanation below:
So, DK3 value is 4, DL3 value is 4, ...

**1**

vote

**1**answer

15 views

### Input “minutes:seconds” format in cell, have Excel convert to seconds and report in same cell

I'm trying to have Excel convert "mm:ss" input into "ss" format in the same cell.
For example I want the user to enter "1:21" (not time of day, but mm:ss) in Cell A1 and have Excel convert it to ...

**1**

vote

**1**answer

53 views

### MINVERSE only returns a single number

For the life of me, I can not get the MINVERSE function on Excel 2011 on my Apple Macbook to work.
I entered the function into a matrix.
I type =MINVERSE(A1:C3), the location of my matrix to be ...