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

**0**

votes

**1**answer

48 views

### How to populate information from one sheet to another in Excel Workbook?

My current task is to create a formula where my information from one sheet will auto populate in another sheet when the product description is copied.
To be specific, we need to have the items ...

**1**

vote

**1**answer

19 views

### MDETERM gives Err:502 on non-contiguous range of cells

I have the following setup:
A 3x3 matrix in the cells A1:C3 and a 3x1 vector in D1:D3.
I would like to calculate the determinant of a new matrix formed by the 1st column of the original matrix, the ...

**0**

votes

**2**answers

25 views

### Applying Filter And Finding The Last Cell - Excel

Sub test_sum()
last_row_in_Process_sheet = Application.Workbooks(file_name).Worksheets("ms").Range("a65536").End(xlUp).Row
atmCurrentSum = ...

**2**

votes

**1**answer

45 views

### How do I update a cell (automatically) based on a Random Result in another cell in Excel 2003?

Scenario
Column J displays data from column C based on random selection from 2 other columns of numbers, i.e.:
J1: =INDIRECT("C"&INDIRECT("F"&N1))
J2: ...

**0**

votes

**2**answers

62 views

### referencing variable cells within the AVERAGE function

I am trying to create an automated system to average data, but the starting row and ending row of that data varies from one time to the next. I currently use the LOOKUP function to locate a reference ...

**0**

votes

**2**answers

79 views

### Enter different text in multiple cells in a row based on one cell's value

Totally new to this but need some help. Any advice appreciated. I was wondering if it is possible to write some kind of formula that would automatically fill about 12 cells in a single row based on ...

**0**

votes

**0**answers

10 views

### Linking data to chart using vba

I am trying to connect this data to my chart but i am getting application defined or object defined error
Dim chtObj As ChartObject
For Each chtObj In ActiveSheet.ChartObjects
chtObj.Delete
Next
...

**0**

votes

**2**answers

34 views

### Deleting charts in excel using vba

How to delete all charts in Excel Workbooks using VBA.
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.Parent.Delete

**1**

vote

**1**answer

17 views

### Create a number formatting that changed based on another cell

I would like to be able to change the number formatting of multiple cells based another cell. The goal would be to switch from absolute to percentage values and vice versa. By using the conditional ...

**0**

votes

**1**answer

29 views

### Excel text with values

Is it possible in Excel to have a cell show text that is different from its value?
I would like to have a a cell that has a value of $55.00, but only show the text "brief physical" in the cell.
...

**0**

votes

**1**answer

51 views

### Create function by check box object name (“Dynamically”)

I have multiple check boxes in a sheet, and in another sheet I have a form. When a check box is selected, then the appropriate content will be added as a column in the form sheet.
I have over 500 ...

**1**

vote

**1**answer

12 views

### Excel: Separate Currency Values from Rest of Line

