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

learn more… | top users | synonyms (3)

3
votes
2answers
60 views

Count first occurrence of one criterion based on a second criterion

I am trying to write an excel formula (or formulas) to count the first occurrence of a value in a given year for each Person in my table. I've searched for related questions on Superuser to no avail. ...
0
votes
2answers
67 views

How to add 4 hours in a format cells as yyyy-mm-dd hh:mm:ss and fit year,month and day

How to add 4 hours in a format cells as yyyy-mm-dd hh:mm:ss and fit year,month and day. Example below.
0
votes
1answer
48 views

Insert columns, within a range, based on cell reference

So am trying to tell VBA to insert a certain number of columns between two other columsn based on a cell reference. I've managed to get excel to insert columns within my range: Sub INSERT() ...
0
votes
1answer
43 views

compare and update spreadsheets

Please I need some help with excel spreadsheet here. I have two worksheets, one holding the old records and the other holding the current record. What I wish to do is to develop a vba code associated ...
1
vote
1answer
32 views

Excel 2010 - Calculate dates and return a 0 if it is the current date or date passed

I need to calculate days between today and the 'Last day to offer' and have Excel return a 0 if today is the last day to offer or if the date has passed; otherwise it should return the number of days ...
1
vote
1answer
110 views

Excel VBA code Private Sub Worksheet Change Power function not working

I'm making a worksheet where the wind speed defines the potential energy of the location. This value is the basis of a few sheets in the same Excel file. I'm trying to write a code that where I10 is ...
0
votes
2answers
21 views

Can I add data from a cell on sheet 2 to a cell on sheet 3?

I want to grab the total of the # hours from 6 cells in a row on sheet 2, and add them and the total of a cell on sheet 3, and display the result in another cell on sheet 3. What I have tried is: ...
0
votes
1answer
67 views

Getting average of data with multiple entries for a single date using Pivot table in Excel

I have tried this, but I am unsuccessful. I have the data dump in an excel sheet (Excel 2010). The first column has month. The second has the dates. The third column has numerical data. I have applied ...
0
votes
2answers
38 views

Define a range based on the result of the INDEX function

In Excel it is possible to write a function looking like that: =SUM(A1:INDEX(...)) Where the end of the range of the data to sum is dynamically defined by the result of the INDEX function. The ...
0
votes
1answer
47 views

Generate ordered numeric list in Excel based on cell value

I'm not sure if this is possible, but I would like to create an ordered numeric list automatically based on a cell value. For example, if I have a cell with a value of 100, I want excel to ...
0
votes
0answers
55 views

Finding IPs covered by Subnets in an Excel document

So I have a giant excel document that contains a list of IPs, but there are also entrys that have subnets. So for example, I might see "IP" "/16" and the first two octets would be the first two ...
1
vote
1answer
28 views

Return Earliest Date from a range

I have an excel spreadsheet where I want to pull the earliest date in the column based on the criteria in a different column (for a given tech) into a different sheet. Rows on sheet1 contain store ...
1
vote
2answers
74 views

Sum column until a criteria is matched in the adjacent column

