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

**1**

vote

**3**answers

86 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

67 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

23 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

17 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

65 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

59 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

35 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

34 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

41 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

53 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

50 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

29 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

51 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

55 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

22 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

77 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

26 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

114 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

45 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

19 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

29 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

176 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

39 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

89 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

38 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

333 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

55 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

74 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

45 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

103 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

55 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

64 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

25 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

41 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

41 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

131 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

45 views

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

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

**0**

votes

**1**answer

32 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

58 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

82 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

221 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

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

**-2**

votes

**3**answers

59 views

### How to decrease the number of sheets in a workbook [closed]

In a new Microsoft Excel workbook (2010) I have three sheets by default. Suppose that I suddenly press the Insert worksheet button next to the Sheet3, so the forth sheet will become added to the list ...

**3**

votes

**2**answers

98 views

### Sum of N-highest number in a series excluding duplicates

I had gone through the thread Sum up to N-highest value of series and it's not exactly what I need. e.g.
=SUM(LARGE(Range;1); LARGE(Range;2); …; LARGE(Range;N))
The question is if the data set has ...

**0**

votes

**1**answer

47 views

### Excel - sumproduct/sumif

I am able to look up single values that meet multiple criterion; but I would like to do more, I want to sum up values that meet two conditions - year and country like in my example. I tried it in my ...

**0**

votes

**1**answer

20 views

### Formula that looks at value in a cell and depending on what the value is

I'm using Excel 2010
I have a number in cell A1.
In Cell B1 i need a formula that says if the number in cell A1 is 25 then put 'account1', if the number in cell A1 is 58 then put 'account2', if the ...

**1**

vote

**1**answer

67 views

### Excel - Change cell colour based on a date in the comment or mixed in with text

I am using excel 2010 and I am trying to set up a rule that will change the colour of a cell based on a date within that cell.
For example the cell may contain Site meeting - 11.05.14
I would like ...

**-1**

votes

**2**answers

44 views

### Excel - comparison of text between two columns [closed]

How do I compare two or more columns in an Excel worksheet to identify the difference. The comparison of two columns is related to text and not the numbers. Please advise.