Questions about using in-cell functions/formulas in spreadsheet software.
0
votes
2answers
172 views
Excel find bucket without nested IFs
I have to separate my data in category, say for example:
Category Minimum Maximum
A -100 -20
B -20 0
C 0 +20
D +20 ...
0
votes
1answer
67 views
Find a Certain Cell based on other Cells in Excel/Calc
I have a spreadsheet:
Beans B-kg Chips C-kg
1.4oz/12 0.47544 6.5oz/20 3.679
1.48oz/12 0.502608 7oz/12 2.3772
1.86oz/12 0.631656 8oz/20 4.528
and a ...
1
vote
1answer
58 views
Convert Text into a Formula in Calc
I have a bunch of cells in a spreadsheet like the following:
1.48oz/12
I am trying to replace the oz/ with * and then calculate it.
So far I have:
=REPLACE(G4,FIND("oz/",G4),3,"*")
which yields:
...
0
votes
2answers
6k views
Can Excel use formula-input data for cell references?
I have a cell in Excel, to which I want to pull data from another cell that is defined as a value in a third cell.
For example:
A1=5
A2=10
A3=insert syntax here, using A4 as source for cell ...
0
votes
1answer
1k views
Copy/move values matching a pattern to another column
I have a worksheet where we need to move all the values matching a pattern to different columns.
For example, we have a B column with values that are one or more letters followed by one or more ...
0
votes
2answers
142 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
564 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
3answers
655 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
129 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
84 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 ...
1
vote
3answers
332 views
Excel Macro To Lookup a User Entered String, and return data from the field next to it
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
236 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
389 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 ...
3
votes
1answer
224 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
380 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
68 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 ...
2
votes
1answer
142 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
172 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
...
0
votes
2answers
447 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 ...
1
vote
2answers
333 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
2answers
2k 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
120 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
1answer
51 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
408 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
66 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 ...
0
votes
2answers
149 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
267 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
142 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 ...
0
votes
1answer
41 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
116 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 ...
2
votes
3answers
131 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 ...
0
votes
0answers
40 views
Comparing two strings in Excel [duplicate]
Possible Duplicate:
if Excel row contains particular words, return that row or highlight that row or give Y/N for that row
I have two Columns with Company Names and I want to find out which ...
2
votes
1answer
122 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 ...
1
vote
2answers
404 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 ...
0
votes
2answers
42 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 ...
1
vote
1answer
52 views
Returning The Name of the highest value field
I have 4 field of Numeric Values, I want Excel To pick the highest value of them and show me the name of the field that is related to the highest one, for example:
Option 1 (J10) Option2 (K10) ...
1
vote
3answers
110 views
How can I add leading zeros between delimiters in Excel 2010
I am trying to convert a list of property id numbers that has a standard format of 0000-A-00000-00-00, where my worksheet has the various combinations of
1-A-123
10-B-1234
Ideally they would ...
0
votes
2answers
6k views
Excel: Return value of different cell in same row based on a 3rd cell value
What I am looking for is not an ambigious "if" return while also "search" and "find" are not advanced enough to fulfill my slightly complicated but easy-to-understand goal:
I have one worksheet (#1) ...
1
vote
4answers
842 views
Calculating the calendar week in Excel
I am using the following formula to calculate the calendar week if referenced to a cell with date:
=INT((A1-DATE(YEAR(A1);1;1)-WEEKDAY(A1))/7)+2
but it does not work properly. If I enter 24.08.2012 ...
1
vote
1answer
61 views
Excel lookup not working as I expected
I have this:
d a 1 =VLOOKUP(A1,$B$1:$C$5,1)
e b 2 =VLOOKUP(A2,$B$1:$C$5,1)
f c 3 =VLOOKUP(A3,$B$1:$C$5,1)
g d 4 =VLOOKUP(A4,$B$1:$C$5,1)
h e 5 =VLOOKUP(A5,$B$1:$C$5,1)
...
1
vote
2answers
67 views
How to name column headers (column B to X) based on values in column A?
Consider the following excel worksheet:
| ... | H | I | J
--+-----+--------+------+-----
1 | ... | obsDay | obs3 | obs4 ...etc
--+-----+--------+------+-----
2 | ... | 3 | |
...
0
votes
0answers
63 views
Search multiple worksheets, even when new ones are added
In Excel I'd like to search across multiple worksheets without having specifically to give the name of each one, so if I add a sheet it will look across that sheet too. I know how to look across ...
0
votes
1answer
55 views
IF Formula Ranging
I'd like to figure out when a range of cells equals one cell and give it a value. For example:
=IF(O40>=BT40,"1",IF(O40+P40>=BT40,"2", IF(O40+P40+Q40>=BT40,"3")))
Basically this ...
0
votes
2answers
383 views
Excel 2010 formula conversion to Excel 2003
I am by no means overly proficient with Excel, I know enough to do the basics.
After much forum and soul searching I had come up with the following formula, which I will discuss what it is supposed ...
3
votes
1answer
334 views
Strange Excel IF formula evaluation
In a particular strange workbook,
In cell A1, I key the value 0
In cell A2, I key the formula =IF(A1="APPLE", 1, 0)
The result in cell A2 gives 1.
I tried to replicate the same in a new workbook, ...
1
vote
1answer
638 views
Finding non blank cell then in same row if another cell is blank then return value of another cell
I have three columns. "Site ID" with a value, "Ready" with date and "Installation Completed" with date.
Now I want that I search whole column B for non-blank cell, if I found then it checks column C ...
1
vote
1answer
93 views
Search for data in Excel 2010 that can be anywhere in a column
I am attempting to search for data anywhere in a column. I have the following formula but I think I need a wild card. My data may be in cell J2012 instead of J2. Suggestions?
=IF( ...
1
vote
2answers
164 views
How do I highlight rows based on latest time stamp in Excel?
I have 4 columns, A B C D.
Column A has a designed value.
Column B has a reviewed value.
Column C is empty.
Column D has a formula which returns false if value in column A does not match value in ...
1
vote
2answers
224 views
How do I fill weekdays on a calculated cell?
I'm trying to autofill cells to exclude weekends. The thing is, the date is calculated and the context menu for fill weekdays is grayed out because of it.
So basically I'm trying to say something ...
0
votes
2answers
153 views
How do I add a value to a cell in Excel if it's an int?
I have an Excel table with some cells that are numbers and some cells that are strings. I want a function that can add search through the table, and if the cell is a number, add it(+) and if it's a ...
