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

**0**

votes

**2**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**0**answers

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

**1**answer

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

**1**answer

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

**0**answers

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

**1**answer

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

**0**answers

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

**0**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**3**answers

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

**2**answers

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

**1**answer

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

**2**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**2**answers

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

**1**answer

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

**1**answer

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

**4**answers

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

**3**answers

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

**2**answers

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

**2**answers

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

**0**answers

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

**0**answers

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

**1**answer

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

**2**answers

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

**2**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**3**answers

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

**2**answers

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

**1**answer

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

**2**answers

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

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

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

**3**answers

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