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

**0**

votes

**2**answers

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

**0**

votes

**1**answer

21 views

### Excel Reporting Funtions

Bare with me as this question has a couple of elements to it...
I have a training matrix with Company Organisational Chart in first few columns e.g. Unit Departments and Position Titles in first two ...

**0**

votes

**0**answers

30 views

### Excel changes the total sum of a column when changing the row order

I have a large list of numbers, in a different cell I have the formula =SUM(G:G). I need to work with the data on the list for different purposes but when I change the order of the rows, my total on ...

**1**

vote

**3**answers

62 views

### Comparing two strings in two columns in Excel Sheet

Can anybody help me to correct this data? There is a long list of this type of data:
If there are columns that are exactly matched, like in row 5 (Harley- harley) it should return 1 or 'true' in ...

**0**

votes

**1**answer

33 views

### Creation of an IF formula that will return a 1 or 0 for times between a range

I am trying to create a spreadsheet that will calculate total number of patients per hour. I have an arrival time and a discharge time. What I was thinking is to organize my spreadsheet with times on ...

**1**

vote

**1**answer

30 views

### LibreOffice Calc: how to pad number to fixed decimals when used within formula?

In a LibreOffice spreadsheet, I want to display numbers (actually, prices) with two decimals. When a cell contains just a number, I can format it to do automatic padding.
But sometimes I have cases ...

**-3**

votes

**2**answers

69 views

### Excel date formula

Could someone look at my formula and assist me with a resolution? The formula must have an error since my amount is going into column AD instead of AC like it should. See highlighted cell.
For more ...

**0**

votes

**1**answer

32 views

### Return Non Null Cookies And Count

I have two worksheets in my workbook. The first worksheet is titled SalesPerson and should show a rundown of the salesman that is entered in cell B1 and the type & quantity of cookies sold. ...

**4**

votes

**1**answer

83 views

### Sorting data without mixing rows

I have three sheets. First sheet renamed General:
Dave Henderson 21/05/2014
Elton Rus 22/06/2014
John Smith 20/05/2015
Second sheet renamed ...

**0**

votes

**0**answers

14 views

### SharePoint data connection to Excel - “refresh all” turns all formula results to zero

I have a SharePoint 2013 list that is connected to an Excel spreadsheet. Data comes into the Excel sheet on the first tab.
I have formulas on the second Excel tab (specifically, SUMIFS-based formulas)...

**0**

votes

**1**answer

30 views

### Excel search for substring and display count

This is an extended question from this one.
Suppose now I have a following table with strings:
A
1 CHI111
2 SEA222
3 SEA333
4 LA444
...
I also plan to have a table to keep count of ...

**0**

votes

**1**answer

20 views

### Excel - Reference cells with same value and SUM

The excel data sheet I have takes this form:
XGTI, 10
XGTI, 12
BIOC, 5
BIOC, 5
BIOC, 1
BIOC, 10
XGTI, 5
BIOC, 1
MGT, 2
MGT, 4
I would like to total all the values separately from each other.
e.g.
...

**0**

votes

**1**answer

37 views

### Excel Formula that autofill an array with a list, based on an array

I'm trying to do a formula that within a range makes a list based on info on a specific array.
What I need is an array that aufills this list based on the following conditions:
1)It has to be the ...

**0**

votes

**1**answer

21 views

### Excel search for string in multiple cells, compare and display string with most counts

Excel 2013
I have a table as follow:
A
1 CHI
2 SEA
3 SEA
4 LA
...
How do I write a formula that can check cells A1 ~ A4 and display on another cell the string with most count? In this ...

**1**

vote

**1**answer

43 views

### How Can I make an excel column of Hyperlinks open in NON default browser

