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

learn more… | top users | synonyms (5)

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