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

**0**

votes

**1**answer

56 views

### Convert a range to a percentage in Excel

I have a range of figures I want to convert to percentage.
For example I have temperature readings taken every hour over a full day. The range is from 4.5deg to 15.5deg. I want 4.5deg to equal 0% and ...

**0**

votes

**2**answers

72 views

### How to calculate with Microsoft Excel the correct amount of minutes?

64 people receive services 105 minutes per day. How can i use Excel to calculate the total minutes of service per day and then multiply by 7, so i have the total amount of minutes per week, and then ...

**0**

votes

**1**answer

63 views

### Can't get VLOOKUP working with roster spreadsheet

I have an Excel spreadsheet set up as a roster. The way the roster is setup is for the ease of the person creating the rotation. This roster is not easy to read for the staff.
Mon ...

**0**

votes

**1**answer

57 views

### Excel Values Require Enter within Cell to recognize as number

I have a worksheet where I am getting direct output from STATA that has a weird character format that included " =[ number ]". I put in a new column to strip the characters and leave the numbers. ...

**0**

votes

**1**answer

107 views

### Make formula impossible to find / read

I have developed a spreadsheet for performing various calculations which our employees will complete and then distribute to clients / competitors. To protect the "intellectual property" of the ...

**0**

votes

**2**answers

61 views

### Converting comma seperated values in multiple rows

I am in urgent need of importing huge data from excel to SQL.
one of the excel sheet contains data as follows.
ID Name Category Section
1 Books Stationery KG1,KG2,1
2 Pencils ...

**0**

votes

**1**answer

33 views

### How to update range of columns in excel

I have an excel sheet where from one sheets's Id used as reference in next sheet.
The Id's are sequential. Like this :
Sheet 1 Sheet 2
ID Name ID Sheet_1_ID Text
1 ...

**0**

votes

**1**answer

48 views

### Is there a formula I can use to count the number of occurrances of a name in 1,2,3 order?

I have an excel spreadsheet containing a list of names and "scores" that they have received for a specific date. Each name should have 3 scores each month and I'm looking for a formula that can help ...

**0**

votes

**3**answers

1k views

### Excel formula to compare single value in one cell with multiple values in other cell

I have a value in Column A, which I want to compare with multiple values in column B, and depending on that value, put the answer in column C.
For example, using the table below, it searching in ...

**0**

votes

**3**answers

100 views

### Average Difference and Direction Between Values in Excel with Blanks

I have a sheet that looks something like this:
Sheet 1
1 2 3 4 5 6 7 8 9 10 11
1 6
2 3 5
3 ...

**0**

votes

**1**answer

83 views

### Generating Joint Density Function in Excel

Is it possible to have Excel calculate a joint density function based on two sets of descriptive statistics generated by the Data Analysis tool? To explain further, suppose I have two means a and b ...

**0**

votes

**1**answer

365 views

### Excel Formula: An Array formula containing VLOOKUP, SMALL, IF, OR

I am trying to create a formula that considers the data from three columns Y, H and V on my tab titled 'Data', returning the smallest value in A that meets that condition, once found Vlookup that ...

**0**

votes

**2**answers

48 views

### Getting current value and passing it to another function

A B
1 Date Data
2 1/1/2014 1
3 1/2/2014 2
4 3/5/2014 3
I wanted to sum up rows for certain months. With month I can get the month and sumif ...

**0**

votes

**2**answers

93 views

### Searching and Inserting Data Based on Date Range

