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

learn more… | top users | synonyms (3)

0
votes
1answer
5 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
1answer
18 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
3answers
19 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
2answers
21 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
0answers
8 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
2answers
25 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
0answers
23 views

Matching a Date Conditional on its Distance to Another Date

I have a set of names and datetimes on two sheets. One sheet we'll call LargerSheet and the other we'll call Sheet1. On LargerSheet each name has multiple datetimes associated with it. On Sheet1 ...
0
votes
1answer
26 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
1answer
18 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
1answer
13 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
2answers
42 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 ...
-2
votes
0answers
13 views

If inventory formula needed [on hold]

Good morning, I am trying to create a spreadsheet to keep count of inventory. I need a formula that subtracts by size from the original inventory count. For example, If a cell = youth small, i need ...
0
votes
1answer
25 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 ...
3
votes
4answers
185 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
3answers
38 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
2answers
25 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
2answers
46 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
0answers
19 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
1answer
38 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
2answers
29 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
2answers
33 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
1answer
55 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
1answer
24 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
1answer
16 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
1answer
19 views

What's wrong with this COUNTIFS() formula? [on hold]

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
1answer
18 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
1answer
13 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
votes
0answers
23 views

2 Excel Tabs: Find matching values in column As and match the B columns based on A. (Only one tab has B filled)

I have two tabs in excel; one working with technologies and one with patents on those technologies. The technology tab has each tech ID in column A, and the patent tab has the same tech ID numbers but ...
1
vote
3answers
26 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
2answers
32 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
1answer
14 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
2answers
71 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 ...
-1
votes
0answers
15 views

Excel - Timesheet query - find first booking in and last booking out in a day

I have a spreadsheet of about 5000 rows as follow COLUMN A: EMPLOYEE NAME - Jo Bloggs, Sheryl Smith, etc COLUMN B: DATE/TIME COLUMN C: EVENT (IE "BOOK IN" OR "BOOK OUT") I am trying to find the ...
0
votes
1answer
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
0answers
33 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
3answers
42 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
2answers
34 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
4answers
64 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
1answer
22 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
1answer
15 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
1answer
23 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
2answers
40 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
1answer
33 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
1answer
16 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
2answers
16 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
1answer
27 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 ...
0
votes
2answers
30 views

Using Index Match Conditionally to Indicate Whether

I feel pretty comfortable with INDEX MATCH, but I came across a problem I'd like to use it on where I'm not quite sure it works. I have two sheets: the first smaller sheet has a small subset of ...
1
vote
2answers
19 views

Single formula to search column and return corresponding fields

Am having issues creating a single formula to search the billionaires list by name and autofill the rank, age, net worth, and citizenship field in the search spreadsheet. This is the formula I have ...
1
vote
0answers
17 views

Looking up the same value in a row and returning multipe corresponding values

I am trying to lookup value 1 in the example below and return corresponding column name. When I use index match, it only looks up the first column which corresponds to 1 in the below example, it looks ...
0
votes
2answers
31 views

Excel - How to compare columns for equal values

I have an Excel sheet which has two columns. Both columns have numbers. The first column contains more numbers than the second column. I want to find which all numbers in the second column are there ...