I have a ms excel 2007 worksheet with a column (A) of hyperlinks using the excel function in this way =HYPERLINK(A2,"url".
How Can I make (if this is possible) open all cels on that column in the NON ...

**1**

vote

**1**answer

32 views

### SUMIF based on multiple criteria

I want to get the sum of a range, but I want to change how the sum us calculated based on critera.
Say I have a table as follows
Name | Value
DP | 2
DP + BC | 4
DP | 4
DP + BC | 6
So ...

**4**

votes

**2**answers

167 views

### Sort by Formula (without VBA)

I am attempting to sort a short column of values (descending) by using a formula in a different column. So if A1 through A10 contain numbers, in B1 I enter:
=LARGE($A$1:$A$10,ROWS($1:1))
and copy ...

**0**

votes

**1**answer

39 views

### Formula to reference a cell value that contains text and retain the cell refences formatting

I have a bunch of cells containing text. The text in those cells have multiple font sizes and styles. I need to replicated those cells in a different order further down the worksheet and if I make ...

**0**

votes

**1**answer

77 views

### Excel formula to find lowest value above a given number and return the column name

I'm trying to write an Excel formula that will look at 5 columns of data (for example, labeled SEL1, SEL2, SEL3, SEL4, and SEL5) and return the column name of the lowest column with a value above a 0....

**3**

votes

**2**answers

230 views

### Excel vlookup reference purposely missing shows #N/A - stop it

I have a cell which does a vlookup.
But the table to which it refers is always changing and when the specific value is there is shows fine.
But when the value isn't there it shows #N/A - how can I ...

**1**

vote

**1**answer

45 views

### Calculating percentage change over a range of 5 cells where a different two will be populated on each row

I have 5 columns for 5 consecutive years.
I need to calculate the percentage change between the first value present across the column range and the second value present. The other three columns will ...

**-2**

votes

**1**answer

30 views

### advanced if excel formula

I want to write an excel formula that calculates cell x divided by cell y, but if the resulting value is between 0 and -.3 return a value of 0, but if less than.-.3 then return the inverse square of ...

**-1**

votes

**2**answers

26 views

### Sum with multiple criteria and vlookup from another table

In the table below, I want to sum all of New York ACT in Q2 after the sales have been multiplied by the corresponding rates. Is there a one-shot formula I can use without needing to multiply all the ...

**1**

vote

**0**answers

66 views

### Excel 2010 - Combine specific sheet in >200 separate files into single worksheet?

I have tried solutions in other posts like this and so far nothing has worked :/
I have >200 separate excel files, and essentially I would like a specific sheet from each of these files combined into ...

**0**

votes

**1**answer

24 views

### Sum of specific cells, up to a given amount

I am attempting to create an overflow spreadsheet using formulas. The "Starting Petty Cash" (on the right) is the carryover from the previous term, and is manually entered. The "Count", just above ...

**0**

votes

**1**answer

26 views

### Use SUBSTITUTE formula to remove text from string in Excel, with text to remove being referenced from another cell

I have the following sheet setup
Column A - Brand Names
Column B - Product Names
The product names are structured as:
Nike Air Force White
Addidas Trainers Black
I want to remove the brand name ...

**0**

votes

**1**answer

33 views

### Sum multiple items with multiple criteria

Given the following table, let's say I want to sum the New York "ACT" sales in Q2.
The answer would be 59. How do I do that with a formula?

**0**

votes

**1**answer

39 views

### Data validation to give text a value that's used in a formula elsewhere on the sheet

We have a group planning to charter a sailboat as a vacation for 10 days. I'm trying to show the total costs of the trip. It's a simple spreadsheet listing many semi-fixed costs like food, beer, ...

**0**

votes

**1**answer

25 views

### If statement with indirect problems

My sheet is set up with I1 as a drop box for other tabs.
A1 and B1 Formula works and pulls the info from the cell in the tab if there's info, with slight changes in formula for cells but you can get ...

**0**

votes

**1**answer

32 views

### Trying to create a SUMIF or SUMPRODUCT with one positive criteria and one negative criteria

I need to perform a SUMIF, SUMPRODUCT, or similar formula with TWO criteria, one a positive match and the other a negative match. Refer to the very shady table below:
A........| B.............| C.......

**0**

votes

**1**answer

27 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

50 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

31 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

36 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

53 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

31 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

29 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

39 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

56 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

27 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

38 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

445 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

36 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

85 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

39 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

39 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

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