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

**0**

votes

**1**answer

17 views

### Excel 2010 Array (CSE) Formula: How to remove a part of contiguous (adjacent) range

I would really appreciate help on the following question:
Is there a way to write a formula that removes a segment from a contiguous range?
For example, say there is a formula ...

**-1**

votes

**1**answer

35 views

### Excel formula to Show (1,0) for specific ranges

The problem I am having is I only want to look at Order number(Column A) if they have more than 4 value. When they have 4 values I want to only look at the last 4 values in that order. In column D I ...

**1**

vote

**1**answer

19 views

### How do I create a summary of the same few cells on many different sheets in Excel 2013?

I have an Excel workbook with 140 sheets. I'm trying to summarize the same 3 cells in each sheet on a summary sheet. How do I grab the value of the respective cells on each sheet without manually ...

**1**

vote

**2**answers

45 views

### Excel 2010 Array Formula (CSE) Dynamic cell range/reference; index? offset?

This might be an easy question to some but I am completely lost so I would really appreciate any help out there!
A
1 10
2 20
3 30
4 40
5 50
6 60
7 70
...

**0**

votes

**1**answer

13 views

### Counting in Excel until a special integer comes up [closed]

I am trying to get column C to say what column D says (I have typed the value in column D). The problem I am having is that I can not find a way to tell Excel to stop counting when it comes across the ...

**-1**

votes

**1**answer

25 views

### Formatting - conditional - vlookup- values from one cell to another different worksheets based on value

I would like to be able to enter a part# in column L of sheet 2 and a formula would look through column A of sheet 1 for a match, and then return their price from column F of sheet 1 into column K of ...

**1**

vote

**2**answers

31 views

### openoffice calc: sum of a function of elements of a row

If I want to sum the elements of the first row (from the column a to x) of a calc document I can use =sum(a1:x1).
My question is about the possibility to calculate something like this
...

**0**

votes

**1**answer

40 views

### How do I Reference all non-blank cells after row A6 for a Formula

I want to use the following formula:
INDEX(A6:A94,MODE(MATCH(A6:A94,A6:A94,0)))
Except the exact number of rows is ambiguous (imported using VBA).
How can I reference all non-blank cells starting ...

**0**

votes

**0**answers

30 views

### Can someone help me with an excel function that would make merging sheets easier?

I have about 61 spreadsheets that I want to merge into one big workbook.
Int the one work book I have tabs that are named the same as the spreadsheets that I want to import the data into.
There ...

**0**

votes

**2**answers

42 views

### Excel function to find the last three items by date

