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

learn more… | top users | synonyms (5)

0
votes
2answers
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
2answers
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
1answer
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
1answer
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
1answer
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
1answer
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
2answers
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
2answers
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
1answer
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
2answers
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
1answer
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
2answers
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
2answers
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
1answer
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
2answers
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
2answers
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
2answers
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
2answers
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
1answer
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
1answer
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
2answers
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
1answer
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
1answer
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
1answer
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
1answer
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
3answers
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
3answers
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
2answers
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
2answers
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
1answer
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
1answer
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
1answer
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
1answer
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
3answers
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
2answers
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
2answers
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
3answers
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
1answer
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
3answers
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
1answer
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
1answer
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
2answers
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
2answers
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
1answer
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 ...