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

**0**

votes

**2**answers

77 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

140 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

248 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

53 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

92 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

111 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

76 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

76 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

458 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

522 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

980 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

9k views

### Excel-Overcoming #VALUE error with FIND function

In cell A1 I have entered "Apple". In B2 I enter the formula =FIND("Apple",A:A). However I keep getting #VALUE error. Can anyone explain this and how do I overcome it.

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

118 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

309 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

5k 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

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

**0**

votes

**1**answer

171 views

### Move every first line of a pair of cells in a column to the same row to the left of the second line

How to move every first line (digits) of a pair of cells in a column to the same row to the left of the second line (names)? The pairs of cells are at a random distance in this column.
0117-000-00
...

**0**

votes

**1**answer

92 views

### Using the contents of a cell to refer to a worksheet name in a formula

I have the names of dealers along with the associated data located in various tabs in a particular workbook. I wrote a sumif formula for one and included the names of the dealers in the cell to the ...

**0**

votes

**1**answer

103 views

### Sum of rows conditional on two conditions [duplicate]

I'd like to use array function to get conditional sum based on two columns. Given the example, for each ID I want to calculate the sum of value column if column B is -1
ID | B | value |
...

**0**

votes

**3**answers

2k views

### Excel sheet becomes very slow

I am working on an Excel 2007 workbook (less than 1gb in size). There is one sheet which has formulae linked to other two sheets in the same workbook. The problem is that it becomes really slow ...

**0**

votes

**1**answer

102 views

### how do i get a simple IF function to work in Excel only when another cell has a value in it?

How do I get a simple IF function to work in Excel only when another cell has a value in it?
I've tried =IF(ISBLANK(G4)="Sold","Closed","Open") but that didn't work so I was wondering if anyone could ...

**0**

votes

**1**answer

82 views

### Perform calculations on a cell-by-cell basis

Is it possible to calculate on a cell by cell basis?
I am trying to add up the values in a range of columns E10:E610 that are between 11,538 and 34,760, and then calculate 5.6 percent on them. ...

**0**

votes

**1**answer

558 views

### Need to use math formula to determine cell reference

I want to be able to use this formula but have the range changed based on the value in another cell.
SUMPRODUCT(1/COUNTIF(B5:B15,B5:B15)
For example, if the macro is written as:
...

**0**

votes

**1**answer

225 views

### Issue with SUMPRODUCT Function

I am Having a issue with the SUMPRODUCT Function in Excel 2007. Here is my string and issue:
=SUMPRODUCT(($B$2:$B$800>DATE(2013,4,1))*($B$2:$B$800< DATE(2013,4,5)))
A B
1 4/1/2013
2 ...

**0**

votes

**1**answer

39 views

### Focusing on particular information within a cell when doing a calculation

When performing a calculation using two cells in Excel, is there a way to focus on specific information within a cell, when entering the formula?
For example, I have two time cells (start time and ...

**0**

votes

**1**answer

13k views

### If cell B1=1 then DELETE cell A1 (excel 2003 formula) - How to do it?

I work with Excel 2003.
If cell B1=1 then DELETE cell A1,
and if cell B=0 then UNCHANGED.
How can I do this?
Example:
http://oi33.tinypic.com/mwsm03.jpg
Many thanks :)

**0**

votes

**3**answers

714 views

### How to update cell references when setting the Formula property in VBA?

Here is a snippet of my code:
For i = 1 To k
If Range("D2").Offset(i, 0).Interior.ColorIndex > 0 Then
Else
Range("D2").Offset(i, 0).Formula = Range("D2").Formula
The ...

**0**

votes

**2**answers

118 views

### Modify formula to work with conditional values

I am currently using the below formula to calculate enablement costs for meters
=SUM(SUMIF($D74,{"New Business"}),$U74*5500)
This worked fine as the fee of $5500 was standard, however I now need to ...

**0**

votes

**2**answers

3k views

### Excel formula to search cell values from one column and add values from another cell if true

I don't think this should be too complicated for Excel, but I am trying to figure out how to:
1) Determine if a cell in column A contains the text "FF"
2) If so, jump over to the corresponding cell in ...

