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

**3**

votes

**2**answers

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

**2**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**2**answers

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

**1**answer

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

**2**answers

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

**1**answer

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

**0**answers

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

**1**answer

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

**2**answers

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

**0**answers

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

**1**answer

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

**0**answers

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

**1**answer

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

**2**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**0**answers

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

**0**answers

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

**1**answer

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

**3**answers

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

**3**answers

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

**1**answer

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

**0**answers

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

**2**answers

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

**1**answer

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

**2**answers

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

**2**answers

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

**1**answer

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

**0**answers

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

**1**answer

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

**2**answers

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

**2**answers

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

**1**answer

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

**1**answer

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

**3**answers

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

**1**answer

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

**1**answer

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

**0**answers

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

**1**answer

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

**2**answers

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

**2**answers

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

**1**answer

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

**1**answer

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

**2**answers

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

**1**answer

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