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

learn more… | top users | synonyms (5)

0
votes
1answer
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
1answer
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
2answers
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
1answer
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
2answers
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
2answers
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
0answers
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
2answers
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
1answer
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
1answer
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
1answer
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
1answer
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
0answers
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
1answer
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
1answer
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
1answer
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
1answer
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
2answers
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
2answers
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
1answer
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
1answer
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
1answer
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
1answer
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
2answers
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
1answer
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
0answers
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
1answer
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
1answer
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
1answer
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
2answers
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
0answers
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
1answer
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
0answers
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
2answers
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
1answer
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
2answers
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
1answer
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
1answer
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
0answers
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
1answer
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
1answer
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
1answer
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
1answer
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
0answers
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
2answers
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
1answer
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
0answers
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
0answers
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
0answers
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
2answers
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) ...