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

**3**

votes

**4**answers

77 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

24 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

51 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

55 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

43 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

20 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

99 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

20 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

34 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

28 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

35 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

75 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

37 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

25 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

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

**0**

votes

**1**answer

21 views

### Excel Reporting Funtions

Bare with me as this question has a couple of elements to it...
I have a training matrix with Company Organisational Chart in first few columns e.g. Unit Departments and Position Titles in first two ...

**0**

votes

**0**answers

29 views

### Excel changes the total sum of a column when changing the row order

I have a large list of numbers, in a different cell I have the formula =SUM(G:G). I need to work with the data on the list for different purposes but when I change the order of the rows, my total on ...

**1**

vote

**3**answers

62 views

### Comparing two strings in two columns in Excel Sheet

Can anybody help me to correct this data? There is a long list of this type of data:
If there are columns that are exactly matched, like in row 5 (Harley- harley) it should return 1 or 'true' in ...

**0**

votes

**1**answer

32 views

### Creation of an IF formula that will return a 1 or 0 for times between a range

I am trying to create a spreadsheet that will calculate total number of patients per hour. I have an arrival time and a discharge time. What I was thinking is to organize my spreadsheet with times on ...

**1**

vote

**1**answer

30 views

### LibreOffice Calc: how to pad number to fixed decimals when used within formula?

In a LibreOffice spreadsheet, I want to display numbers (actually, prices) with two decimals. When a cell contains just a number, I can format it to do automatic padding.
But sometimes I have cases ...

**-3**

votes

**2**answers

69 views

### Excel date formula

Could someone look at my formula and assist me with a resolution? The formula must have an error since my amount is going into column AD instead of AC like it should. See highlighted cell.
For more ...

**0**

votes

**1**answer

32 views

### Return Non Null Cookies And Count

I have two worksheets in my workbook. The first worksheet is titled SalesPerson and should show a rundown of the salesman that is entered in cell B1 and the type & quantity of cookies sold. ...

**4**

votes

**1**answer

80 views

### Sorting data without mixing rows

I have three sheets. First sheet renamed General:
Dave Henderson 21/05/2014
Elton Rus 22/06/2014
John Smith 20/05/2015
Second sheet renamed ...

**0**

votes

**0**answers

14 views

### SharePoint data connection to Excel - “refresh all” turns all formula results to zero

I have a SharePoint 2013 list that is connected to an Excel spreadsheet. Data comes into the Excel sheet on the first tab.
I have formulas on the second Excel tab (specifically, SUMIFS-based formulas)...

**0**

votes

**1**answer

30 views

### Excel search for substring and display count

This is an extended question from this one.
Suppose now I have a following table with strings:
A
1 CHI111
2 SEA222
3 SEA333
4 LA444
...
I also plan to have a table to keep count of ...

**0**

votes

**1**answer

20 views

### Excel - Reference cells with same value and SUM

The excel data sheet I have takes this form:
XGTI, 10
XGTI, 12
BIOC, 5
BIOC, 5
BIOC, 1
BIOC, 10
XGTI, 5
BIOC, 1
MGT, 2
MGT, 4
I would like to total all the values separately from each other.
e.g.
...

**0**

votes

**1**answer

36 views

### Excel Formula that autofill an array with a list, based on an array

I'm trying to do a formula that within a range makes a list based on info on a specific array.
What I need is an array that aufills this list based on the following conditions:
1)It has to be the ...