# Questions tagged [worksheet-function]

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

**0**

votes

**0**answers

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

**0**answers

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

**0**answers

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

**1**answer

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

**0**answers

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

**1**answer

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

**2**answers

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

**2**answers

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

**0**answers

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

**1**answer

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

**3**answers

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

**0**answers

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

**6**answers

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

**0**answers

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

**0**answers

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

**1**answer

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

**1**answer

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

**0**answers

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

**2**answers

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

**3**answers

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

**1**answer

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

**0**answers

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

**5**answers

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

**2**answers

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

**0**answers

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

**1**answer

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

**0**answers

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

**2**answers

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

**3**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**3**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**0**answers

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

**1**answer

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

**0**answers

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

**2**answers

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

**0**answers

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

**2**answers

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

**0**answers

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

**1**answer

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

**0**answers

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

**2**answers

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