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

**1**

vote

**2**answers

55 views

### Add values in a column until a certain point in excel and repeat

I have several thousand values similar to those shown in the picture provided.
I am attempting to sum the time values in column K, but only if they have a value of "flag" in column M, and only ...

**1**

vote

**1**answer

27 views

### using <li> on each newline

I have following pattern in every single cell in the coloumn in Libre Calc -
Spec One: This is one
Spec Two: This is spec two
Spec Three: This is spec three
Is there any way to convert this to -
&...

**0**

votes

**0**answers

17 views

### Comber cell contains to rang (not match not vlookup not index)

Blue is range "Alist", cell yellow which contains names which one of them is to be compared ie, if the yellow cell contains a name from blue range = OK.
How to return the value of the green cell to ...

**1**

vote

**1**answer

30 views

### Excel mod function rounding error

I am testing multiples of 0.4 in Excel.
=MOD(1.2;0.4)
returns 0.4, when it should returns zero.

**0**

votes

**0**answers

20 views

### Why does the SUM not work adding the totals of values which have been IMSUBed?

I'm using Excel 2016.
One column used the IMSUB function to subtract 12 from each value inputted. I then wanted to SUM for a total of all the remaining values shown (after IMSUB calculation was ...

**1**

vote

**1**answer

43 views

### Google Sheets - Conditional Formatting based on column heading of cell value [closed]

I've had a problem that's been bugging me for days and I can't quite figure it out. Scenario:
Names: This is an unsorted sheet where there are a bunch of names.
Team: This is another unsorted sheet, ...

**0**

votes

**1**answer

35 views

### When should SUM() be used as an array formula vs. not in Excel

How come {=SUM(A2:A10*B2:B10)} works, but without CTRL+SHIFT+ENTER it doesn't?
I'm trying to learn more about array formulas, but I wish there was some guide about what goes on behind-the-scenes. ...

**1**

vote

**0**answers

21 views

### Table relationships across 3 tables and updating all with a single slicer

I have 3 tables with data. All of which have a username column and I'm trying to connect them.
For this purpose I have created a 4th table which is basically just the User directory (where each ...

**1**

vote

**0**answers

41 views

### Excel 2016, “{}” disappearing in formula

I've modified an existing template called "Daily Schedule". For some reason, all of the cell formulas have "{}" around them. When I click into the cell to edit the formula, the "{}" disappears. After ...

**2**

votes

**0**answers

83 views

### Excel- How do I find common text values from multiple (>2) rows?

This is essentially a variant of the question asked here:
Excel- How do I find common text values from multiple (>2) columns?
In this case, however, instead of multiple columns of data I need to ...

**1**

vote

**1**answer

36 views

### Excel Multiple Criteria

I have 5 months worth of data that I am trying to find out how many Defects occur within 30, 60, or more than 60 minutes after the defect code 15R occurs.
Column AX, and AY converted column T and ...

**0**

votes

**1**answer

37 views

### How to extract text between two characters in Excel

I am trying to extract product information from between "en_US@" and ";" so for example if I was given "en_US@Terminal Block;" I would want Terminal Block returned. My method for doing this was:
=MID(...

**3**

votes

**4**answers

79 views

### Determining whether specified time is within 15mins of target time in Excel

I’m trying to create spreadsheet to monitor the timeliness of backups executed. Backups must be executed within 15 minutes of the target time, in order to be considered compliant. The raw data ...

**-1**

votes

**1**answer

20 views

### Formula to calculate a % IF value of cell X is greater than a value Y

I am looking to create a formula to calculate tax payments. If cell x is greater than £11000, then calculate the excess at 20% of the excess figure. i.e. Cell A1 £12000, Cell B1 would thus be £12000-...

**-1**

votes

**1**answer

25 views

### trying to do a spreadsheet for work tomorrow

i have 3000 graduation tickets that need to be entered into excel by number 001-004, 005-008, 009-012, 013-016- etc. What formula do i use to complete this task. I tried dragging the first 5 rows but ...

**1**

vote

**0**answers

19 views

### Dynamically Naming Sections of Columns in Excel

I am doing calculations that change how much of the data I want to include in a graph for each data set. I have cells that reference the first and last row numbers that I need to use and then I have a ...

**3**

votes

**0**answers

54 views

### Highlight differences between two Excel spreadsheets

Data from Spreadsheet A has multiple columns but I need to compare only:
Column A - order #
Column C - count
Column D - date
Spreadsheet B also has many columns, but I need to compare:
Column A - ...

**0**

votes

**1**answer

24 views

### Sheet comparisons in Excel where content is mostly text

I've got a a document that entails recommendations for my place of business, specific to site (we own several smaller facilities) with several sheets. The first is a full, all-inclusive list. The ...

**1**

vote

**1**answer

29 views

### VLOOKUP error when looking up date

I have been trying to use the CONCATENATE function in the Excel for creating multiple values of the financial statements for different years. My main formula is as shown:
=VLOOKUP(TEXT(CONCATENATE(A1,...

**2**

votes

**1**answer

33 views

### Using INDIRECT within VLOOKUP

I am conducting a series av tests of data in different excel workbooks. It order to not have to change all Workbook names in the formulas used I find it suitable to use INDIRECT() and refer to a cell ...

**2**

votes

**4**answers

56 views

### Remove everything to right and left of semicolon

I have these two cells:
35hz 24.5/jy;8243-9884;100093 and
aslhz/45.77;94-224444;103944234
I'm trying to isolate the number in between the semi-colons. I've used the function:
=RIGHT(A1, FIND(";",...

**0**

votes

**1**answer

10 views

### Linking Excel Workbooks

I work for a large agency and created a spreadsheet for employees to track time spent throughout the day working on a series of duty assignments. I designed a separate workbook for each employee where ...

**1**

vote

**2**answers

30 views

### Excel Formula needed

I know someone has asked a very similar question, but I need to take it a bit further than what their request was so here it is...
I'm in need of an equation to compare two columns and have a third ...

**0**

votes

**2**answers

38 views

### Get the last column letter in Excel in a specific row that has data

If I have data in row 4 in columns A through F, and I want to know that F is the last column with data, what formula can I put in B2 to get an answer of F?

**0**

votes

**1**answer

15 views

### Auto fill cyclically an array with a list of elements, setting only the first element

I have an array of 31 elements in Libreoffice Calc which I have to fill automatically with the days of the week (Mon, Tue, Wed, Thur, Fri, Sat, Sun, Mon, etc...) setting only the first element of the ...

**0**

votes

**1**answer

14 views

### SUMIF Function to sum 2 criteria in the same range

Right how I have a SUMIF Function that looks like
SUMIFS(M6:M13, N6:N13, "/M", C6:C13, SUM("Inforce", "Modify")
In the last range I want it to sum if the text criteria is either "Inforce" or "Modify" ...

**0**

votes

**1**answer

44 views

### How to find the date of the last matching row?

So I have a master spreadsheet that has 20k+ rows containing monthly leasing data. It has information like a person's name, when they leased a car, what kind of car they have, etc. Most of the rows ...

**0**

votes

**1**answer

21 views

### Excel 2007 Custom Data Validation, Excluding characters in large range

Trying to create data validation (absolutely no macros, it is simply not an option), where a user can type in only Basic characters
(A-Z,a-z,0-9,-,~,',\,/)
So what I tried to do is create a ...

**2**

votes

**1**answer

25 views

### Counting Divide-by-Zeros

I can easily count number of #N/A's in a column with the array formula:
=SUM(--ISNA(A:A))
How would I count the number of #DIV/0!'s in a column ??

**1**

vote

**1**answer

22 views

### Suggestions on how to integrate a very large block of text into Excel

I've been working on an Excel assignment where I have a data table of information, and beside it I want to write my analysis and recommendations for each of the lines of data.
Some criteria:
I don'...

**1**

vote

**2**answers

102 views

### Excel: sum values in multiple columns within each row if criteria in another column (also multiple) are met [closed]

Please refer to the image attached containing a sample table that is similar to the actual workbook:
I want to write formulas in columns B-D such that for each customer,ratings are added based on ...

**1**

vote

**1**answer

21 views

### Excel 2013 IF/AND statement using more than one workbook in a spreadsheet

I am trying to set up a sort of schedule in Excel 2013.
What I want to do is have an individual workbook inside the spreadsheet for each of the 7 people I am trying to make this for, on these ...

**0**

votes

**0**answers

35 views

### Stumped with how to write Excel formula: How to check if multiple values in a single row are the same in two different sheets?

I'm sorry, I know that title must sound confusing but I'll try to explain it better here.
Essentially, I have to check if a row in a Sheet2 has two of the exact same values in another row in Sheet1. ...

**0**

votes

**1**answer

20 views

### Excel 2010 Conditional Formatting - cell/text colours based on value

I have a query regarding conditional formatting in Excel 2010. I have a date in cell P2.
The function I would like is:
In cell Q2 if a date is entered that is 7+ days after the date in cell P2, the ...

**0**

votes

**1**answer

14 views

### Excel auto Text formatting based on values within a range of cells

Need help finding a way to auto format a text response based on values in another set of cells. There are 3 values in Column B,C, and D and depending on the highest value of the three, the adjacent ...

**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 ...

**1**

vote

**1**answer

29 views

### excel create a custom vba function?

Can anyone help me create a vba function to execute the following?
In Sheet1 I have 3 columns A(product) and B(price) which are populated with 3000 items and 3rd column C(quantity) which is not ...

**0**

votes

**1**answer

44 views

### Formula for Ascending Sequence

I am trying to help someone generate a formula to return a simple sequential series of values. For example in cell C6 we inserted the formula:
=ROW(A1)
and in cell D6 we inserted the formula:
=...

**1**

vote

**1**answer

22 views

### Find second latest date, billing number and stock

If the quantity show zero or negative amount, I would need to find for the second latest date, billing number and quantity by using batch number.
Sorry that I only know how to attach image instead ...

**1**

vote

**1**answer

38 views

### Excel replace multiple text options within one cell with other text

How can I replace one set of text options with another? I was thinking that an Index/Match combination might work, but I cant figure it out. Perhaps a VLookup function?
For example:
My report output ...

**0**

votes

**2**answers

37 views

### How do I compare a cell value with a range of values on 2 dirrerent sheets and return a Text I specify

In Excel I have 3 sheets in the same workbook.
I want to compare column A on "Sheet 1" with Column B on "Sheet 2" and Column b on "Sheet 3".
If I find an exact match on anywhere in column B "Sheet ...

**0**

votes

**1**answer

62 views

### Make a formula that has specific-cell-references change every n'th row in Excel

I have a big Dataset it is structured in such a way that always 16 rows belong to the same group. (e.g.: A1, A2, A3, A4, A5, A6, …A16 -> Group1; A17, A18, A19, A20,…,A32 -> Group2..etc.)
I would like ...

**1**

vote

**1**answer

77 views

### Deriving elegant or 'nice' axis values on graphs using Excel - lookup function or formula

I am trying to automatically calculate the most elegant y-axis scale (maximum, and division value) for some bar charts. I'm using Excel integrated with a dashboard tool (SAP Dashboards) to display two ...

**0**

votes

**1**answer

41 views

### Editing CSV file in Excel always converts number column to scientific notation [duplicate]

I have a CSV file that I'm editing in Excel 2013. In the file there is a column that contains numbers e.g 688087123456789. Everytime I open the CSV file in Excel, it converts the number to scientific ...

**0**

votes

**1**answer

26 views

### Excel Spreadsheet data movement

I have copied data from a main list on one tab on to another list on the next tab of the same sheet. I have done this by copying each cell across individually. Now, when I sort the original sheet (...

**0**

votes

**0**answers

17 views

### How to count each value occurrence within group of arrays in Microsoft Excel 2010?

I have a network related spreadsheet, and the part I need to work on is multiple groups of arrays that contain values (port numbers). I'm trying to figure out a way to make a pivot-table that counts ...

**0**

votes

**1**answer

1k views

### Excel Start Date with specific start Time

Date and time data is imported into excel from an outside source (excel see the imported data as Text so column Q is converting to date/time that excel will recognize).
I am using multiple formulas ...

**0**

votes

**1**answer

1k views

### If Function is not working in Excel

I had made a sheet to calculate factory workers timing and payment against working and over time.
First I made it in Excel 2013 and then I changed my Windows from 10 to 7 and also changed the ...

**1**

vote

**1**answer

26 views

### Excel2013 - Grabbing data from specific cells based on the selection from a drop down list

Depending on the selection from a drop down list, I need to match data from a specific cell into another cell. I have a list of Team Members and depending on which area they work in during a shift ...

**0**

votes

**2**answers

39 views

### Ignore blank cells for an average in Excel

I'm a little stuck with a sheet I'm creating at work.
Basically the sheet tracks performance across 3 fields for our suppliers: Delivery, Product Quality and Supporting Documentation.
I would like ...