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

learn more… | top users | synonyms (5)

0
votes
2answers
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
1answer
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
1answer
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
1answer
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
1answer
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
3answers
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
3answers
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
3answers
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
1answer
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
1answer
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
2answers
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
2answers
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
2answers
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
1answer
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
1answer
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
2answers
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
2answers
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
1answer
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
1answer
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
4answers
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
1answer
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
2answers
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
1answer
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
2answers
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
5answers
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
1answer
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
2answers
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
1answer
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
2answers
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
1answer
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
1answer
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
1answer
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
1answer
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
2answers
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
3answers
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
1answer
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
1answer
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
2answers
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
2answers
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
1answer
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
2answers
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
4answers
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
3answers
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
3answers
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
1answer
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
2answers
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
3answers
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
1answer
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
2answers
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
2answers
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 ...