In another post, a formula was given that was meant to help search and insert an entry if the date associated with the entry was less than the date on another sheet. The formula was as follows:
=IF(
...

**0**

votes

**2**answers

36 views

### Excel - Is there a function to return a number based on which string is in a cell, like CHOOSE but not?

I need a function =Func(A1, "Alex, "Betty", "Charlie") that returns the index of the matched entry. If this function is put into B1:B13, it would show
"Charlie" 3
"Alex" 1
"Alex" 1
"Alex" ...

**0**

votes

**1**answer

88 views

### Excel help to lock formulas from adjust on cell insert

I have a excel workbook where there is a hidden sheet that basically pulls data from other sheets in the same workbook just using the = formula.
The problem I am having is if I add a column or cells ...

**0**

votes

**1**answer

27 views

### Automatically Copy the only Credit rows into a Sheet

I am trying to use a formula by the below, With out use Developer Options.
I have a excel workbook and there is a two sheets
One is -
One sheet is (sheet1)-
TXN. ID Transection Type After ...

**0**

votes

**3**answers

367 views

### Excel filter duplicate values in column A and filter out rows which have column C blank

How to filter duplicate values in column A and filter out rows which have column C blank.
I have 2200 rows in Col A (the first 1400 rows also have data in Col B & C, the next 800 rows are ...

**0**

votes

**1**answer

97 views

### Excel Formula Challenge

I have a fairly specific Excel problem and I am hoping someone is savvy enough to come up with a working formula for me. I am using Excel 2011 for Mac btw.
So, I have a list of about 134 ISBNS, for ...

**0**

votes

**2**answers

45 views

### Showing name of row instead of excel cell name

I am having extremely long formulas over an extremely big sheet. At the moment I am tracking the formulas with the Formula Auditing Tool. However, my idea would be to just replace for example in a ...

**0**

votes

**1**answer

432 views

### Excel Help: Search Column A, Sheet 1 for same value in Column A, Sheet 2, return value of Column B, same row in Sheet 2 [closed]

I'm surprised I couldn't find the answer on Google, maybe I am being silly.
I want to perform this function:-
I have two sheets in Excel, lets say they are Sheet 1 and Sheet 2
In Column A, Sheet 1 ...

**0**

votes

**1**answer

548 views

### Excel - minifs function returning 0

I'm trying to write a function in Excel 2013 that returns the minimum of a set of cells that meets two criteria, but the function always returns zero.
On one worksheet, I have a table with several ...

**0**

votes

**2**answers

77 views

### Calculating time between 5a-1p or 530a-130p

I was wondering if it is possible for Excel to calculate time span if each time block is in 5a-1p format in one cell. That way I could add the hours for each week without changing too much.
Sample ...

**0**

votes

**1**answer

285 views

### Excel SUM From Different Sheets IF Date Found

I have a workbook with separate sheets for each product (about 20 sheets, adding more on a regular basis). Each product is only available for a certain time frame, and has daily sales data recorded on ...

**0**

votes

**3**answers

52 views

### Excel Extending Equations

So I have an excel table that is multiply 1 value against several other values. It looks like this:
So I want the equations inside cells C14 to F14 to be B14*C5, B14*C6, B14*C7, B14*C8 respectively. ...

**0**

votes

**1**answer

129 views

### SUMIF with multiple match criteria in Excel

I'm trying to create a formula so that if in cell E2 it says "Market", "Limit", or "Stop" it adds the numbers in cells L2 and M2.
=SUMIF(E2, "Market" "Limit" "Stop", L2+M2)
These expressions are ...

**0**

votes

**5**answers

1k views

### Excel COUNTIF Slow

I'm having an odd issue in excel that I haven't run into before. I have an excel sheet that I'm attempting to pull some specific records from, requiring that I know of duplicates. I am using a COUNTIF ...

**0**

votes

**1**answer

61 views

### Excel 2010 Countif Formula

I'm trying to make a spreadsheet to display stock orders. I am including both executed and unexecuted orders, but I'm trying to display how many orders did or did not execute using Countif. My orders ...

**0**

votes

**1**answer

75 views

### Matching grades to names in separate sheets

I'm having some trouble with an excel formula. What I have in sheet A is a list of student names in (Fname LName) format, and then I have the same list of students in a separate sheet along with exam ...

**0**

votes

**2**answers

79 views

### Creating a formula that queries data in Column B based on a selection from Column A in Microsoft Excel

I am reasonably familiar with Excel formulas but I need to construct a more complex query and am not having much success at the moment.
I have simplified what I am trying to achieve for the purpose ...

**0**

votes

**2**answers

143 views

### Excel if/then formula

I am wondering if there is an if/then formula for the following situation. I am trying to populate a monthly calendar. I want the calendar to show the value in A1 if the value in B1:B100 is equal to a ...

**0**

votes

**1**answer

267 views

### Defined Name to give text or date value instead of numeric value

I had been trying out Define Name in Excel. I defined a name for a cell in which I would put a text or date value or I may leave it blank.
But when I use this defined name in another cell, the second ...

**0**

votes

**1**answer

56 views

### Nesting the IF Function in Excel when there may be #Value! errors in the target cell

I'm trying to get some help to change a formula:
=IF(S4>8000,3,IF(S4>6000,2,IF(S4>4000,1,IF(S4<4000,0))))
So that it returns a value of 1.5 if the target cell contains an error value. ...

**0**

votes

**1**answer

99 views

### Can't compare a cell to a column in excel

I'm trying to compare a column to a cell in excel several times.
The columns are copy pasted from a PDF (only way to get the information). The results are bad, with trailing spaces, some completely ...

**0**

votes

**1**answer

114 views

### Use INDEX and MATCH function to find value and get a corresponding value from the returned rowindex

I want to combine columns [country code] and [admin1 code] with a "." in between and then lookup that value in Sheet1 in the [admin1code] column.
When a match is found I want to get the corresponding ...

**0**

votes

**2**answers

79 views

### Indirect turns up with #ref error

I want to use INDIRECT in a formula like =INDIRECT(CONCATENATE(A1,"+",A2),0);
where A1 = 7 and A2 = 8.
It comes out with #REF error all the time, but if I use the INDIRECT formula like this: ...

**0**

votes

**2**answers

77 views

### Define a range based on the result of the INDEX function

In Excel it is possible to write a function looking like that:
=SUM(A1:INDEX(...))
Where the end of the range of the data to sum is dynamically defined by the result of the INDEX function.
The ...

**0**

votes

**1**answer

502 views

### Generate ordered numeric list in Excel based on cell value

I'm not sure if this is possible, but I would like to create an ordered numeric list automatically based on a cell value. For example, if I have a cell with a value of 100, I want excel to ...

**0**

votes

**2**answers

61 views

### Excel 2007 - refering value from other worksheet in another function in excel

I have two worksheets. I have data in Sheet2 in column A for which i want to use average function. In sheet1 i have values in only two cells. B1 cell has value 5 and B2 cell has value 15. Using these ...

**0**

votes

**1**answer

542 views

### How to do a sliding window in an excel table?

I have a table with a date column and a value column like this
|A |B
1 |date |value
2 |2013-12-10 |1
3 |2013-12-10 |3
4 |2013-12-09 |2
5 |2013-12-04 |5
6 |2013-12-02 |2
Dates can ...

**0**

votes

**2**answers

1k views

### VLOOKUP with multiple criteria

I have a workbook with 2 sheets: spg and data
From the data sheet I wish to do a vlookup in the spg sheet, based on the value of multiple columns in spg.
I can do a =VLOOKUP(H5,spg!$D:$I,6,0) but ...

**0**

votes

**2**answers

53 views

### IF function only working if Value is TRUE and remitting a #NA if the Value is FALSE on Excel

Can anyone help me with the following formula? Basically it works when the condition is TRUE but gives me an error when the condition is FALSE. I would like the cell to remit the value "0" if the ...

**0**

votes

**1**answer

1k views

### Excel 2007 adding a percentage increase

Value = 20 is a constant number
Percentage increase to "Value" in levels of 2%, 4%, 5% up to 20%
I would like to enter in a cell the LEVEL of the object and it return the VALUE + the percentage ...

**0**

votes

**2**answers

2k views

### How to extrapolate or predict a project completion date

I have a few data points consisting of a percentage completed and a date. I would like to be able to predict the expected completion date based on the progress made so far.
If this cannot simply be ...

**0**

votes

**2**answers

123 views

### How do I determine if a value is between range of values specified in two columns and return another value from the row where condition is true?

I have a value that I enter in cell B5. I need a value returned from column C and the corresponding row where the value is between minimum values in A10:A12 and maximum values in B10:B12. In the ...

**0**

votes

**2**answers

317 views

### Multiple conditions in if statement on non-contiguous cells in Excel

I would like to perform an if statement with conditions on the values of multiple non-contiguous cells, something like
...

**0**

votes

**1**answer

1k views

### Excel summing named range

I have a workbook with many sheets in it. I want to sum column C2:C50 on each sheet. However, in the future I anticipate having to sum the range C2:C?, and rather than have to go into each sheet to ...

**0**

votes

**1**answer

1k views

### Subscripts inside Excel formulae

I have a formula in Excel that evaluates the l_x's (number of lives aged x) in mortality data:
=IF(J3>J2,"l_x increased",IF(J3/J2-1>=devianceThreshold,"Deviance too high",""))
Accross the ...

**0**

votes

**2**answers

4k views

### Excel - how to either have direct data input or cell value from formula

I would like to either populate a cell value from direct key input or have the cell value derived from a formula.
Example: either directly input a number into cell A4 or have the value for A4 = A2 + ...

**0**

votes

**1**answer

508 views

### Find and Replace VBA/Macro in Excel 2010

Another Excel VBA question. I'm using Excel 2010
I want to create a Macro that will remove the following 3 symbols in any cell for a selected range:
[ ] "
For example; If I select a range of ...