I am looking to obtain the 3 most recent transactions in relation to the date. (The problem is that they are in the spreadsheet out of order,
and I don't want to do a sort.)
Date Amount
...

**0**

votes

**2**answers

29 views

### Prevent formula input in all cells in a column, but allow whole number input

I am in a bit of a sticky situation. There is a public spreadsheet that some of my coworkers are required to use for their job. They all collectively use it during their shifts to track product ...

**0**

votes

**1**answer

48 views

### Why is this IF, OR, AND function not returning TRUE?

Why is this IF, OR, AND function not returning TRUE in A848?
See image below to view screen shot.
I'm using this algorithm to search for " not " and " valid", "not valid", and "invalid" in the ...

**1**

vote

**1**answer

51 views

### Work out Number of hours still to Complete [closed]

I have a spreadsheet to keep track of training which I do. As part of the requirement, I have been given a set number of hours to complete per month (20 hours).
The formula I require is to work out ...

**1**

vote

**1**answer

65 views

### Formula to show date of the last day of the month specified in another column in Excel

For a month number in column A, I want the LAST day of that particular month in column B. Right now, I'm working with year 2014, so:
A1 number is 1 so B1 date is 01/31/14
A2 number is 2 so B2 date ...

**0**

votes

**1**answer

20 views

### Recurrent excel count

Is there a more elegant way to do this in excel?
=(IF(ISBLANK(O3); 0; O3-P3) + IF(ISBLANK(O4); 0; O4-P4) + IF(ISBLANK(O5); 0; O5-P5) + IF(ISBLANK(O6); 0; O6-P6) +
IF(ISBLANK(O7); 0; O7-P7) + ...

**1**

vote

**3**answers

47 views

### Excel 2010: count if using a formula

I've a table with
actual stock in column C
goods in arrival in column D
auto-reorder in column F
I need to count the number of rows when (C+D+F) > 0.
Every row must be counted only one time and ...

**1**

vote

**3**answers

25 views

### AVERAGEIFS with OR conditions

I've got a series of blood pressure measurements. I want to determine the average pressures during the day and during the night.
The first half of the problem is trivial:
...

**0**

votes

**4**answers

78 views

### Excel: Sum values on a column based on criteria* on other columns *(complex criteria)

See below for rewrite.
Please refer to the data below for the question. The very top A B C are columns and the far left numbers 1 through 15 are rows. The rest are cells.
A B ...

**0**

votes

**1**answer

55 views

### Obtain a row number from 2 criteria (with 1 fuzzy search)

I am attempting to create some graphs with dynamic cell references as indices. These graphs are based upon time stamps generated from a testing machine. I have a column of Boolean values (0 or 1) that ...

**0**

votes

**1**answer

18 views

### EXCEL: Icon Conditional Formatting Based on Range of Values

I am creating a spreadsheet for work and I need to format cells such that I get a green check mark when the value in the cell is between 100 and 101, yellow ! if it is between 97 and 100, and red X if ...

**1**

vote

**3**answers

44 views

### Writing excel formulae that reference columns by number rather than letter

Is it possible, and if so what is the syntax, to write formulae in Excel using the column's integer number rather than the lettering system that is the default? I know it is possible to change the ...

**1**

vote

**1**answer

29 views

### Getting a webpage title into Excel

I'm attempting to create a cell that will pull in a hyperlink based off of what is filled out in another excel cell. I have the hyperlink portion working, but I'd like to have a better label for the ...

**1**

vote

**2**answers

30 views

### DATEVALUE() gives different values for similar data

In one column, I have timestamps in the format Jun 10, 2015 11:36:51 AM
So, to get the date from that column, I created another column and used the formula
=DATEVALUE(CONCATENATE(MID(D2,5,2)," ...

**2**

votes

**1**answer

35 views

### Not getting desired output with VLOOKUP - Need assistance

I have Table 1, which looks like this -
A1 B1 C1
1 X Date11
2 Y Date12
3 Z Date13
I have another table, Table 2, that looks like this -
A2 B2 C2
1 M Date21
2 N Date22
3 X ...

**1**

vote

**1**answer

32 views

### Excel - how do I retrieve corresponding values from another worksheet?

I have a "DETAIL" worksheet which tallies numbers:
DETAIL WORKSHEET
(Column A) (Column E)
(Excel Row #) DATE TOTAL
...
700 6/8/2015 =sum(D701:D709)
701
702
703
...

**0**

votes

**0**answers

11 views

### Matching data between two separate worksheets and transposing the data from another column next to the matched data

I have two worksheets. I am trying to input the stock number next to the matched claim number. How would I go about doing this? The first sheet is my inventory list. The second is a list we get from ...

**1**

vote

**1**answer

46 views

### How to hide formula results in a cell until conditions are met - percentage discount [duplicate]

My question relates to this post, but with different formula criteria: How to hide formula results until conditions are met.
Here's the setup for a spreadsheet to calculate the percentage discount ...

**1**

vote

**2**answers

59 views

### Is there an excel function which displays a string but outputs a value when referenced?

Let's say if the function called "LABEL".
Then in cell A1 I would enter =LABEL(4,"Four Apples") and it would render as:
Four Apples
But if I performed a calculation, say =A1+2 I would get 6, not ...

**-2**

votes

**1**answer

23 views

### Need to sum values associated with specific words, in Excel [duplicate]

I want to sum values associated with specific words. For example, calculate Sum for Food:
Column A Column B
Food $8.50
Food $5.10
Maintenance $12.00
Laundry ...

**0**

votes

**0**answers

24 views

### Microsoft Excel 2011 concatenate each row in a cell

I am trying to use an if function for each row for a result in a single/destination cell. The if function checks if a row meets a criteria, if it does, it adds the row details to the destination cell ...

**0**

votes

**1**answer

30 views

### VBA Code Isn't “holding” ThisWorksheet Parameter

I want to retrieve one column of values from another workbook and populate a tab in my current workbook. This will ultimately go into a much longer module I've written that works great. I just wanted ...

**0**

votes

**1**answer

28 views

### Excel 2013 - Referencing multiple checkboxes on a different sheet

Good morning, Superuser! I am facing a pretty tough problem at work this morning...my supervisor wants me to do something I have never done before, nor can I find a solution online.
The spreadsheet ...

**0**

votes

**1**answer

36 views

### What formula would you use to add up a column based on results from two drop down menus? [duplicate]

I would like to calculate a column based on selections from two other columns with drop down menus.
For example :
In one column, "C" you have 3 options (A team,B team,C team), In column "F" you ...

**0**

votes

**1**answer

39 views

### Want to get the unique combination of data for dynamic number of rows/columns

I have a few rows (the number of rows should be dynamic) of data that I would like to combine, and get each unique combination. I am able to do that for a specific number of rows of data, but when the ...

**0**

votes

**1**answer

47 views

### Excel not adding decimal numbers correctly

If I have a cell (A1) with a constant of -1.1 and then 11 subsequent cells referring to the first A1, and then to each previous one consecutively, after 11 cells, I'm expecting an answer of 0. ...

**1**

vote

**1**answer

35 views

### Excel SUMIFS based on two criteria

I would like to total a list of minutes based on two criteria to make a graph after the data is calculated.
The item "Department" (Column C) and "Day of Week" (Column F) are both drop down menus.
I ...

**1**

vote

**2**answers

37 views

### Excel formula daily average of weight

I am looking to set up an excel spreadsheet to calculate cattle weight gain per day. We weigh our cattle once a week so column A will be a date and Column B will be the weight on that day(day of the ...

**0**

votes

**1**answer

58 views

### Excel Run Length Encoding

If I have a column of data in Excel that looks like:
1
1
1
0
1
0
0
1
1
Is there an easy way to convert it into a run length list that looks like this instead:
3 1
1 0
1 1
2 0
2 1
(Three 1's, ...

**0**

votes

**1**answer

39 views

### Finding Common and uncommon values in excel 2010 [closed]

I have some values/numbers from A2 thru F8, I need the common values in 1 column (i.e the values which are common in A,B,C,D,E & F)-THIS CAN BE IN COLUMN H
I also need values uncommon only in A, ...

**2**

votes

**1**answer

34 views

### Excel formula sum multiple rows/column once on cell has reached it's max number and continue to sum

I am trying to come up with a formula that will split three rows of numbers evenly until one cell reaches a max number and then continue to sum the same three rows but split all the new values in two ...

**0**

votes

**1**answer

18 views

### I want to take two columns of cells (not the whole columns) and copy them and sort them into different cells

My Table:
I want to copy the names and scores from the desirability score area into the "Most to least desired rooms area but place them in descending order. How do I do this? Edit: I want the ...

**0**

votes

**2**answers

32 views

### How can I return the second last text value from a row?

Could anyone tell me the Excel formula to return the second last text value in a row?
I managed to find this formula for last text value: =INDEX(U2:Y2,MATCH(REPT("z",255),U2:Y2)), but not sure how I ...

**1**

vote

**1**answer

36 views

### Find the previous Row with a specific value at in column A

Given the above sample sheet, which has semantic 'header', 'subheader' and 'subtotal' rows, I am trying to determine a formula to locate the previous subheader row relative the current cell. For ...

**0**

votes

**1**answer

14 views

### Find missing values on spreadsheet 1 when compared to spreadsheet 2

I have the following scenario:
I have a list of ID's on a spreadsheet generated from a web app that has to be compared to our internal system. Any missing values from SP1 have to be alerted since ...

**0**

votes

**1**answer

30 views

### MS Excel UDF - Time series difference

I'd like some help with manipulating some data please. Given:
i) A range "oRng" of say "MaxR" rows and "MaxC" columns;
ii) and an integer i such that 1
I'd like to figure out the easiest way to ...

**2**

votes

**1**answer

39 views

### Excel, Setting a cap for values that meet a criteria on a column

I'm having trouble finding a way to limit the values of a column by setting a cap, but only for some values meeting a criteria.
An example of what I have:
>Criteria | Value
>--------- + ...

**1**

vote

**1**answer

19 views

### Excel in Cell Function VALUE for Formulas

I have an excel sheet which I need to run on Android. Only problem, I used VBA for the Evaluate Function and VBA does not work on Android Apps. I was searching for an alternative and came a round the ...

**1**

vote

**1**answer

34 views

### Need to add newline(s) and space(s) inside a cell in Excel

I have data in an Excel cell which is delimited by ,.
For example: abc,bde,fgh.
I used the formula =SUBSTITUTE(A2, ",", CHAR(10) & CHAR(13)) to change the commas to line breaks. Therefore, the ...

**0**

votes

**1**answer

53 views

### Excel countif formula that will subtract from another number if cell contains certain values

I am dealing with job applications for 8 different sites. People can apply for any combination of sites i.e 1 of them, 2 of them, all of them.
Some applicants have specifically said they don't want ...

**1**

vote

**1**answer

21 views

### Summing up values from a user-defined range

I have a spreadsheet from January 1 to present. and I needed to sum up the value parallel corresponding from the range which the user defines.
For example:
in Column A are the dates January 1 - ...