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

learn more… | top users | synonyms (5)

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