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

**0**

votes

**0**answers

28 views

### Go to first non-negative cell in a row and divide by another cell

I'm looking to have Excel calculate the financial payback period for me.
In words, I'm trying to count and return the first non-negative number in a row and divide that value by a corresponding cell.
...

**1**

vote

**1**answer

32 views

### How to perform a function based on surrounding cells in Excel?

My rows on my spreadsheet look like the following:
foo | cherry | 400
bar | lemon | 300
foo | cherry | 500
bar | cherry | 70
I want another row to make calculations in column C based on the ...

**1**

vote

**0**answers

16 views

### Excel Table function

I got a Excel file. There is a formula, {=TABLE(C7,C5)}. I cannot find any table name from ribbon Name Manager, so I think there is no table. Then what is this formula doing ?

**0**

votes

**0**answers

40 views

### Excel 2010 Compile Data from Multiple Worksheets into Summary Spreadsheet

I've compile a structural steel and architectural finishes material take off for an industrial building.
I simply tallied the quantity of each length of each shape on a sheet corresponding to each ...

**1**

vote

**1**answer

39 views

### Working with cell addresses in excel with VBA

I have a Excel sheet in which are multiple forms. The forms look all the same and they have exact the same cells between them. They are ordered in 4 x 3:
x x x x
x x x x
x x x x
x x x x
....
There ...

**-1**

votes

**0**answers

39 views

### How do I use VLOOKUP or other lookup function to return all results of criteria?

I have a spreadsheet like this:
**Column A:** **Column B:**
apple 1 apple 1/red
apple 1 apple 1/green
apple 1 apple 1/yellow
apple 2 apple 2/blue
...

**0**

votes

**1**answer

24 views

### excel text to specific cell

so i have this problem i want to get a specific cell from text i mean that
i have made a table 1,2,3... A,B,C,....
i made a vlookup to search for specific letter in number so if i press 1 in a A3 it ...

**-1**

votes

**1**answer

25 views

### Excel, Write a value based on a counter