**0**

votes

**1**answer

173 views

### Complex excel formula help needed?

I have a complex formula i need to create to look up various fields, with percentages of totals to be given if certain criteria are met, and need some guidance.
In column P i have a monetary figure.
...

**0**

votes

**1**answer

2k views

### Excel formula sorting data by date but group by column value

Morning people
I've been roped into helping someone with an excel sorting issue and I've got myself a bit stuck to be honest. I need to sort a spreadsheet by date (oldest to the newest) but group ...

**0**

votes

**1**answer

399 views

### Spreadsheet fill down for a formula

I want to add a formula to multiple cells in a column without typing them all out. now don't just tell me about the fill option as that does not do what I want (as far as I can tell).
When I used ...

**0**

votes

**1**answer

1k views

### LibreOffice/Excel: SUM an adjacent cell if the value in the conditional cell is equal to N (SUMIF(?))

Disclaimer: I have no idea what I'm doing.
In LibreOffice Calc (3.6.2.2), I'm trying to SUM all of the values in G:x (where range is G2:G529), where B:x is equal to a given number N.
The data set ...

**0**

votes

**3**answers

489 views

### Excel - Sum First Value Of Non-Unique Adjacent Column

I have a table:
ColA | ColB
1 1
2 1
2 1
3 1
4 1
5 1
I would like to sum ColB only once where ColA = 2.
Essentially, I would like to sum the values ...

**0**

votes

**2**answers

57k views

### How do I calculate percentage change in an Excel formula?

I currently have 2 cells in Excel and I'm trying to find a formula to display the percentage change between them.
For example:
If B2=100 and B3=102, the result should be: +2%
If B2=103 and B3=100, ...

**0**

votes

**2**answers

767 views

### Excel. Double the number in cell A7, IF all cells from A1 through A6 contain a value higher than 0

Excel. Double the number in cell A7, IF all cells from A1 through A6 contain a value higher than 0.
I am trying to make an excel timesheet where Employees who work on Sunday get paid at double their ...

**0**

votes

**3**answers

6k views

### Excel: Using ADDRESS to create Array Formula

I'm trying to replace the hardcoded references E13:E15 with relative references:
=+SUM(LN(INDIRECT("E13:E15")))
I've tried variations of below, in cell e16, but nothing seems to work:
...

**0**

votes

**1**answer

14k views

### Count how many times a text appears in Excel 2010

How do I count how many times each text appears in a column?
For example I have this column:
foo1
foo1
foo1
too2
foo1
too2
mmm
mmm
foo1
Now I like to excute a function that shows me this result:
...

**0**

votes

**3**answers

281 views

### Microsoft excel formula on rounding up values

If a value is less than 200, then it should be rounded up to 200.
How do you do this in Microsoft Excel?

**0**

votes

**1**answer

1k views

### Compare a column from two different sheets and return true/false

I need to compare a column from two different Excel sheets and return a value of true or false.

**0**

votes

**1**answer

371 views

### HLOOKUP Return Greatest Value

I have some columns A, B, C, D, E, F, G with some values in the rows below 2, 3, 4. I don't know if HLOOKUP is the correct answer for what I am trying to do but I want to have a function that looks at ...

**0**

votes

**2**answers

1k views

### Positive result, negative result and current balance. How do you make starting balance show current result?

I have 3 columns:
Column A shows positive result and if the result is negative then it is in a column B.
Column B shows negative result and if the result is positive then it is in a column A.
...

**0**

votes

**2**answers

5k views

### Excel COUNTIF Array (Multiple Criteria)

I'm using this function:
=COUNTIF((B:B,"Superior")*(A:A,"Employee Effectiveness:"))= 2
The idea is to try and find how many times the value "Superior" is used adjacent to the cell with value ...

**0**

votes

**1**answer

61 views

### How to edit a formula that I already have to take into account another cell's data

I have an Excel sheet that keeps track of bonus for our paralegals. Because we need the clients to be listed in one sheet all the paralegals are also in one sheet instead of each having their own. I ...