Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [worksheet-function]

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

0
votes
1answer
8 views

How to make addresses populate from one spreadsheet to another

I have two excel sheets of store #'s/event details and another one of all the store number addresses. Not every store gets an event so just copying and pasting all the addresses wouldn't be an option. ...
0
votes
3answers
15 views

Excel saying too many arguments for IF/AND/MONTH function

I need it to produce orders that are in Canada or Mexico, over $1,000, and in the month of July. The formula I am trying to use is: =IF(OR(A2="Canada","Mexico", IF(C2>1000, "Ship by Air", IF(...
0
votes
1answer
16 views

How to move values from one column to another based on matching values from other columns

Example of what I'm trying to do: Hopefully the attached image helps you understand what I'm going for. I have 2 massive Excel files, one with names & income in the column next to them and ...
0
votes
0answers
42 views

How do I get one cell to reference two other cells, so its selections change off those two?

I am making a write up sheet for my company for employees. They want me to make it as simple as possible to stop errors. I need help with the employee name selection. Basically, there is 3 positions (...
6
votes
4answers
1k views

Combining first two letters from first name and first two letters from last name

I have a spreadsheet of usernames. The first and last names are in the same cell of column A. Is there a formula that will concatenate the first two letters of the first name (first word) and the ...
0
votes
2answers
22 views

Finding the difference in quantity of matching items from two sheets

I have two different sheets in Excel with two columns in each sheet. The first column contains the name of the item and the second column the quantity. How do I get the difference in the quantities? ...
0
votes
0answers
15 views

Drag-to-duplicate row, preserving data underneath, in Calc

I have a spreadsheet in LibreOffice Calc that I use for invoices. In this is a row that lists task and cost. When I select the cells in this row, I can drag the lower-right corner and duplicate the ...
0
votes
1answer
33 views

Identify gaps in a sequence in one cell - Excel

I have a list of items in my Excel sheet that are numbered, together with a total number, in the following way: Example: | Column A (available) | Column B (missing) | Column C (total) ___|...
0
votes
0answers
37 views

EXCEL: SUMIF In Array Formula Causing Errors (Bug?)

I have noticed that SUMIF does not play nice with array formulas. To save on convoluted explanations of my motivation, here is a sample that demonstrates the issue: =SUMIF(ROW(INDIRECT("1:10")),"<...
1
vote
2answers
37 views

Comparison of strings containing inequalities in excel countif() function

I'm analyzing some survey data in which the responses are in the form of a string containing inequalities, e.g. a) < 5 b) 5-10 c) 11-15 d) > 15 I'm attempting to tabulate responses using the ...
0
votes
3answers
61 views

Sum rows based on criteria in columns

I'm stuck with a problem, I have two sets of data like below: How can I find if the sums of column "a" based on ID in Table 1 are equal to column "b" based on ID? I'm assuming i need a Sumif and ...
-1
votes
1answer
42 views

What is Excel #N/A error abbreviation, looking for?

In MS-Excel we have below functions return errors: #N/A #REF! #NAME? #DIV/0! #NULL! #VALUE! #NUM! I think about what is the complete error massage, which each one are represents? Of ...
0
votes
1answer
35 views

Excel - Search for a string then add some values (Entered to many arguments)

I have 5 sheets with country data, I want to lookup the country name on 4 separate sheets within 4 separate ranges then take a value from the row and add all those values. I was getting #NA as a ...
-1
votes
0answers
16 views

Leader board rank system in excel [on hold]

Can anyone tell me how to build a formula in Excel, that will allow to me to take team points and rank from first to last?
-1
votes
0answers
21 views

Excel Functions [on hold]

I'm attempting to make a calendar for auditing purposes. My goal is to input all data on one page, then have a list of upcoming audits on a different page So on the data page, I have: Company - ...
0
votes
1answer
19 views

Return and List different values in different cells

enter image description here I working on writing a formula that displays all users with status yes in different rows. Attached a small picture of sample data and how the result should look like. ...
1
vote
1answer
35 views

How do I use Excel to produce a table showing what plant species are likely to be present based on location and habitat?

I'm a botanist and I am trying to produce an Excel table that filters a very long list of species based on location and basic habitat. I have created a master table with 200 rows for each species ...
0
votes
0answers
16 views

Excel: Is it possible to set some cells on a different z-index? [closed]

In order to ease a task, I would require to set a portion of an excel sheet onto a different "z-index" (took from CSS z-index logic). Basically, my requirement, in order to be satisfied, is to being ...
0
votes
3answers
59 views

Finding highest numerical value from a range of hybrid data in Excel

I have a row of 6 alpha-numeric values as in the picture. I need a formula that will determine the three highest numerical values across each row, and then display the letters associated with those ...
0
votes
1answer
36 views

Count the number of times a combination of two values occurs in a cell

I want to count the number of time a combination of two values occurs in a cell. For example: ADD UINTRAFREQNCELL:RNCID=1,CELLID=3,NCELLRNCID=1,NCELLID=2; ADD UINTRAFREQNCELL:RNCID=1,CELLID=3,...
0
votes
1answer
27 views

Removing rows in an excel worksheet if duplicate is found in another worksheet

I have two worksheets: "compiled" and "firstpickup". What I'm looking to do is delete rows in the sheet "firstpickup" if the value in column A can also be found in column A of "compiled". "compiled" ...
1
vote
1answer
27 views

Latest Date from Attendance Sheet where attendance is noted as “P”

I have a list of names in Column A starting in cell A2 Each column heading starting from cell B1 contains individual dates As a person attends an event I enter P into the appropriate cell ...
0
votes
1answer
29 views

How to compare two table with duplicate key reference

I have two worksheets, and there are many columns and rows inside. The same key reference is " order number" with two worksheets. But unfortunately, the order numbers are duplicate. is there any way I ...
0
votes
1answer
29 views

Combining multiple excel formulas

I have three different formulas that I would like to combine into one formula; In cell I2 : =IF(ISNA(MATCH(CONCATENATE(A2,D2),INTRAFREQNCELL!E:E,0)),"NO","YES") In cell J2 : =IF(ISNA(MATCH(...
0
votes
1answer
20 views

I need to apply two formulas in one cell (one formula is logic one)

I’m currently working on a Risk Register document in Excel and have populated 2 separate columns: 1) Risk Impact and 2) Risk Probability Both of those fields can have values from 1 to 5. It depends ...
-1
votes
0answers
47 views

Overlap in no of days calculation [on hold]

I work at a rental company and was tasked with writing up a formula that calculates number of days in a month our various units are on rent which will be used to calculate revenue made on the unit for ...
1
vote
1answer
22 views

Excel: Sum [range] if cell equals value of another cell

| A | B | 1 | Value | Input | 2 | 1000 | 1000 | 3 | 3000 | 3000 | 4 | 5000 | 150 | 5 | 10000 | | 6 | 15000 | 3000 | 7 | 20000 | 20000 | ----------------- I'd like to know if ...
0
votes
0answers
18 views

How to retain internal connections (existing worksheet) in excel

I am having an Excel Master File, v1 and the data source for my list boxes and multi-list boxes are pointing to an external workbook, v2 instead of internally. As a result, my conditional formatting ...
0
votes
2answers
38 views

How can I use SUBSTITUTE to substitute an apostrophe?

I want to substitute spaces and apostrophes with hyphens. I use this formula. =SUBSTITUTE(SUBSTITUTE(C129," ","-"),"'","-") This has replaced the spaces but ignores the apostrophes. I then tried =...
-1
votes
0answers
52 views

Adding or Subtracting based on 3 variables

This may be simple but I'm new to formulae in Excel. I've got two sets of data that both have Dates in one sheet. One set has repeated dates for every Type and every State because it's a cumulative ...
-3
votes
2answers
65 views

Excel: how to count and group rows

I have an Excel file with Column A and Column B. Both have people names (Elizabeth, John, Michael and Robert). I need to count each name of Column A and count each name of Column B. And then I need ...
0
votes
0answers
20 views

How to enter an array formula within another formula

I have an Excel sheet, which holds timings for reports (when were they started, first handled, still open or already closed, ...). For clarity in the Excel formulas, I've given a name to all columns, ...
0
votes
1answer
34 views

How does Increase decimal and Decrease decimal work in Excel

How the increase decimal operation is performed in Excel and how they done it? Case 1 : I enter a number in a cell whose number of digit is less than 11. For instance A1 cell value be : 123 If I ...
-6
votes
0answers
47 views

Please help me check whether this excel IF function has too many arguments?

=IF(S9=1,IF(P11=1,IF(AD5=1,AH5,IF(AD5=2,AH6,IF(AD5=3,AH7,AH8))),IF(P11=2,IF(AD5=1,AI5,IF(AD5=2,AI6,IF(AD5=3,AI7,AI8))),IF(P11=3,IF(AD5=1,AJ5,IF(AD5=2,AJ6,IF(AD5=3,AJ7,AJ8))),IF(AD5=1,AK5,IF(AD5=2,AK6,...
0
votes
2answers
29 views

Combining Formulas add percent and roundup

How can I combine the following formulas? =f4*0.54 and =roundup(d4,0) In cell D4 I have the formula =f4*0.54, which gives me our cost from an MSRP cost that I entered in cell F4, but the cost that ...
0
votes
0answers
49 views

excel formula to find date between two columns

I'm newbie to excel. So please forgive me if anything found silly. I have two sheets with couple of similar columns, rest everything is different. Now what I was doing is: In first sheet (say sheet1)...
0
votes
1answer
38 views

EXCEL - Nested Ranges for references

thanks for reading my question. I'm trying to find price points for various sizes of product packages. I have a data range on Sheet1, A1:Z102, with the top row as mass of the packages (B1:Z1), and ...
1
vote
3answers
91 views

Search a field based on two or more columns

I want to output the professor field based on the input in the yellow fields. The input will be the possible sub_code of column D OR column G. I've tried VLOOKUP(...) and INDEX(...,MATCH(...)), but I ...
3
votes
1answer
67 views

Excel: Keep cell containing date the same after being set, without macros

I have a table that looks like this: What I want to achieve is that when you fill in a row (other than the 'Time' section), the 'Time' column automatically fills itself in. Then, it stays the same ...
1
vote
1answer
33 views

Excel: Count cells in matrix with multiple conditions

I have a table table1 with a matrix of n columns [[column_1]:[column_n]] that contain dates or empty fields. Also there's a column [number] with integers. I want to count all cells that contain a ...
0
votes
0answers
14 views

Vlookup For time range and duplicate removal

My data is like in the picture and like to display it like the sheet. I tried the Formula: =SUM(--(FREQUENCY(IF(MOD(Table2[A],1)>=D2,IF(MOD(Table2[A],1<D3,MATCH(Table2[BBB],Table2[BBB],0))),...
0
votes
3answers
23 views

How to omit a row in calculations?

I have the following data (the first column is Excel's row number, the actual values of column A are 10, 10, ...) A 1 10 2 10 3 15 4 27 When making a SUM(A:A) all figures in the column are taken ...
0
votes
0answers
29 views

Excel changes formulas (on save?)

Sorry if the following is impenetrable, but I haven't been able to find a similar example: I have a (large) model that uses lots of range names. Recently, on saving, the formulas are changing the ...
1
vote
2answers
40 views

Sum cells based on the value of other cell

I am facing a problem that seems trivial in Excel. What I need to do is simply this: In short, I have a large file of prices linked to a few people and I need to know how much each person paid. I've ...
0
votes
3answers
37 views

How can I get Excel to choose the highest grade credits on my course and display my final grade?

I'm in my final year of university and my final grade is calculated by (0.2*second year grade) + (0.8*final year's 90 best credits). I study one course worth 30 credits and 6 courses worth 15 credits ...
0
votes
1answer
34 views

Excel formulae for priority sections

I would like to add the date C6 to E6 (if P1=30 days, if P2=60 days, if P3=90 days, if P4=1 year) and show total in I6
0
votes
1answer
47 views

Counting how often two different values appear with the same value in the neighbouring cell

I have an excel spreadsheet with one column for ingredients and one column for recipes. (see on the left in the picture below). I would like to create a matrix with all ingredients (as seen in the ...
0
votes
2answers
52 views

Fixing Excel timesheet by time in and time out per user and date

I have this: which is difficult to read, 'I' means time in and 'O' means time out. I want to transform it like this: I want to align each time in and time out to its respective employee ID and ...
0
votes
1answer
24 views

Can't get dynamic print area working

I have just finished building a report which will have a variable number of entries and would like the print area to adjust dynamically. The print area needs to run from B2 across to AD2, and down to ...
-1
votes
2answers
89 views

Looking for formula to search for particular pattern in Excel

I am looking for formula to search for "words" with a particular letter pattern, like INNSA1 or INMAA4 or INPTPB, where the word starts with IN and ends with 4, 1, 6, or B. The words are always ...