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

**0**

votes

**2**answers

535 views

### Find a range of cells with a MATCH

OK so I know I can do this with VBA but I'm interested if this is possible without....
Say I have a table like this with some tickers:
Page | Ticker
----------------------
1 | UKX index
1 ...

**1**

vote

**1**answer

2k views

### Excel 2007 - Conditional Formatting: Compare 3 columns with text - find unique values

I have a spreadsheet with three columns, each column listing the folder names found on a server.
Server1 | Server2 | Server3
--------|---------|---------
A | | A
...

**-3**

votes

**1**answer

309 views

### 24 hour conversion to decimal [closed]

I figured it out _ I had to change the cell format to number and add *24
Using the following
=SUM(MOD(C6-B6,1)+(E6-D6)) I get a correct 24hr sum of time (9.30) however I need that in decimal format ...

**2**

votes

**1**answer

3k views

### How to enter a fraction in formula editor of Word?

How can I enter the following fraction in Word for Mac (with the keyboard, clicking twice every time I need it is obviously not a solution)?

**-1**

votes

**1**answer

474 views

### Highlighting duplicate column-pair and counting the rows Excel

Given the data below, the column-pair with the same values for at least 4 consecutive rows should be highlighted.
Here's an image for better visualization:
2 2
3 4
3 4
3 4
3 4
2 3
1 2
...

**0**

votes

**3**answers

2k views

### Highlighting duplicate column-pairs in Excel

Example, this is the data in Excel:
1 2
2 2
3 4
3 4
3 3
2 2
2 2
1 2
The rows which have consecutive duplicate column-pair values (in bold font) should be highlighted:
1 2
2 2
3 4
3 4
3 3
2 2
2 2
1 ...

**1**

vote

**3**answers

10k views

### How to identify cells in Column A that contain any of the values in Column B

First off, I'm not that advanced of an Excel user. I'm certain Excel can do what I'm about to ask, but I've yet to find a way in any of my attempts or searches.
My question is very similar to Find if ...

**2**

votes

**3**answers

355 views

### Excel formula to calculate power of [closed]

Given the following equation: 2^X - 2 = N
I'd like to solve for X. N is known.
What function could I use in Excel to do this?
Thanks in advance.

**1**

vote

**1**answer

171 views

### Copy text value and formatting to another cell

I have to call the text value of cell A1 using index+match function to A2.
A1 contains two fonts – Calibri (11) and Times New Roman (20). I want to get the same text format in cell A2.
How can I ...

**0**

votes

**1**answer

990 views

### Excel Macro To Lookup a User Entered String, and return data from the field next to it [closed]

On worksheet A, a user is prompted to enter a product number, such as BCI610.
On worksheet B somewhere, that value exists.
I want excel to lookup/find that value, and then return the data in the ...

**0**

votes

**2**answers

559 views

### Formula to calculate the 5th [day of the week] when applicable

I've used the following formula to get the nth xday of the month:
=DATE(B2,A2,1+7*n)-WEEKDAY(DATE(B2,A2,8-xday))
where year is in B2 and month (as a number from 1 to 12) is in A2, and where xday is ...

**0**

votes

**2**answers

1k views

### How to lookup a value in a table with multiple criteria

I have a data sheet with multiple values in multiple columns. I have a qty and a current price which when multiplied out gives me the current revenue (CurRev).
I want to use this lookup table to ...

**0**

votes

**2**answers

4k views

### Setting background colour of cells based on their string length