What I want to achieve is very simple. Based on the value of an Excel cell (let's consider it X), I want to write a specific value in X subsequent cells.
For example, if A1 has the number 3, a ...

**0**

votes

**1**answer

12 views

### In OpenOffice Writer, how to populate a column of a table with a sequential increase in dates?

I am using OpenOffice Writer. In it I have a table for a time sheet. The first column is for the dates which begin from eg. 01/01/2014 and should end in eg. 20/07/2014. These dates are not already ...

**0**

votes

**1**answer

24 views

### how to create a YES NO notification for time?

I have a formula that shows hours and minutes of how long it took to notify a client. I need to create another cell to say YES if that notification is 4 hours or longer.
I currently have the formula ...

**1**

vote

**3**answers

47 views

### Excel date & time formula-round up cell value

I'm preparing a report of usage statistics for a group study room reservation system we use at the library where I work. I've exported all the reservations from MySQL in Excel for the past year and am ...

**1**

vote

**2**answers

38 views

### Excel to calculate summation with exclusion list

Say I have a table like below:
Task Time
a 1
b 2
c 4
d 8
--------
sum 15
When a task is finished, I would like to add strike line to the row and exclude it from summation. Adding ...

**0**

votes

**1**answer

24 views

### Calculating frequency of only visible cells

I'm having trouble calculating the frequency (in order to plot a histogram) from filtered data.
As for now, on Sheet1 I have the data of ten temperatures with a filter, I assigned a name for each ...

**0**

votes

**2**answers

44 views

### Create New Spreadsheet of data from many Excel workbooks

In retrospect, this is an issue of data-collection, but I want to see if there is an easy way to do it without doing each file.
I have about 350 separate Excel workbooks. Each workbook has a bit of ...

**0**

votes

**1**answer

34 views

### Excel Formula Assist - Find missing numbers

I have a spreadsheet exported from my database that contains Roles and Account IDs. There are 4-5 role names per account ID. I need a formula to display any accounts that do not have a role of RBD. ...

**0**

votes

**1**answer

24 views

### How can I use a formula or conditional formatting to format mutilple criteria?

I am trying to do an excel function, either an IF formula or use conditional formatting to do an AND formula.
I need it to produce all three results in one formula, it is:
if the date is before ...

**1**

vote

**1**answer

34 views

### Sumproduct, Sumif, Indirect and named ranges issue

I have this formula that works:
=SUMPRODUCT(SUMIF(INDIRECT(Financials&"!$B3:$B10000"),'Information PG1'!$K$2,INDIRECT(Financials&"!d3:d10000")))
where Financials is a named range. If I ...

**0**

votes

**2**answers

49 views

### Indexing a Date Range if Date is Greater Than and Within n Days of Another Date

In a previous post I asked a question about how to find the most recent date associated with a particular entity and have that value returned in Sheet2 so long as it was greater than the associated ...

**0**

votes

**1**answer

29 views

### Search from bottom with multiple criteria

I am trying to search a data table in excel and find the bottom most row that meets a specific criteria. I thought I could use this LOOKUP trick but it only returns a #DIV/0 error. This is what I ...

**4**

votes

**4**answers

194 views

### Showing whether a list in Excel has a 1 or a 0 in it

So I have a column in excel that I want to roll up into one row. Essentially the column has a 0 if a feature isn't present, and a 1 if it is.
What I want to do is get excel to check a column, lets ...

**-1**

votes

**3**answers

41 views

### Excel Formula to match dates

This may sound really simple but I need a formula that checks if one column's date is the same as another, and if so put 'true' or '1' or something. I tried =IF(H2=G2, "True", "False") but it does not ...

**0**

votes

**2**answers

29 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

58 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

**0**answers

33 views

### Excel Table Copy Wrong Formula with Newly Inserted Rows?

I am trying to create a simple spreadsheet to track costs. I have a total of five columns with different categories, two of which have formulas. I wanted to be able to insert a new row into the ...

**2**

votes

**1**answer

45 views

### Excel Help: How to scan a row and return the value of the first cell in that column

I have a matrix of numbers and the matrix has column header -- say like this:
a b c d e f
0 0 0 1 0 0
0 0 1 0 0 0
What I want to do is to search for each row for the element 1 and return its ...

**0**

votes

**2**answers

30 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

**2**answers

37 views

### Using IF to Place A Value in A Different Cell

Is it possible to do something like the following?
=IF(A1=0, Place "Yes" in B2, Do nothing)
I was considering using =OFFSET somehow, but I'm really not sure how I would implement it here.

**1**

vote

**1**answer

68 views

### Avoiding Long Nested IFs in Excel Function

I have a formula that relies on two different inputs: the first is an integer value between 0 and 5, and the second is the rounded sum of values in an array. I would like to have users input values ...

**1**

vote

**1**answer

30 views

### Excel Conditional Formatting Colour Scales highlight group of cells when another cell is a certain value

So I have a document that has 2 drop downs with the first acting as a currency selection and the other acting as a discount selection (0% - 50%). Basically whenever I select from the dropdowns the ...

**1**

vote

**1**answer

19 views

### Trying to sum a column of values between settable dates

So I have a list of costs over a period of time. Each row is a date, descriptor, quantity and value. So looking at the image, all the values in B, C and D are entered manually or through some other ...

**0**

votes

**1**answer

20 views

### What's wrong with this COUNTIFS() formula? [closed]

Will someone please help me with the following formula
=COUNTIFS('CURRENT LEAD LISTING'!$C$2:$C$378,"ACT",'CURRENT LEAD LISTING'!$E$2:$E$378,"X",'CURRENT LEAD ...

**0**

votes

**1**answer

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

**2**

votes

**1**answer

22 views

### Use IRR function with discontinuous cells in EXCEL

Is there a way to use a range of cells that are not next to each other with the IRR function?
I'd like to use IRR this way:
=IRR(A1:A3 AND B2) meaning read A1, A2, A3, B2 the same way you would ...

**1**

vote

**3**answers

30 views

### Using OR condition in SUMPRODUCT

I have three columns of data - a sum column (A) and then the two columns that form the sum (B and C). Sometimes, just one of the B and C columns is filled in; occasionally the B and C columns are both ...

**1**

vote

**2**answers

36 views

### add same value to range of cells in microsoft excel

I want to add the same word from cell B1 to B40000
I do not want to use the click and drag box method to apply it across multiple cells/rows.
I know I have to type in the formula bar B1:B40000 but I ...

**0**

votes

**1**answer

23 views

### Excel - sumifs and indirect

I am trying to obtain data from multiple worksheets:
Where:
sheet 1 - has an F2 cell that enables you to choose from a drop down list of names and each of these names equals a different worksheet in ...

**0**

votes

**2**answers

122 views

### Calculating time difference between two date/times from two columns in Excel

I have 5 columns: Start Date, Start Time, End Date, End Time and Duration. I need to calculate the duration between the Start Date/Start Time and the End Date/End Time. I tried just subtracting the ...

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

**0**answers

41 views

### Microsoft Excel 2007 - Auto Update in 1st Sheet based on What updated in 2nd sheet (2nd sheet Update will be in Fixed Column but Random Row)

Actually I'm facing a problem now.
I am using Excel 2007.
And, I have two different sheet of excel file with me.
My 1st sheet is my report.
My 2nd sheet is my customer name.
I hope that when ever ...

**0**

votes

**3**answers

56 views

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

Total n00b here.
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 ...

**1**

vote

**2**answers

61 views

### Filter OpenOffice Calc rows by matching if a cell in one column exists in another column with a certain value

Have a question I cant seem to figure out using the filter.
Column A has 1400 rows with 800 additional rows that are duplicates (so 2200 total rows).
Column B has inventory amount for only the last ...

**2**

votes

**4**answers

88 views

### In Excel, how to count enormous number of timestamps, and arrange counts in 5 minute periods sorted by day of week?

I've got just over 300,000 rows in a spreadsheet, and each of these rows contain a timestamp (HH:MM:SS AM) and a date (DD/MM/YYYY).
I'd like to get counts for these timestamps and group them in 5 ...

**1**

vote

**1**answer

28 views

### VLOOKUP not interpolating all cells?

My VLOOKUP formula is not interpolating all cells
AD AE AF
11-6-2014 15:20 90,4 #N/A
11-6-2014 15:21 89,1 #N/A
11-6-2014 15:22 90,4 Good
11-6-2014 15:23 89,1 Good
AF column is my ...

**1**

vote

**1**answer

20 views

### How to remove one column of a calculated item in Pivot?

I have two columns. One with data from this year and the other with data from previous year. I now have added a calculated item in Pivot to display the % delta of these two years. Now when doing this ...

**0**

votes

**1**answer

29 views

### In Excel I need to find data on sheet3 from data on sheet1

I have an Excel workbook with several sheets. The column 'SheetA'!W:W lists text that can be found within a column header on row 'Sheet3'!1:1. For example, SheetA'!W42 contains "B16". Cell ...

**0**

votes

**2**answers

48 views

### How do I find the next smallest/largest value without sorting?

I have a table from which I need to pull the next largest and/or smallest value, relative to a value in a separate table. However, I also need to allow the first table to be sortable by any criteria, ...

**1**

vote

**1**answer

46 views

### A formula to check if the date is older or equal to 60 days and another columns cell is equal to a value

I have tried using conditional formatting and managed to get the first part of the formula to work.
=D2:D999<=TODAY()-60
I want to then also check if the value of what is in column G is equal to ...

**0**

votes

**1**answer

18 views

### Last occurence in a “right vlookup”

I don't know if the question must be put here or on SO. Sorry if i make a mistake.
I have to search the first and last occurrence of a certain value within a column(array).
I have managed to make use ...

**1**

vote

**2**answers

17 views

### Rolling up the status of an excel column to a single cell

I am an intermediate excel user at best. I've looked everywhere back can't seem to be able to find this specific formula how-to.
What I need to do is in a status worksheet I have a series of tasks ...

**0**

votes

**1**answer

29 views

### excel spreadsheet average with prioritization

I need help how to write excel formula (or any excel solution)
to sum with priorities.
I have transaction data like this:
Date Unit Price Buy/Sell
7/11 3 10 Buy
7/12 2 9 ...