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
0answers
11 views

Excel Match / Index - Fill in blank cells based on two other cells matching

I need to fill in blank cells with valued cells based on matching cells in two different worksheets. MasterCAP Table BESTRFS Table The cells in MasterCAP[RFS_ID] must be filled in with the value in ...
1
vote
0answers
14 views

Is there a way to dynamically specify an array in Excel?

Yesterday, I found this example of data and some array formulas: The idea is to use a formula to calculate the number of employees in Departments A and B combined. One solution offered was to use an ...
-4
votes
0answers
22 views

Excel formulas in sheet [on hold]

"A" columns lines Value for Negative & Positives Both together. If B column "DT" means, A columns amount should be reflect in C column converted Positive figures only If B column "CT" means, A ...
0
votes
1answer
17 views

Should I use, COUNTIF, Array or something else in my Excel Sheet?

I have a spreadsheet from which I want to pull certain "count" information. In column R I have a list of hours and in column T I have a list of names (an array created from all the names in the ...
0
votes
0answers
19 views

Excel formula for calculating product of numbers in 4 rows, 3 columns each

I am using Excel 2007. I am trying to make a small table with three columns: BOXES | BAGS | KILOGRAMS The person using the table will be entering information regarding a production process, so for ...
0
votes
1answer
41 views

Excel 2016: Return total quantity of matching part #s over multiple lines if conditions are met

I have 1 sheet that needs to get a total quantity of a part number from a 2nd sheet based on if other columns in the second sheet meet specific criteria. Here is an example: And I am trying to use ...
-1
votes
2answers
46 views

Writing an IF() formula to use an alternate cell if the primary cell doesn't have a value

I'm trying to write a formula to subtract two cells but only if they have values. If there isn't a value in one of them I want to use a different cell in the subtraction. For example: | A | ...
0
votes
2answers
22 views

Excel auto incremental number in a formula

I need to make a formula that has a number in it which I would like to be auto incremented once I hold and drag the formula along a row. For example my formula (simplified) is: =500 + 1 And if I ...
0
votes
0answers
21 views

In the same worksheet of Excel, I need to pull data from a cell and have it copied into another cell as I type it in [on hold]

I will try to explain this as simply but as detailed as possible. Data(data will be letters not numbers) in cell A1 will have corresponding data for cells F1 and G1. There will be more data in A1 ...
0
votes
1answer
26 views

Excel formula for sum of income over multiple years adjusted for inflation

I have the income X, and the number of years in the future Y, and the inflation Z. I can calculate the compound interest simply by CI(Y) = X*(1+Z)^Y for any given year, but how can I construct a ...
1
vote
3answers
45 views

Find and move only the email addresses from one column to another

I have a client list that contains name, address, city, state, zip code, but some randomly have an email address instead of a street address. I need to move the email address out of the address ...
1
vote
0answers
10 views

How to use old dot notation for ranges in Excel formulas?

As a veteran spreadsheet user I am accustomed to using dots for ranges. Excel supported this in previous versions: For example, if I entered =sum(b2.b21) then when I hit enter Excel converted it to =...
7
votes
6answers
502 views

Apply any formula n times without using VBA? [on hold]

I have the following cells: A1: justsometext B1: 3 C1: =DOSOMETHING(A1) I want to apply the formula in C1 n times (n being 3, the value in B1), so in this case it would mean: C1: =DOSOMETHING(...
-1
votes
0answers
29 views

Work related project Excel 2010

I am by far not an expert and am only beginning to see the complexity that is Excel. I am trying to write a formula (if it is even possible) that will allow me to make a condition that if a certain ...
0
votes
0answers
21 views

Excel Questions with lots of different problems [on hold]

I'm developing a spreadsheet that will chart the Relational Value of a client. I'll likely need several separate formulas, but struggling with all of the formulas. What I would like to have ...
2
votes
1answer
89 views

Find position of first uppercase character in excel string

I need to find the position of the first uppercase character in an Excel string. There are lots of resources on the web that have the same (or effectively the same) solution for this: =MIN(IF(ISERROR(...
0
votes
1answer
34 views

A text box which is visible across all the worksheets

I have an Excel workbook with 8 worksheets. I want to create a TextBox with an arrow attached to it and place it at the bottom left Corner, right above the worksheet's names. I want this text box to ...
0
votes
0answers
39 views

Excel formula Function [on hold]

I have to update the below user name one by one in 10 counts wise, after make the filter below mentioned points ( A & B Points ) if any formula or VB please help me. SR.NO User Name 1. ...
0
votes
2answers
20 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. ...
1
vote
3answers
37 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
43 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
56 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 (...
8
votes
5answers
3k 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
35 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
50 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
45 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
43 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
38 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
72 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
49 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
37 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 ...
0
votes
1answer
21 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
3answers
61 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
30 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
28 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
30 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
25 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
55 views

Overlap in no of days calculation [closed]

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
26 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
70 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
48 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 ...