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

learn more… | top users | synonyms (5)

0
votes
1answer
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
1answer
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
1answer
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
1answer
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
0answers
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
2answers
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
2answers
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
1answer
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
2answers
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
1answer
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
2answers
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
2answers
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
1answer
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
1answer
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
1answer
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
0answers
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
1answer
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
2answers
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
1answer
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
1answer
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
1answer
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
0answers
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
1answer
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
2answers
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
1answer
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
0answers
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
1answer
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
1answer
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
0answers
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
1answer
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
2answers
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
1answer
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
2answers
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
1answer
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
1answer
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
2answers
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
1answer
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
1answer
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
1answer
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
2answers
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
0answers
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
1answer
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
1answer
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
1answer
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
2answers
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
1answer
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
1answer
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
0answers
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
1answer
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
1answer
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 ...