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

**1**

vote

**1**answer

36 views

### Clear contents from matched column of data in another sheet

I have a column of email addresses on sheet 2 col A (but I could put them on sheet 1 if it would make it easier / faster) that I want to remove from sheet 1 col D if matched (there may be 2 or more ...

**0**

votes

**1**answer

33 views

### =randombetween with a certain percentage between a narrower range

I want to randomly generate dates (within a four-year period) for 32 different records/rows in excel.
Additionally, I want 75% of those dates to be during one year in particular (within that four ...

**0**

votes

**1**answer

53 views

### Excel formula for collapsing adjacent rows with a sum

I have a spreadsheet with values like so:
blah 1 12
2 12
3 12
foo 1 6
bar 1 2
foo 1 6
2 5
Numbers in the middle column range from 1 to 8; ...

**0**

votes

**1**answer

48 views

### Excel - minifs function returning 0

I'm trying to write a function in Excel 2013 that returns the minimum of a set of cells that meets two criteria, but the function always returns zero.
On one worksheet, I have a table with several ...

**0**

votes

**2**answers

57 views

### Calculating time between 5a-1p or 530a-130p

I was wondering if it is possible for Excel to calculate time span if each time block is in 5a-1p format in one cell. That way I could add the hours for each week without changing too much.
Sample ...

**0**

votes

**0**answers

48 views

### Excel using INDEX and LIST to return multiple results [duplicate]

I'm trying to use Excel with the INDEX and LIST functions (rather than VLOOKUP) as I am trying to return multiple results from one worksheet into another.
I've got close with the following formula: ...

**1**

vote

**3**answers

106 views

### Excel function working like SQL group by + count(distinct *)?

Suppose I have an Excel sheet with below data
CODE (COL A) | VALUE (COL B)
==============================
A01 | 10
A01 | 20
A01 | 30
A01 | 10
B01 | ...

**8**

votes

**1**answer

82 views

### How does the NOW() function get evaluated cross time zones in Excel Online?

How does EXCEL ONLINE evaluate the NOW() function when a spreadsheet is accessed from different time zones? For example, if user PAUL is in London, UK and accesses a sheet at 5PM GMT and user SUE is ...

**0**

votes

**1**answer

28 views

### How to compute regularization date in excel [duplicate]

Hi I want know if there is a formula in excel. Let's day I was hired Feb 24, 2014 so regularization is 6 months so my regularization date would be August 24, 2014. I want to know the formula in excel ...

**1**

vote

**1**answer

18 views

### Date Arithmetic in Spreadsheets

I have a bunch of data in a spreadsheet where the left-most column represents a date in the format DD-MM. Here's an example for column A:
15-09
23-09
25-09
30-09
02-10
I ...

**0**

votes

**1**answer

80 views

### Excel SUM From Different Sheets IF Date Found

I have a workbook with separate sheets for each product (about 20 sheets, adding more on a regular basis). Each product is only available for a certain time frame, and has daily sales data recorded on ...

**0**

votes

**1**answer

72 views

### How to require a cell input if another cell has a value

I'm trying to edit the VBA for one of my workbooks so that If there is a value in column A, then a value for column C is required or else the file won't save, but i'm having some trouble with this. I ...

**0**

votes

**1**answer

36 views

### Double Filter in Excel

I'm trying to "stack" filters in excel, so to speak. I want to filter column A to show anything greater than 30 and then I want to filter column B to show the top ten items. When I do this, however, ...

**0**

votes

**0**answers

39 views

### Creating automated Microsoft Excel filtered list

I have the following situation in one of my Excel sheets:
I have a table with some values, particularly "date", "stock" and "cost" among other data. The table may contain values from various ...

**0**

votes

**3**answers

44 views

### Excel Extending Equations

So I have an excel table that is multiply 1 value against several other values. It looks like this:
So I want the equations inside cells C14 to F14 to be B14*C5, B14*C6, B14*C7, B14*C8 respectively. ...

**-1**

votes

**1**answer

59 views

### Excel: if cell in column A = text1 and cell in column B = text2 then cell in column C = text3

I'm desperately trying to figure out how to create a formula. In column F, I've created a data list of 5 items. In column H, I've created another data list of 5 items. In column I, I'm trying to make ...

**0**

votes

**0**answers

80 views

### Google sheets formula reference cell data in another sheet

I need help with formatting a sheet. My sheet that I need to format is an attendance list. I want the names of the students in column A that have a certain teacher. I want to pull those names from ...

**2**

votes

**1**answer

36 views

### Auto formatting new pivot rows to data bars

I have a pivot table that takes data from database and displays it as a data bars. Problem is, that the data is updated daily - every day new row is added so the pivot table is growing. Unfortunately ...

**0**

votes

**1**answer

56 views

### How can I sort data that is organized in 4-row chunks?

I have an Excel document with six columns and 57,000 total rows. Each record comprises four rows, that is, each set of four rows goes together. How can I sort this data by one field while keeping the ...

**0**

votes

**1**answer

58 views

### Return day number in year from date in Excel

I am trying to show the date in an adjacent cell as the number of days in the year.
Jan 1 would be 1, Feb 1 would be 32, Dec 31 would be 365.
Also the reverse, how many days are left in the year.
Jan ...

**0**

votes

**1**answer

26 views

### Excel: filter consistent information from constantly changing cell

I am setting up an Excel spreadsheet and I am needing to extract a dollar amount out of specific cells. The problem is that there will always be a dollar amount in the cell but the information on ...

**0**

votes

**1**answer

113 views

### VBA Solution to VLOOKUP with Hyperlinks

I am looking for some help with a VBA solution for preserving hyperlinks when using VLOOKUP on Excel (2010).
I have a load of data on Sheet 1 for internal use only, and a cut-down version of this ...

**0**

votes

**1**answer

32 views

### Excel - Four returnable values, given two crieteria

I need excel to return one of four possible values given two criteria. The two criteria are:
Is the value in cell A1 >= to 10 OR A1 < 10 (two possibilities)
and
Is the value in cell B1 > ...

**0**

votes

**1**answer

27 views

### Windows 7 Enterprise, Service Pack 1. Software MS Office Excel 2010

In Excel I understand there is no mechanism to customise & re-label the Rows & Columns (i.e. Renaming Col. A to some text like "Item Number" and so on.
My question is regarding if it's ...

**3**

votes

**2**answers

142 views

### Highlighting Cells in Excel based on the Value Associated with the Cell

I have two sheets:
Users in their respective groups and
A compilation of those same users with an expiration date.
I'd like to highlight any cell in the the first sheet if their respective date ...

**0**

votes

**2**answers

56 views

### How to dynamically populate a filter list with “first match” on a row, using excel 2007

The image is an example of what I am working with. I am trying to write a formula in my leftmost column, just under the listbox cell that you can see, which will find a string/value match in that ...

**2**

votes

**1**answer

20 views

### How do i add a running total in A1 to B2 if there is a value OR subtract from C2 if there is a value

What I am trying to accomplish is that when I enter a number in B2 it will only subtract,
A1-B2= A2 but if i put a number in C2 and nothing is in B2 I want it to add A1+C2=A2. I really tried looking ...

**1**

vote

**1**answer

31 views

### Total row doesn't work properly, Excel 2010

In my Excel 2010 file and in one of its sheets I have a small table. As following, when I checked the Total Row option in Table tools > Design > Table style options > Total Row the tabled appeared as ...

**3**

votes

**1**answer

382 views

### Add/Subtract button Excel VBA

Here is an example of my project in this picture
I like collecting, so I am making an inventory sheet in Excel.
I want to make a way that I can click a button and it affects the next, or previous ...

**0**

votes

**1**answer

54 views

### SUMPRODUCT and IF conditional (LibreOffice) [closed]

I would like to multiply two cells in a same row, if a condition of a third adjacent cell is met; then sum the result to the next product in the next row (if condition is met), and so on.
| A | B ...

**0**

votes

**2**answers

133 views

### Excel: AVERAGEIF on multiple work sheets (via 3D Reference)

I have a question when using excel averageif functions:
I have a workbook containing monthly information (stored as numerical values) in 12 different worksheets (named Jan, Feb,..., Dec). In each ...

**2**

votes

**1**answer

44 views

### Match formula excel case sensitive

I have an if(iserror(match formula in excel. Just when I thought I was finished with my sheet I found out that the formula isn't case sensitive. Does anyone know how I can make my formula case ...

**0**

votes

**2**answers

778 views

### Searching for a value in Excel, returning yes or no

I have one workbook, with two separate worksheets. I want to know if the values that appear in worksheet B also appear in worksheet A, if so, I want to return a "YES". If not, I want to return a "NO". ...

**0**

votes

**1**answer

66 views

### Excel - Sum certain range from pivot table

I have an excel pivot table that looks like the following:
Is there any way I can create a formula that will calculate the sales$ for a particular product at a particular store for a particular ...

**1**

vote

**3**answers

81 views

### Trouble using Excel OFFSET function

I have an Excel workbook with numerous sheets entitled "Week1", "Week2", "Week3" ... "Week7". Each week, I input the customers IDs of people buying my product in column C, and -- as I am interested ...

**0**

votes

**1**answer

49 views

### In Excel selecting an item from row when cell matches the first item in that row

I have a LIST of 5 optional sizes. There are several numbered mating parts that coincide with each of the sizes forming a pair. Any 1 of the mating parts can go with a chosen size, but only 1. Some ...

**0**

votes

**1**answer

24 views

### Define names in workbook scope

I use MS Excel 2010. In this app, I know that using Formulas > Define names > define name it is possible to name a cell (or range).
At that dialog box there is a scope rectangle which if I choose the ...

**1**

vote

**1**answer

123 views

### Excel, compare cells in different sheets in a workbook

I am a real nooby and I have some questions.
I have 5 sheets (A,B,C,D,E). Each sheet has numbers in it (10 rows-15 lines) A1 to I15
Now I want to make a 6th sheet in which I can compare the cell ...

**0**

votes

**2**answers

92 views

### Is there one simple formula for Excel that can identify a string contain non-ASCII Characters?

I know that using the clean formula, I can clean up some of the non-ASCII characters (such as additional non-printable ASCII control characters #0 through to #31, #129, #141, #143, #144, and #157 ...

**3**

votes

**1**answer

75 views

### Toggled Hyperlink with If:Then formula [duplicate]

I'm attempting to make a formula that will toggle a hyperlink on or off depending on the argument. Basically, I want to make it argue the following:
If there is text in (Tab A, Cell A), then create a ...

**-1**

votes

**1**answer

26 views

### Which formula should I use to place a user ID in one column based on a name in another column? [closed]

Edited to reformat: I have 530 rows of employees in a spreadsheet, each has their manager listed in column H. There are 15 managers. In Column I, I need to assign an auditor to review each employee. ...

**0**

votes

**1**answer

43 views

### Search Excel price grid with variable values

WIDTH
DROP 1150 1550 1850 2160 2360 2750 3000 3480 3980 4450
1200 338 348 378 413 431 450 467 513 ...

**-1**

votes

**1**answer

47 views

### How to split up above single cell Address value into multiple cell value through macro

How to split up the below single cell value into multiple cell value through macro ?
So, this...
A1 = MISS FirstName LastName***95*SPECIAL ROAD**LONDON SW92**SW929AB
...should split as below,
B1 ...

**0**

votes

**1**answer

304 views

### Excel formula to sum all entries in a row if value of adjacent cell is equal to third cell [duplicate]

I need to build a formula that will sum the numbers in a series of cells only if the value of an adjacent cell is equal to the value of a third cell.
The latter two values are text.
In plain ...

**0**

votes

**2**answers

51 views

### Excel 2007 IF AND function calculation not copying across

I'm using an IF AND function, which is:
...

**0**

votes

**1**answer

39 views

### How to lock a cell with formula in excel after the referred cell is filled?

I'm using "CONCATENATE" formula in excel and I would like to lock (or turn the formula into value in) the cell which contains the formula after the last referred cell is filled. Some of the referred ...

**0**

votes

**1**answer

65 views

### SUMIF with multiple match criteria in Excel

I'm trying to create a formula so that if in cell E2 it says "Market", "Limit", or "Stop" it adds the numbers in cells L2 and M2.
=SUMIF(E2, "Market" "Limit" "Stop", L2+M2)
These expressions are ...

**0**

votes

**2**answers

115 views

### Nneed to calculate average of NON sequential multiple cells & I want to exclude values = 0 or Empty Cell with AVERAGEIF

Nneed to calculate average of NON sequential multiple cells and I want to exclude values = 0 or Empty Cell with AVERAGEIFS.
There is a mistake in my formula but I can't find a way to have it since ...

**0**

votes

**2**answers

583 views

### Excel compare two columns and list a third column

I have an Excel sheet which has 3 Columns:
A B C D
1 A 1
2 B 3
3 C 4
6
5 D
7 E
I want to check column C for values present in column A and list corresponding values of ...

**0**

votes

**1**answer

64 views

### Conditional formatting in Excel 2013 for C8=blue and bold if C5>C2

This is part of a problem for college for which I can't find a solution.
Q: Use conditional formatting to format, in blue and bold, the value in column 8 for which the value in column 5 is greater ...