I have a table looking like the following: Col1 | Col2 -----+----- A | 12 A | 4 B | 5 A | 16 A | 8 B | 91 C | 6 A | 55 -----+----- C | ??? What I want is if I type C (a ...
2
votes
0answers
162 views

Splitting an excel 2010 spreadsheet into multiple workbook files based on column value

I find and clean a lot of contact data, sorted by country, which I am currently using excel to manage. Mainly because it is easier to clean and manipulate the data I find this way, and I can easily ...
0
votes
1answer
64 views

How can I do this in spreadsheets?

Let's say you have a table like this: | A | B | C | ------------------------------------- 1| sex | age | answer q1 | ------------------------------------- 2| ...
0
votes
0answers
43 views

Turning students’ marks into grades with excel

I have a sheet contain grade boundaries, For example, the first row says ‘A* | 100 | 90’, the next row is ‘A | 90 | 80 ’, the next ‘B | 80 | 70’ and so on. Another sheet contains a list of student in ...
1
vote
1answer
69 views

Get the row and column for a cell in a named range?

Given a cell that exists in a named range, what's the cleanest way to get the row and column numbers for that cell inside the named range? In other words, I want the row and column number relative to ...
0
votes
2answers
61 views

How to split comma-separated values by formula, not by script

In A1 I have the following string. "831 days , 12673 total edits, 12286 approved, 5974 reviews" I want a formula (not script) that can take this string and return the following in B1:E1 B1=831 ...
1
vote
1answer
89 views

Conditional Formatting in excel based on another column

I have an excel worksheet and two columns. I highlight the first column if the value of the cells are less than 0. Now how can I highlight the second column cells based on the first columns that are ...
1
vote
1answer
84 views

Need a Excel script to identify email addresses that contain a different variation of the first name of the contact

I have an Excel spreadsheet with 50k+ rows of data, where we are trying to identify within the rows of data where the email address has no match of the first name, etc. We are basically trying to ...
0
votes
1answer
38 views

Excel - pulling data from certain sheet of different workbook

I am trying to pull pricing data from a larger workbook (masterprice.xlsx) for a new basic "price quote" workbook. The large workbook has a different sheet for each model name. In my new workbook, I ...
0
votes
0answers
34 views

Displaying some cells data depending on selected text from other sheet

I have an excel sheet which looks similar to below and with some more columns. Area Branch Empolyees Products Shelfs Area1 Area1 Branch1 10 Product 1 10 Area1 Area1 ...
1
vote
0answers
18 views

transposing a row with formulae & values both in several cells to another sheet in same file Excel [duplicate]

Consider the data arranged as follows in my Sheet1. I want the fifth row to be transposed in the Sheet2. When I reference the first item from B5 in Sheet2, and then drag the fill-handle(?) ...
0
votes
1answer
27 views

I want to merge cells to complete names in one row

I have copy pasted some name, desig and email data from a website, after pasting realized that the first middle and last name is in 3 rows. Desig and email ID is in corresponding column to that of ...
0
votes
3answers
60 views

Need help on using a few excel formulas together

I have some particular data in sheet1 and some other data in sheet2. I need to compare a specific value(Var1) from sheet1 and return a specific value from the sheet2, and return an output for the ...
2
votes
3answers
79 views

How to make excel return a date from a column corresponding to the last 'YES' in a different column

I'm trying to develop a spreadsheet tool to help track the training currencies of professional aircrew. In order to be considered 'current' in all training, aviators must accomplish a variety of ...
1
vote
1answer
34 views

What is the cleanest way to write an array formula to facilitate the addition of more rows?

I have a spreadsheet with several columns that use array formulas, specifically referencing other columns of data. I started off using the A$1:A$100 syntax (if 100 was the last row of data in the ...
0
votes
0answers
23 views

Mail Merge | Excel transpose or any other solution

i have an excel with 3 columns: domains, clients, email clients each client can have more than one domain. I want to mail merge all my clients to make a change in the dns. but i don't want to send 5 ...
0
votes
2answers
36 views

Excel 2007 - refering value from other worksheet in another function in excel

I have two worksheets. I have data in Sheet2 in column A for which i want to use average function. In sheet1 i have values in only two cells. B1 cell has value 5 and B2 cell has value 15. Using these ...
0
votes
1answer
27 views

How do I replace two or more instances of a character in an Excel string?

For example, what function or combination of functions would I use to convert string 1, "this_is_a_string" to string 2, "this is a string"?
0
votes
2answers
33 views

“#REF!” appears in cell after referred-to cells are pasted in

On Office 2007's Excel on Win7, I have a file with a formula like AVERAGE(A29:A33) in cell A34. I then copy four vertically-adjacent number-containing cells and paste them into cells A29:A33. At ...
0
votes
2answers
52 views

How do I locate two values in excel found on the same row?

I've got an index with multiple sheets full of names, sections, townships, ranges, dates, books, pages... and wanted a formula to locate two cells matching on the same row. For example: Column 'D' ...
0
votes
1answer
48 views

Subtracting hours from time

I am trying to subtract hours from a time, but it's giving me strange results A1 = 06:00:00 (manual entry, hours to deduct) B1 = 00:30:00 (=C1-A1, start time) C1 = 06:30:00 (manual entry, end time) ...
0
votes
0answers
36 views

Excel Forumla for SUMPRODUCT to exclude rows/cols

I am rather new to using Excel. How can I take a formula like this =SUMPRODUCT(B3:B39, A3:A39) and have it exclude specific rows that I am using as headers/dividers. But seem to break the formula ...
0
votes
1answer
14 views

How to delete and retain a particular value in a cell based on the value of a certain cell?

How to make a vb code to delete the contents of Column B when i entered a specific value in column A? Example: If cell A1=apple, cell B1 should delete whatever value it have. But if cell A1=grape, ...
-1
votes
2answers
149 views

How Do You Stop Auto Formatting to a Cell I'm Using as a Barcode Number [duplicate]

I am having a little bit of a problem with Excel. I am typing into a cell a barcode number for my products but every time I leave the cell it automatically formats the cell as below. Number I want to ...
1
vote
2answers
51 views

Excel: Check for specific value in a matching row

I want to check which products (listed in rows) are produced by which company (listed in column headings) in order to fill a table like this Company A Company B Company C Product 1 ...
0
votes
1answer
115 views

Excel formula to sum the total for a specific person in a specific month

I need help with an Excel formula that sums the total or work for a specific person in a specific month. Here is what my data looks like: I need to setup a formula to extract automatically the sum ...
0
votes
1answer
77 views

Calculation of hour in Excel using [h]:mm formula

START END TOTAL TOTAL 21:00 3:15 6:15 6:15 23:25 1:11 1:46 1:46 20:25 5:20 8:55 8:55 ...
1
vote
3answers
43 views

formula for date to fill its self out but results stay hidden when date is blank

I have made a spreadsheet that when I put in the date at the top of the header it automatically fills in the rest of the spreadsheet. BUT, I would like for the results to stay hidden when there is not ...
2
votes
1answer
262 views

How to hide formula results until conditions are met

I am completely new to making spreadsheets with Excel and even more dumb when making formulas. I have come to surprise myself how much I have learned, but now I am stumped and could really use some ...
0
votes
1answer
118 views

How to do a sliding window in an excel table?

I have a table with a date column and a value column like this |A |B 1 |date |value 2 |2013-12-10 |1 3 |2013-12-10 |3 4 |2013-12-09 |2 5 |2013-12-04 |5 6 |2013-12-02 |2 Dates can ...
0
votes
0answers
62 views

Is there something better than VLOOKUP?

I'm wondering if there is something like a VLOOKUP for multiple matches in one column. So, for example, if I have the following: Alfred red Jone orange Christina blue Danielle blue ...
1
vote
1answer
40 views

Add cell data to new column at the end of each row in excel?

I have a spreadsheet and I need to combine the information in three cells and add the combined information to a new cell on the same row. For example, I have columns A through M and rows 1 through 10. ...
1
vote
2answers
57 views

What functions can I use to track subscriptions?

I am struggling to make a spreadsheet which will help me take control of member's subscription fees. Fees are paid per month. The spreadsheet has 12 horizontal cells, each one for a different ...
0
votes
2answers
213 views

VLOOKUP with multiple criteria

I have a workbook with 2 sheets: spg and data From the data sheet I wish to do a vlookup in the spg sheet, based on the value of multiple columns in spg. I can do a =VLOOKUP(H5,spg!$D:$I,6,0) but ...
0
votes
1answer
28 views

How do I lookup using 2 values, Verticle and Horizonal? (Apple Numbers)

I have a sheet that has an individuals info, one table as their Age (TABLE 1). Another Table has a score in cell (TABLE 2). I want to lookup the VALUE that corresponds with their age and score in ...
0
votes
1answer
40 views

multiple IF Calculation excel

I currently have this formula in Excel: =IF(G1+I1>E77,H1) Sometimes I want it to be like this: =IF(C1+E1>G1,D1) I need both these formulas in the same cell but there will only ever be a ...
0
votes
2answers
33 views

Force a one-dimensional array into a constant in Excel

I have a condition in an Excel IF-statement as follows: INDIRECT(ADDRESS(ROW(ME3),MATCH("PremiumWaiver",$1:$1,0)))=1 where INDIRECT(ADDRESS(ROW(ME3),MATCH("PremiumWaiver",$1:$1,0))) eveluates to ...
0
votes
1answer
58 views

How can I find max value and display text next to it in Excel?

I need a formula that will find the highest number in a column and then display the text in the cell immediately to the left of that number, i.e. find the highest grade and display the name of the ...