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

**0**

votes

**1**answer

26 views

### Count the amount of times a value appears combined with a certain event

I basically have the following list:
How can I count the amount of times John won?
I have tried using both countif and countifs but have find no positive result. I either get "#VALUE!" or a simple ...

**2**

votes

**1**answer

46 views

### Excel : How to output a list of data that satisfies a range?

I have a list of numbers (single column), A,
and two values B and C which are calculated from the list of data, A.
I would like to output all data A in the same sheet (but in a different column) that ...

**0**

votes

**2**answers

25 views

### Formula for excel (return largest number for subtraction of different cells and which cells were used for answer)

I'm trying to return the largest number for the difference of different cells - ie, return largest number after computing a1-b1, a1-c1, b1-c1. Assuming
a1=3
b1=2
c1=1
then number returned is 2 (...

**0**

votes

**1**answer

28 views

### index and match 2 criteria with COUNTIF

I know many questions have been asked for using INDEX and MATCH with criteria and returning a unique list without duplicates. And I have that much working. But I ran into a problem.
Here is my data:
...

**0**

votes

**1**answer

44 views

### Run formula on cell click

I'm trying to run a macro that runs the following formula when a cell is clicked and populates it with the rounded figure:
= Round((A1 + "0:02") * 96, 0) / 96
This takes the time from A1 and ...

**0**

votes

**2**answers

21 views

### Excel sum if based on multiple criteria

I need to sum values in one column where two other columns meet certain criteria.
Column A has the item name
Column F has its value
Column G has the date it was bought
What I need to do is get the ...

**0**

votes

**3**answers

29 views

### Having one cell to reference different cells based on another call's value

I am looking for a formula that allows one call to reference a specific cell based on the value of another cell.
For instance
If cell A1=10 (I manually put in this value), Cell B2=C3.
If Cell A1=12 ...

**0**

votes

**0**answers

10 views

### Numbering cells in excel with relation to another cell

I have data of many rows with few blank rows/text rows in between. position of blank rows differ every time. I want to give one number (say 1) to all the rows before blank rows, next number (say 2) ...

**-1**

votes

**2**answers

28 views

### Working out addition Formula of Months in Excel

Is there any sort of formula that can sum up together how much of the actual dates are in 1 Column? Hazy explanation but I'll explain;
So you have in a column, 01 May 2016, 02 May 2016, 06 May 2016, ...

**0**

votes

**1**answer

37 views

### Excel weight calculations

I'm really new to excel and was wondering how I could take the info below and if one of the items was not a full box, and contains say 5 eaches of that product instead of 40, how I could enter that ...

**3**

votes

**1**answer

35 views

### How to define an alias for user function in Excel?

I'm looking for a way how to define some kind of alias in Excel. Let's say I have this long function to categorize/tag every line in home expenses list:
=IF(ISNUMBER(SEARCH("Curry",$F2)),"Curry",...

**0**

votes

**1**answer

10 views

### Ability to perform lookup in a different file based on dropdown

I want to perform a lookup in a different file based on a drop-down list.
Twist - I want the drop-down selection to create the reference to the other file. So if I selected May 2016 from the drop-...

**0**

votes

**1**answer

26 views

### Average of formatted numbers appears incorrect

I have three cells I'm averaging.
a1= 19.5
a2= 19.5
a3= 19.5
a4= 19.5
a5= 19.4
a6= 19.48 <<== result of average above ^^^
I have a cell formula that I calculate this by doing this:
a6=IF(...

**0**

votes

**1**answer

37 views

### Excel -formula that changes depending on input in another cell? Is that possible?

It should be quite easy I guess but I could not find an answer.
Lets say that in the cell A1 I will be putting an alphabet letter from A to Z.
Then in cells A2 I need a formula that will be sort of ...

**4**

votes

**7**answers

429 views

### How to convert text with middle blank space to a number in Excel?

I am not able to convert a text value of 5 205 with a middle blank space into 5205 in Excel. If I leave the value like this, my report chart with sum displays 0.
If I try to convert the text using ...

**1**

vote

**2**answers

32 views

### EXCEL- Counting pairs/quads of numbers in rows. How to count?

I have 300+ rows containing 40+ columns, each column has a number 1-40 at random.
I want to know how to search each row for pairs or numbers that occur together within the row.
Example.
5 10 12 ...

**-1**

votes

**1**answer

50 views

### How to extract only specific numeric values from a range of data in MS Excel?

I have a range of data say a1:a100 in which there exist mixed up numeric and alphabetic values.
I want that only the numeric values which are greater than 1 and lesser then 10000000 (i.e. falling ...

**0**

votes

**2**answers

26 views

### Subtracting cells only if both have a value in Excel

I want to subtract two cells in excel but only if both cells have a value. More specifiaclly in my case the formula in cell C2 is currently =B2-A2 but I only want this to be calculated when B2 has a ...

**0**

votes

**1**answer

34 views

### “Foreign Key” VLOOKUP in excel?

I'm not too learned in Excel functions, so I'm not quite sure how to use a VLOOKUP to achieve my goal.
I have two worksheets in excel, and I need to get referential data from one sheet into another.
...

**0**

votes

**2**answers

43 views

### Excel - Formula to return a single value for each unique identifier

What I have:
A column that contains year plus week number which serves as a unique time identifier for my data, and a column that contains a value. Amount of rows varies. As you can see, Value is ...

**0**

votes

**2**answers

36 views

### How can I make Excel ignore special characters and spaces when taking data from another cell?

I have an Excel workbook which is used to produce quotations for customers. It automatically generates a unique quotation number based on the salesperson's name, the name of the company receiving the ...

**0**

votes

**1**answer

28 views

### Reference formula 'Text' within another cell in Excel [duplicate]

I would like to insert text from a cell into a formula.
For example, using the formula:
=LOOKUP(A1,'Sheet2'!B2:B10, 'Sheet2'!D2:D10)
I would like to change 'Sheet2' to some other reference (i.e. ...

**1**

vote

**3**answers

89 views

### Excel - How to return the first number only string that is between 7-10 characters long in a large body of text? (preferably Formulas only)

I'm trying to extract some numbers from a few cells that each have a large body of text.
The number strings are accompanied by certain words that precede the number string I'm trying to extract.
I'...

**-1**

votes

**1**answer

31 views

### Lock Drop Down Cells and Enable Use - Excel 2013

I would like a drop down list in a cell. I would like to lock the cell so it cannot be moved or altered but changes other than selecting from the drop down menu.

**-1**

votes

**1**answer

23 views

### If < formula is not working on all my cells

I need to determine if my players play slots or tables based on if they have higher slot or table points.I typed in the formula =IF(D2

**4**

votes

**3**answers

42 views

### Excel Formula-Return Value based on looking at two separate values (either/or)

I am using this formula to return a value from table below:
=IF(ISNA(VLOOKUP(A2,USUB,1,FALSE)),"No Fruit Found",VLOOKUP(A2,USUB,2,FALSE))
USUB table has this data:
Fruit Fruit Code ...

**0**

votes

**3**answers

47 views

### Variable sliding window in Excel

I am using Excel 2010, but I presume this would be solved the same in any version of Excel.
I have data that I would like to sum - essentially providing a sliding cumulative value. For example, on a ...

**0**

votes

**0**answers

17 views

### Creating an Excel timetable

I have created course timetables for groups of students. i.e. a timetable on a sheet for each day, with the students' names across the top. What I would like to do now is to combine the five days into ...

**1**

vote

**1**answer

28 views

### Need to Reference multiple sheets with IF(OR(AND( imbeded

I need to reference 3 sheets, "Shipping", "Equipment", and "Inventory", where Column A in Shipping and Column A in Equipment both refer to the Location Number, and Equipment Type is designated by text ...

**0**

votes

**1**answer

19 views

### Excel - Multiplying daily values for each week

What I have: over 200 rows, each with three values, each for a unique day. Only values for workdays are listed, so sometimes there may be days or even whole weeks missing from the list because of ...

**-1**

votes

**2**answers

54 views

### Automatically removing invalid or gramatically incorrect entries in Excel [closed]

I need to clear out all the incorrect data from my Excel sheet, such as grammatical mistakes and invalid entries, and I want to do it automatically.
I have tried the sort and filter option, but it ...

**0**

votes

**1**answer

16 views

### Allow Excel user to select operator for formula

I'd like to setup a spreadsheet that would let the user select the operator to apply to other cells. For example: If A1 has 5 and A2 has 10, I'd like the user to be able to select the plus or minus ...

**0**

votes

**1**answer

44 views

### If a range contains specific value, how to produce a percentage of that range below the specific value in Excel

I'm stumped.
I am arranging a data set that contains ranges of numbers that are specified by two columns, the minimum and maximum values (sometimes these values are the same). I am interested in the ...

**2**

votes

**1**answer

41 views

### Generating Repeating Data

I have 4 data items in column B. Initially I needed to fill another column with the original data repeated 7 times. So the first item should appear 7 times, then the second item should then appear 7 ...

**1**

vote

**0**answers

30 views

### Excel doesn't create structured formula for all tables

Structured formula case
I am facing 2 weird issues with Excel 2013. I have been mainly using the tables. Initially when I created the sheet and inserted tables and added formulas, they were all using ...

**0**

votes

**1**answer

39 views

### Spreadsheet formula for adding all values adjecent to multiple instances of text cells

Given a table with unique and duplicate text values in a column with number values adjacent in the following format:
A|12
B|23
C|98
A|66
D|86
E|3
B|25
A|65
.
.
.
The values are simplified for the ...

**0**

votes

**1**answer

37 views

### Creating a formula that analyzes whether or not individual was served between two dates

I am writing a grant report in which I need to count the number of individuals who received a service during a specific quarter. That is, if the person was in our shelter any day between 10/1/2015 and ...

**0**

votes

**1**answer

16 views

### Excel indirect function to name a sheet

I am attempting to have the worksheet of an external workbook update to a reference cell on a current work book.
This is my code so far.
=INDEX([Test.xlsx]indirect("I8")!$A$3:$B$7, MATCH(I8,[Test....

**0**

votes

**1**answer

24 views

### Formula not referencing new row

In Excel 2016 I have a workbook with two tabs -
Active_Directory_Locations
SuperTab
In SuberTab I use the following formula, copied in to cells A5-A1000 -
=IF(OR(Active_Directory_Locations!$D5 = "...

**0**

votes

**1**answer

33 views

### Make a column hidden by a function in excel

I want to hide a column by using a function. More clearly, what I am trying to implement is, for a particular type of data bundle, which is arranged as column1, column2, column3, etc, a number is ...

**0**

votes

**2**answers

59 views

### How to count rows with two values in any of the many columns in Excel?

Say I have some Excel columns that look like this:
A B C D
1 Amy Bob Carol David
2 Carol David David Carol
3 Bob Carol Amy Amy
4 David Amy ...

**0**

votes

**3**answers

32 views

### Leaving a cell blank in excel, or display a different cell

I've tried to read the other posts and follow but I just can't wrap my head around this formula.
I would like F9 to:
be blank if no data is entered in D9
if data is entered in E9, then D9*E9
if ...

**0**

votes

**1**answer

46 views

### Microsoft Excel calculation inquiry

I have a distribution spreadsheet that has a "total previous distributions" column showing the (obviously) previous distribution for each partner.
This amount is manually update by adding last ...

**0**

votes

**1**answer

49 views

### Excel Dynamic Chart- Range Name based on IF formula not accepted as 'Series Name'

I have created a simple Dynamic Line Chart in Excel 2013 as seen below. As I change the Year in Cell D1, the Series on Y Axis changes accordingly. The X Axis series is static. When I use Excel Range ...

**0**

votes

**3**answers

51 views

### Excel 2010 Specific cells for formula input cell arguements

Col A Col B Col C
Row 1 a a a
Row 2 a a a
Row 3 a a a
I am trying to set a specific range in the COUNTIF function, =COUNTIF(range,criteria).
Example 1: ...

**0**

votes

**0**answers

31 views

### Cross sheet trace dependents not working when using IF TODAY formula

Here's how to reproduce the issue:
Put the following formula in Sheet1, cell A1:
=IF(TODAY()>0,Sheet2!A1,A2)
Trace precedents works fine, trace dependends from A2 also works. However the cell ...

**0**

votes

**2**answers

36 views

### How to make excel SUMUP values of multiple rowkeys in a two-dimensional lookup to return sumed up values in a corresponding cell

So I have this challenge with a set of data arrangement and by merging the VLOOKUP and MATCH strings I was able to get my result but there is a little hiccup!
Pasted below is the merged string that ...

**0**

votes

**1**answer

34 views

### Reference cell on another sheet dynamically?

Basically I have a column of tab/sheet names. I then want to build hyperlinks to those sheets and make the friendly name a value from the target sheet. I have the first part down, thanks to a ...

**0**

votes

**1**answer

30 views

### Excel formula based on date variables

I'm trying to create a fee calculator but I can't seem to get the formulas to work for me. I'm essentially trying to write one long formula that takes into account all of the different date variables. ...

**0**

votes

**2**answers

27 views

### Subtract Two Cells. Result value

I want to subtract two numbers. The numbers are categorized, e.g. cell N2 (WAS) and N3 (IS). The cells populated are N1 thru N351. I want to calculate the difference between the two cells. If the N2 (...