Suppose I have this column of data
Canada
Chile
China
...
How do I set the background colour of each cell if the length of the string in it is greater than say 5? ('Canada' cell would be ...

**0**

votes

**1**answer

266 views

### Adding value of one cell with previous value of same cell if value of another cell matches in Excel

Here is my data in sheet1 of excel:
A B
A1 20
A2 2
A1 2
A3 3
Consider the above in an excel sheet. Column A contains duplicate keys and ...

**3**

votes

**1**answer

3k views

### What does “calcs” formula do in Microsoft Excel?

I encountered an XLS file with this formula in some of its cells. I searched the web for information on the formula, but I couldn't find anything. Could you help?
The formula in question is:
...

**2**

votes

**2**answers

2k views

### What function should I use in Excel for searching a (multiple) text string?

The title is a bit unclear, but I'll be explaining it now for better clarity.
I have this:
When I type in the Input field, I'd like Excel to show me the result in the Output field. For example, if ...

**1**

vote

**2**answers

116 views

### MS Excel: How to work with a column chosen by value in header (row1)

I am importing a large dataset from a website and I want to build some summary stats on a new sheet. The column order is not fixed, so I want to be able to (for example) count all the cells with the ...

**0**

votes

**1**answer

920 views

### How can I run SUMIF or COUNTIF with two Conditions?

I recently got some great help with SUMIF and COUNTIF and ended up with the following, working formulas;
=SUMIF(REC!$F:$F,$C2,REC!$G:$G)
=COUNTIF(REC!$F:$F,$C2)
I had been mixing up the syntax and ...

**2**

votes

**1**answer

567 views

### How can I write an excel formula to do row based calculations; where certain conditions need to be met?

I am given:
An excel sheet contains around 200 tasks (described in rows 2-201 in Column A).
Each task can be elegible for a max of two projects (There are 4 projects in total, called "P1-P4" - drop ...

**-1**

votes

**4**answers

252 views

### Multiple “IF” formulas

I am trying to write an IF formula for an order form where a discount is applied depending on the amount of units ordered.
The discounts are:
0 to 24 units - 0 discount
24 to 59 units – 5% discount
...

**3**

votes

**1**answer

10k views

### Excel Formula: What is the equivalent of =MATCH(), but for multiple columns?

Problem
This formula will return the row number where the value "Bob" appears. Say the value happens to be in $A$10, the formula would return: 10
=MATCH("Bob",A:A,0)
I would like a formula to do a ...

**0**

votes

**2**answers

2k views

### Complex rota hours calculation in excel

OK so there are a number of different shifts in operation here and I want a total hours formula.
problem is the rota is written using a number of different notations for different shifts
some are ...

**2**

votes

**1**answer

1k views

### Using Excel Linest function with blank cells

I need LINEST function to be applied with blank cells to get the slope parameter as well as the associated standard error. I can get the slope parameter with a normal function that may resolve the ...

**1**

vote

**2**answers

2k views

### Create table, by conditionally copying rows from another table - possible in MS-Excel?

Every week, a new Excel file is generated by a CRM system, which has the date-stamp in it's file-name, but placed in a folder at a fixed location (a sharepoint URL).
This file is quite large, and ...

**1**

vote

**5**answers

7k views

### Find, match whole word only workaround

I just noticed, there is no option in Excel Find, to "match whole word only" (I am not talking about "match entire cell content") e.g. searching for "part" should not match "partner". MS Word has this ...

**0**

votes

**1**answer

497 views

### In Excel input filter values in cell

Using Excel I am searching for records using Filters>Down Arrow>Text Filters>Contains
(refer screenshot), this is very tedious as the seach window disappears after taking the imput and I have to ...

**1**

vote

**2**answers

74 views

### Excel - determine average using 1/2 value for flagged cells

Is there a way to calculate an average of a column of numbers using 1/2 the value for cells that are flagged by an asterisk in an adjoining column without having to insert a separate column?

**2**

votes

**1**answer

983 views

### How to use countif, sumif across two sheets where the logical test is an equivalency?

I am building a simple sum sheet that relies on a conditional but I can't seem to get COUNTIF/SUMIF to work for me.
To sum, I have two sheets, one with a few hundred lines of records including an ...

**0**

votes

**2**answers

340 views

### excel 2010 Blank cell in place of 0

How do I use this formula and add if 0,"" so the cells are blank rather than 0
=SUMIF(NotReady!AA17:AA50,5,NotReady!AB17:AB50)
I would like it in formula rather then cell format please
Thanks in ...

**1**

vote

**1**answer

77 views

### Is there a way through an excel formula to have it where a blank cell and a specific value show 0?

I have a cell that shows =IF(ISBLANK($E12),1,0) I have another that shows =IF($E4="Total:",1,0)
Is there a way to make them do the same thing, so if the cell shows 0 or Total: it shows 1, otherwise ...

**-1**

votes

**1**answer

884 views

### Calculating the number of occasions with a set period sumproduct function

=SUMPRODUCT((F16:F274=("A")) *(F17:F275<>("A")))
+SUMPRODUCT((F16:F274=("AH")) *(F17:F275<>("AH")))
+SUMPRODUCT((F16:F274=("AU")) *(F17:F275<>("AU")))
...

**1**

vote

**1**answer

166 views

### How to copy a row conditionally?

I've searched for a similar question here, but haven't been able to find something that answers my issue.
I'm a mediocre user of Excel 2010 with no experience in macro's. I have a dataset where each ...

**1**

vote

**1**answer

193 views

### Excel Date Formula with a condition

I have 3 fields in excel as follows:
Cell A1=2012 A2=12 A3=15 which contains year, month & date value respectively. And I want to make a date string out of it.
I know it's very easy to achieve ...

**0**

votes

**2**answers

421 views

### Excel - How do I highlight a cell if its address is stored in a column?

I have a worksheet with a list of 'Special Cells' in a column, like 'C352', 'E11', etc.
How do I write a conditional formatting rule that looks through the column on the other worksheet, and if that ...

**1**

vote

**3**answers

4k views

### How can I get the Cell Reference of a Named Range in Excel?

I solved the issue, but I'm wondering if there's a better way to do this. What I have is a column of some named ranges ("NAME"), and I want a formula to the right that looks up the named range and ...

**1**

vote

**1**answer

208 views

### IF/THEN statement for text

In my Excel spreadsheet:-
In Cells A1 and down I have cities listed. For instance Cell A1 has: "Daytona Beach, FL"
In Cells B1 and down I have some generic information with the word CITY in it.
For ...

**1**

vote

**1**answer

218 views

### Getting a MAX using AVERAGEIFS logic

I have a table of data that I want to find a MAX value for.
The challenge is filtering the values I care about.
Basically, I want the MAX from Column D when:
Column A = 'VIC'
Column B = 'CBD'
...

**1**

vote

**2**answers

175 views

### Search for part of a string in a cell with Excel

If I have a string to find in a cell such as:
[Agent Name & ID: Christopher Montaray - 1011]
… and I use the IF statement, can I search for a part of it? For example:
=IF (A1="Chris",1,0)

**3**

votes

**2**answers

5k views

### Excel: conditionally format a cell using the format of another, content-matching cell

I have an Excel spreadsheet where I’d like to be able to create a “key” of formatted cells with unique values, and then in another sheet format cells using the key formatting.
So for example, my key ...

**0**

votes

**1**answer

50 views

### I need a name search function to insert 7 rows for each missing agent

Morning I use excel 2010, I grab data everyday on sales rep stats, some are off or on vacation from time to time - what I need is a formula to search for each of the reps in order and insert 7 rows ...

**1**

vote

**2**answers

169 views

### Formula to merge column from sheetA to sheetB to matching ID

Match unique id Sheet1!A2 to a cell in Sheet2!A2:A100 that has the matching ID.
Paste cell content from column Sheet2!C2:C100 from the same row with the matching id in Sheet2 to the cell with formula ...

**1**

vote

**4**answers

3k views

### Can SUMIF show blank for 0 result?

=SUMIF(Auto_Tracked!B1:B300,"rcumbaa",Auto_Tracked!D1:D300)
I would like it to show blank rather than 0 if possible

**0**

votes

**3**answers

89 views

### Copy a group of cells in excel 2010 without copying the formulas

Is it possible to copy a group of cell results with out pulling the formulas over to a new worksheet ?
what happens now is it copies the formulas with the =Sum(workbook!Formula) to new workbook, I ...

**2**

votes

**3**answers

171 views

### Excel 2010 input blank for all 0 results

I use this
=IF(MINUTE(E59)+(SECOND(E59))=0,"",(HOUR(E59)*60)+(MINUTE(E59)+IF(SECOND(E59)<30,0,1)))
To calculate time into minutes like 00:06:13 = 6 as a result and 00:00:00 = blank as a ...

**2**

votes

**1**answer

192 views

### Show (copy) data at “X” time and stop update

I have two sheets.
In the first sheet, cell F4, I have 00:00:00 (countdown). G9, G10 and G11 are cells that receive live data (decimal numbers).
In the second sheet, I have three cells linked from ...

**3**

votes

**2**answers

3k views

### Counting occurences of a character in an excel spreadsheet

Feel silly asking this question, but I need some help!
I want to count how many * appear in a range of 5 cells.
The cells will range from 0-7 * each.
I was thinking of using the ...

**6**

votes

**3**answers

11k views

### Excel formula to convert boolean values into {0, 1}

What is the Excel formula to convert boolean values {FALSE, TRUE} into {0, 1}?
Supposing there is one shorter than =IF(cond,1,0).

**0**

votes

**1**answer

141 views

### add blank for 0 result into existing formula

is there any way to use this formula [<30,0,1)) for a group of cells in Sheet1!(A14:I100)] and if result == 0 insert blank or add [=IF(O16=0,"",] somehow so all 0 results just show a blank result. ...

**1**

vote

**2**answers

30k views

### If an Excel row contains particular words, return that row, highlight that row, or give Y/N for that row

I have a sheet containing thousands of cells in a column. These cell contains names of different securities.
Some names of these securities contains a particular string such as "C/O".
How can I use ...

**0**

votes

**2**answers

99 views

### Parsing out known strings from Excel spreadsheet

I have an Excel spreadsheet that has a list of names in column A. What I'd like to do is parse out all the names in the column that I don't know and their rows. For example, in a spreadsheet of 100 ...