Tagged Questions

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

learn more… | top users | synonyms (5)

0
votes
2answers
72 views

How to sort columns in Excel so that only one value appears in each row?

Say I have a a spreadsheet with the following data: A B C D --------------------- 2 1 0 2 3 2 1 4 5 3 3 6 7 5 6 8 11 8 10 10 ...
1
vote
1answer
28 views

excel grid to columns

I have data in an array/grid like this: Name | Group | A1 | A2 | A3 name1 | Group1 | 14.95 | 19.95 | 0.00 name2 | Group2 | 4.95 | 9.95 | 12.95 name3 | Group2 | 49.95 | 0.00 | 0.00 And I need a way ...
2
votes
1answer
78 views

Two types of formulas in one cell - Excel 2013

Thank you for your time. I am using Excel formulas for the first time ever and I am stuck (I am sure that I am not using the proper terminology to search and thus cannot find the help I need). It is ...
0
votes
1answer
30 views

Construction Quote Log

I'm reworking my company's log of the proposal's we send out. Trying to indicate the total dollars that we could conceivably be awarded. The challenge I'm having goes something like this: We are a ...
0
votes
0answers
34 views

Trouble with IRR formula in Excel 2008 on MacBook

I am getting an error message for a relatively simple calculation. I am trying to calculate the IRR over a 2.5 year period for a set of data in which -11,000,000 is the initial investment and zero ...
1
vote
1answer
21 views

Using column data from another worksheet

I have a worksheet with one column and that has a list of my company names and what I want to do is be able to create a stats worksheet where I can use those company names without changing both ...
0
votes
1answer
55 views

Copying VLOOKUP formula in multiple sheets, where sheet number automatically changes

I have created a progress card in Excel (there are 50 such sheets, one each for 50 students). There is one tabulation sheet also with the names of the 50 students. So, when numeric marks are entered ...
0
votes
0answers
57 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
1answer
44 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
0answers
30 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
0answers
116 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
1answer
64 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 ...
0
votes
1answer
95 views

Excel 2007 - find max value based on status criteria in data list

I need to find the largest value (aging days) in a set of data based on the item status. Much like a SUMIF function, but a MAXIF (if one existed). My data is not sorted by status. I basically want ...
0
votes
1answer
36 views

excel text to specific cell [closed]

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
1answer
29 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
1answer
21 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
34 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
137 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
67 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
1answer
44 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
104 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
1answer
45 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
32 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
109 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
65 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
1answer
42 views

How to sum every second column depending on the first column value

Team, This is driving me mad. I know how to sum every second column in a row: ie {=SUM(A1:G1*(MOD(COLUMN(A1:G1),2)=0))} What I need to know is how to sum all of the first columns unless there is a ...
0
votes
1answer
35 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
4answers
200 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
48 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
34 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
77 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
94 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 ...
0
votes
0answers
43 views

Reference last cell not only in a row or column, but in a range

all users, especially Jerry Beaucaire. I was reading this link when I stumbled upon his answer Here is the link How to refer to the last cell in a row in Excel? And Jerry Beaucaire answered it like ...
2
votes
1answer
69 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
32 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
40 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
85 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
62 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
30 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
29 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
1answer
27 views

In excel, add up a column so that each row in a new column shows the sum of the other column for all previous values

I want to add Column A so that it results in Column B, I've tried the Sum and SumIf function in Excel. Column A : Column B 0 0 0 0 0 0 4 4 8 12 ...
0
votes
1answer
49 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
35 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
3answers
46 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
43 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
39 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
670 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
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
58 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
105 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 ...