I have the following conundrum, which I think will be helpful for a lot of people. I have been pulling down data from bank statements (yes, I could use Mint; no, I don't want to for various reasons), ...

**0**

votes

**0**answers

15 views

### Conditional formatting from a clock on Excel 2010

I was wondering if anyone can help me with my spreadsheet.
I have set up a macro in cell L1 to run a clock the programming is as follows:
Dim running As Boolean
Sub clock()
running = Not ...

**-1**

votes

**1**answer

24 views

### Sum Values from a List

I have a list of people and a corresponding work week which gives a value. When I pick a name I want to get a total for that person only.
Kevin - Week1 Total = 17 Week2 Week5
...

**1**

vote

**1**answer

26 views

### Change output of cell in Excel

I am an Excel and Xpath noob (well, at least I am honest!). I am working on a project that, at present, I have got working through learning from online tutorials. However, the next step has me ...

**0**

votes

**1**answer

25 views

### Lost. Work sheet cross reference

I am studying for a practical Excel test for a job promotion, and this topic has me lost. here goes.
I created a work book with an alphabetical of employees (test one).
I sorted and got all the data ...

**0**

votes

**1**answer

62 views

### How to get a cell to display no value if another cell is empty?

I want to do the following: The answer for D13 must be D12-D11. However, if I don't put a value in D11, D13 must display nothing, even if there is a value in D12.
Is this possible?

**0**

votes

**2**answers

14 views

### Choose a certain cell in an array

I have this spreadsheet named teams. It has four columns of soccer teams, spelled in various ways, and with varying way of abbreviations.
I got another spreadsheet were I going out use the name of ...

**1**

vote

**2**answers

31 views

### Filtering by matching criteria from multiple columns to return text from a third column

I am trying to create a formula that will automatically tell me which crew is working on a particular day and shift. I have tried several things from many different forums but somehow I am not doing ...

**0**

votes

**1**answer

38 views

### Using Min/Max function in excel how do I return a different value from the same row?

I've created a table (not using the table tool just using the cells) in Excel in one sheet.
In another sheet I have called the min/max function for that table to return the highest/lowest value.
...

**0**

votes

**1**answer

40 views

### How to set criteria range from date in multiple cells?

I have something like this:
I need to sum up all values for user, if date is is some range.
If there is no need to check dates, then I can use this formula in excel:
...

**0**

votes

**1**answer

47 views

### Excel export batch of columns from one worksheet

I am a very new user to VBA and am trying to create a macro to do a batch export of selected columns to a csv and then increment and loop that selection to continue exporting csv until end of dataset. ...

**-1**

votes

**1**answer

18 views

### Why is Excel able to complete resource-intensive formulas only when restarted through Task Manager?

This morning I was fumbling around with various iterations of IF(COUNTIF()) in one column for 16,000 rows. I've experienced how slow the COUNTIF() function can be, so I wasn't surprised to see it ...

**0**

votes

**2**answers

85 views

### Excel 2010 How to compare two sets of numbers that don't match but are related and return true/false result?

I am trying to compare two sets of numbers but they have different formats (the length of one number is different in each list) but parts of them match. I want to find the matches and return a YES ...

**0**

votes

**1**answer

21 views

### Using a Date to refer to a page to VLOOKUP into

I currently have data that has a date and item number and I'm looking to pull the price from a different sheet.
=Vlookup(#,Jan1!(Range),2,0)
Gives me my price of Jan 1
=Vlookup(#,Jan3!(Range),2,0)
...

**1**

vote

**0**answers

38 views

### Double Column Validation in Excel

I have a data table like this in excel:
ColA ColB
A 1
A 2
B 2
B 3
C 4
In a different sheet, I am trying to create two column for which the values will be drop ...

**0**

votes

**1**answer

14 views

### formula data doesn't show zero

I am entering excel sheets that need to reference bar codes like
014497020712
I know by using apostrophe I can get the zero to show
but I also need to list check digit separate (2) and the bar code ...

**0**

votes

**1**answer

65 views

### Excel will not sort all columns on my worksheet

I have a very basic worksheet with multiple columns and rows. I usually will sort one column to sort a-z and normally all data for those rows would follow the sort. Suddenly I am finding that if I ...

**1**

vote

**1**answer

26 views

### Count unique values of “X” where all instances of “X” are associated with the same assigned value “Y”

Say I have a table like the following:
X Y
1 TRUE
1 TRUE
1 FALSE
1 TRUE
2 FALSE
2 FALSE
2 FALSE
2 FALSE
3 TRUE
3 TRUE
3 TRUE
3 FALSE
1 is associated with both TRUE and FALSE.
2 is associated ...

**3**

votes

**2**answers

88 views

### Perform Sum over Conditional Products

I do have a list like this
╔═════╦═══╗
║ 1.5 ║ A ║
║ 2.5 ║ B ║
║ 2.5 ║ ║
║ 5.2 ║ A ║
║ 6.4 ║ C ║
╚═════╩═══╝
and want as result:
╔═══╦═════╗
║ A ║ 6.7 ║
║ B ║ 2.5 ║
║ C ║ 6.4 ║
╚═══╩═════╝
So ...

**0**

votes

**0**answers

19 views

### nper() function with daily compounding and monthly cash flows

nper(rate, pmt, pv, [fv], [type]) of course calculates the number of cash flows. The rate entered is the annual rate divided by the number of cash flows per year.
Assuming the cash flows are monthly, ...

**0**

votes

**1**answer

33 views

### How to return rows with top five values in a certain column

I have some data like:
A | B
a 12
b 12
c 0
d 5
How can I get the top 3 maximum values of column B from this list?
The formula
=INDEX(A2:A6;Match(MAX(B2:B6);B2:B6;1))
gives me the ...

**1**

vote

**0**answers

19 views

### Excel search function search term from other sheet

I have an excel sheet with a function which uses the SEARCH function in the following way:
=IF(OR(IFERROR(SEARCH("TERM1";MYCELL;1);0) > 0;IFERROR(SEARCH("TERM2";MYCELL;1);0) > ...

**1**

vote

**2**answers

33 views

### how to use NOW or TODAY, but freeze the cell with its value

if i use NOW or TODAY in a cell, it updates every time you press F9. What I want is a function that returns today's date but that is not dynamic, meaning that it freezes the date as of today. i cant ...

**3**

votes

**1**answer

34 views

### Correctly using the Excel match function to find a partial match

I am desperately trying to make my excel do what I want. I want to find a string which is written in column B1 in my Column A1 to A5.
My string in column B2 shall be searched in A1:A5 as well.
...

**0**

votes

**2**answers

37 views

### How to make a formula in one cell only run when another cell has a content

Hi I am using 2013 excel and have a formula in W269:
=product(u269,v269)+50
However I need this formula to run only when T269 contains a value (text not a number).
Here's some more details in case ...

**0**

votes

**1**answer

16 views

### Populating part of a formula with the contents of another cell

I have a simple formula in cell B1 it looks like:
=Robert!B$63
In column A I have a list of names. I wonder if it's possible to write a formula that can take the name from a cell in Column A and ...

**2**

votes

**1**answer

42 views

### Trying to create a formula to calculate a rate value per row in Excel

I think this is to do with a formula over an array but I can’t get it to work.
I’m looking for the easiest method to do the following.
Say I have this setup, columns are 'jobs' and rows are ...

**0**

votes

**0**answers

34 views

### Conditional formatting to highlight differences between 2 pivot tables in Excel

I have an Excel issue I hope somebody may be able to help me with please?
I have 2 pivot tables that both contain the following fields:
'Provider ID' in the Rows area,
'Month' in the Columns area ...

**0**

votes

**1**answer

26 views

### Attendace calculation from when the person started

I have a list of young people, I want to calculate their individual attendance to the programme, from the date they joined. NOTE not everyone joined at the same time.
[
I can just sum from the date ...

**0**

votes

**1**answer

27 views

### Need some help showing the difference between two dates

So I am trying to write a function that shows the difference between two dates.
I'm currently using the following formula:
=DATEDIF(D2,E2,"y")&" Years, "&DATEDIF(D2,E2,"ym")&" Months, ...

**1**

vote

**1**answer

36 views

### Excel condition comparing two cells then fetching data

I'm relearning Microsoft Excel after moving to Numbers a couple years back. I have an idea in mind that I would like to put in practice, but having a rather hard time trying to do. I'm not sure if ...

**0**

votes

**1**answer

35 views

### Insert an image into a excel comment [duplicate]

I follow the steps from How do I load external images into Excel?
but when inserting the function appears an error:
Any idea what is causing this error?

**0**

votes

**0**answers

22 views

### Can you use unit of measurement combinations with Excel's CONVERT function, and if so, how?

I've been wanting to experiment more with Excel's CONVERT function and came across a useful web page that listed the proper syntax required for it. The url to the page is below:
...

**1**

vote

**2**answers

40 views

### Auto (drag)incrementing only one cell value in Excel

For example, I have one table with values:
14.10%
1.60%
2.80%
and I have a cell with value I want to multiply each table row by, eg L15=300
I want to multiply every value by 300, so I put
...

**1**

vote

**1**answer

19 views

### How do I add text in a cell based on information from a separate cell in Microsoft Excel?

Column A is a list of city names, and I want to add a column B for a list of the counties that correspond to the cities. Example: Charlotte Hall, Piney Point, and Lexington Park are all located in ...

**0**

votes

**0**answers

28 views

### What is the most efficient way to standardize time formats and find time durations exceeding specific lengths using Excel 2007?

THE SCENARIO: Let’s say somebody hands you a spreadsheet containing a column for the time duration of 416,000 videos. When the information was entered, it was entered into an Excel 2007 spreadsheet ...

**1**

vote

**0**answers

25 views

### Calculating averages for every n rows with missing data

I have a column with 30-min averages, and I'd link to make a new column with 24-hour averages. I found this formula very useful:
...

**0**

votes

**0**answers

34 views

### Formula to find difference/change between 3 or more fields?

Probably a dumb question, but I'm looking to find the difference between 3 values in excel.
+-----------+-----------+-----------+
| Nov. 2015 | Dec. 2015 | Jan. 2016 |
...

**0**

votes

**2**answers

27 views

### Excel, possible array? Not sure how to calculate conditional steps

I have two rows of numbers, 1 and 2, they go from G-Z. I want to calculate the following:
(G2-G1, if greater than zero) add to...
(H2-H1, if greater than zero) add to...
(I2-I1, if greater than zero) ...