Questions about using in-cell functions/formulas in spreadsheet software.
1
vote
1answer
38 views
How do I create a list of phone numbers for people whose reference numbers appear in two columns?
I have two sets of data in columns A & B and also in columns C & D. Some of the content in column C & D does not appear in columns A & B:
A = reference numbers
00001, 00002, 00003, ...
0
votes
0answers
24 views
replace images with text in Excel
I have 3 columns in an excel sheet.
ID Product
Name
Image
The Image column has images pasted as objects. I have extracted the images by saving the file as html which gave me an html file and a ...
0
votes
1answer
25 views
Excel: URL with normal Text in a Cell
How can you add an HTML URL to a cell with normal text?
I have a cell with normal text, but want to add a URL, but I don't want the whole cell to be the URL.
i.e.
Please go to this link: ...
-1
votes
0answers
12 views
Trying to match values in a worksheet to a range of values in a 2nd worksheet. both worksheets may contain list/array of values
I have two worksheets, but the there is really only 1 column in each worksheet that is giving me a headache.
Both Worksheet 1 & Worksheet 2 has a column E that each contains only 1 value, the ...
0
votes
0answers
10 views
Runtime Error 1004 Unable To Set the Locked Property of the Range Class
Have searched around but I could not find the answer. The only thing it pointed to was merged cells however I do not have any. If someone wants to look at the file, I can only email as its too big to ...
1
vote
1answer
36 views
Refering certain rows in other sheets based on a particular condition
I require help for making the following table: Consider that I have three sheets in an Excel workbook.
The first sheet is the summary page. The next two are Subject1 and Subject2.
Each of these ...
1
vote
1answer
33 views
How to count based upon multiple conditions
I am trying to create the following formula
=SUM(IF(AND(K3:K138="Tekken",L3:L138="American Football"),1))
However, it always seems to result in zero, even where a single row would have both values ...
0
votes
2answers
29 views
VLOOKUP to search multiple values in a Cell
I have a VLOOKUP that looks up an Account # and Returns a Rep. The Account # field contains historical data (every account including inactive ones). How can I setup a VLOOKUP to take to see pull the ...
0
votes
2answers
21 views
Need simple macro for “match” formula
Are column B cells hidden somewhere anywhere in column C? Give results in column A. (true/false or yes/no)
I'm unable to post the simple Excel image I've created as I'm a guest, but it shows simply ...
0
votes
1answer
35 views
Is it possible to build a cumulative formula that will sum on new worksheet added daily?
I have a worksheet that the user opens each day and clicks a button that copies the current sheet to a new sheet and renames (Day 1, Day 2, etc.).
I want to keep a cumulative count in some of the ...
0
votes
3answers
27 views
Nested IF statement
I'm having a problem with this code for nested =IF statement. The range is basically:
<=16 to get 5 pts
16-17 to get 4 pts
17-18 to get 3 pts
18-19 to get 2 pts
above 19 to get 1 pt
and my code ...
0
votes
1answer
36 views
Compare two date columns and highlight the newest
I have two date columns in the same sheet. Column B receives its data from a vlookup function.
COLUMN A | COLUMN B
2013-07-05 | 2012-01-01
What I want to do, is to compare the two date columns ...
0
votes
3answers
51 views
how do I average the lowest 3 of the final 6 numbers in a row of 22? Some cells may be blank
How do I automatically average the lowest 3 of the last 6 numbers in a row of 22 that adds one number per week? Obviously I need at least 7 numbers to begin using this formula. Also, some cells in ...
-1
votes
0answers
46 views
Format multiple sheets with irregular headers in Excel
I am trying to format a price list from a supplier for import into my own database. The workbook the supplier generates contains multiple worksheets each with the same columns of data that I want to ...
1
vote
1answer
25 views
LINEST function in excel vba
I am trying to figure out the 2 order equation (Y = ax^2 + bx + c) for a set of data values. When I use the inbuilt function in excel
=LINEST(C2533:C2537,B2533:B2537^{1,2})
I get the following ...
0
votes
0answers
53 views
Need to find four common data columns in different locations in multiple workbooks
I am working from reports generated from different projects. Each report has varying columns but all have the four columns that I need. I would like to be able to boil the sheets down to these four ...
0
votes
1answer
25 views
How to check if a date is in the future
I want to make a spreadsheet reporting daily and weekly goals for a certain quantity and their achievement, one reporting sheet per calendar week. Imagine that I open it on Wednesday, then I want to ...
1
vote
1answer
20 views
How to calculate values between three cells using conditional calculation
I am new to this forum and not very new to Excel (not in depth)
I have to perform a calculation for three cells by conditional calculation. so here is the scenario
Column Name: Total Area (Cell ...
0
votes
3answers
74 views
How do you add extra value to a cell that already has an existing number in it
I find this hard to explain, but I'm stocktaking beers, spirits, and liqueurs and I want to keep one column for my ullage count for each product. However, as I go through the days, I often come across ...
2
votes
1answer
28 views
Capitalize first word in each row of a column in mac
I want to capitalize the first letter of every word in each row of a column.
So I create a new column, type in =PROPER(B2), then drag that down the entire length of that column. It works great, it ...
0
votes
0answers
15 views
How to get the average time of two cells hh:mm in Google Spreadsheet [migrated]
In columns K and L I have time stamps. Let's say 03:30 is in column K and 04:30 is in column L. I want the total or average amount of time in hh:mm in column P. I have tried the average function, but ...
1
vote
2answers
54 views
Look up the next highest value in another column with Excel
I have two columns A and B.
A B
20 100
40 200
60 300
80 400
140 500
190 600
240 700
User inputs a value in a cell. I need to check ...
-2
votes
2answers
120 views
Nested IF formula
I want a formula that will capture the following three scenarios below. In column S, I have Orange in cell S2, Mango in cell S3, Grape in cell S4, Pawpaw in cell S5, Onion in cell S6, Apple in cell ...
2
votes
2answers
34 views
Calculate average score for by category in Excel
Assume I have the following 2 sheets in an Excel workbook:
Sheet companies:
A B C
1 COMPANY COUNTRY SCORE
2 Foo Greece 10
3 Bar Greece 2
4 Baz Greece 3
5 ...
2
votes
1answer
45 views
Calculating mean of value across random trials/refreshes in Excel?
Say I have a 1000 cells filled with random values and a particular cell that computes a final answer based on those 1000 values. Now I wish to track the value of the answer over 1000 trials i.e., I ...
4
votes
3answers
63 views
Check if Excel cell text is numeric using formula only
I have to find out if my cells text is a numeric value and wanted to use an elegant non VBA method that doesn't impede on its current state or value.
What I've found is that the ISNUMBER() function ...
0
votes
1answer
33 views
Issue with SUMPRODUCT Function
I am Having a issue with the SUMPRODUCT Function in Excel 2007. Here is my string and issue:
=SUMPRODUCT(($B$2:$B$800>DATE(2013,4,1))*($B$2:$B$800< DATE(2013,4,5)))
A B
1 4/1/2013
2 ...
0
votes
1answer
45 views
Formula to search array and return values
I have a Quotation Book and an Order Book each with a column called Q#. In the Quotation Book the quote numbers are generated and in the Order Book quote numbers are entered without being in order.
...
0
votes
1answer
51 views
vlookup and indirect and external shared workbooks
i have two excel workbooks that are both shared in our company folder.
excel1 is where each employee has his own page to fill information and column 1 of each page is the row reference
excel2 is a ...
1
vote
2answers
44 views
Can I use Microsoft Excel to convert a “ranged” table to a full breakdown table?
Suppose I have a spreadsheet with the following rows and columns:
QTY MIN | QTY MAX | 1 Color | 2 Color | 3 Color | 4 Color
1 | 10 | $1.00 | $1.50 | $1.75 | $1.90
11 | 20 ...
0
votes
2answers
63 views
excel: how to leverage vlookup with count
I have some data in columns, several binary variables:
| A | B | C |
| 0 | 1 | 1 |
| 1 | 0 | 1 |
| 0 | 0 | 1 |
| 0 | 1 | 0 |
| 0 | 0 | 1 |
| 1 | 1 | 0 |
and then another variable with three values:
...
2
votes
2answers
227 views
How to implement MATCH and INDEX functions together in Excel VBA?
I am trying to find the row number of the first cell that is less than zero.
So, by browsing through the net I was able to come up with this formula which works perfectly fine:
...
0
votes
1answer
28 views
Conditional Data Type based on Cell Value
I know that you can conditionally format the visual treatment of a cell using conditional formatting, but can I control the data type of a cell based on the value of another cell?
I'm building an ...
0
votes
1answer
29 views
How to check for specific values in a column
I have a column that I'm working with that has a number of possible text options in it. The only two I care about are "Bad" or "Good."
I'd like to write a function that results in 1 for "Good," -1 ...
4
votes
2answers
40 views
Highlight row if date is in current week
I'm working on a data table for finances. I want to highlight the row if the date is in the current week:
What it should look like:
The current condition formatting for the yellow fill spans ...
-1
votes
1answer
45 views
Excel, comparing data in two columns
I have two columns with various amounts. I know that amounts in column A add up to match numbers in shorter column- B. Is there any way Excel could do this calculation for me and show me which numbers ...
0
votes
1answer
62 views
remove values less than a number in excel array
I have a value that needs to calculate stock based on the previous years sales:
{=CEILING(SUM(IF('Inventory Transactions'!$H$2:$H$7137=LEFT($B6,3),IF('Inventory Transactions'!$O$2:$O$7137='Do Not ...
1
vote
3answers
67 views
fill an ordered series in column A only if its corresponding cell in B having any text
I want that if in column B having text then in corresponding cell of column A number should be filled according to previous entry or so called series.
I tried this for A1 =IF(B1=="","",1) but it is ...
0
votes
0answers
47 views
Change Column Values Based on an Input
Column A contains Products created from a drop down list. I want Column B to equal product benefit statement for each product, Column C to equal full price of the Product and Column D to equal ...
0
votes
2answers
62 views
How can I easily combine multiple Excel columns to a single columns?
I have a dataset which has a seven possible conditions based on data in other columns. I had to split my formulae for those conditions across seven different columns. I would like to combine all seven ...
1
vote
2answers
119 views
Passing Range to ABS function in VBA
I would like to perform a function which looks like this
Selection.FormulaArray = "=GEOMEAN(ABS((C646:E646)))"
Since my range keeps varying I would like to pass the range as a variable which looks ...
0
votes
2answers
30 views
Time Difference In Formatted Text
How can i find the time difference from like "9-10:30" formatted cell.What is the formula to find the "1.5" hour or "90" minutes from that cell.
0
votes
1answer
111 views
How to copy the value and format of a cell into another cell on Excel 2010? [duplicate]
I have been looking all over the internet for this question, but it has not been answered by anyone.
I am, on Excel 2010, trying to copy (not copy and paste) the format AND value of a cell, onto ...
1
vote
1answer
32 views
Just get date stamp once in excel
To datestamp a cell in Excel I use the forumula : =NOW()
The problem is that as I datestamp new cells all other cells which contain the formula NOW() are updated with the new datestamp. Is there a ...
0
votes
2answers
67 views
How to count number of rows excluding rows with duplicate data
I have a table in which the column "Doc. No." has duplicate info as below; please advise on which formula I should use to count the number of unique Doc. that I have (as reference to the below table ...
0
votes
2answers
82 views
How can I refer to a cell that is a constant number of cells away from a particular cell?
I have two identical tables on a sheet: one that contains text for reference, and a second that contains values.
I am trying to sum up some cells in the second table but using the addresses of ...
-1
votes
1answer
58 views
Copy text from “joined cell” to another sheet's smaller joined cell
Normally, text appears in the cell when it contains a reference to another (=Sheet1!A1 will show the contents of sheet A1) However, if both target and destination are joined cells, and the target is ...
2
votes
1answer
76 views
Find last row in sorted table for matched value
I have a table in Excel, which for simplicity sake we will define as:
Order# | Date | Price |
123 | 1-1-2013 | 100 |
135 | 1-2-2013 | 150 |
145 | 2-4-2013 | 100 |
145 | 2-5-2013 ...
1
vote
3answers
124 views
Counting frequency of comma-delimited tags in Excel
I have an excel spreadsheet with a list of seminars and classes that students have taken that (when abridged) looks like:
(A)___ClassName________|(B)_________Tags____________|
Astrobiology ...
0
votes
1answer
43 views
VLOOKUP in Excel with IF statements?
I'm not sure whether IF statements are actually needed in this problem;
however, I'm totally stumped.
In the first worksheet I have a column for "years employed" (Column D6:D107) ranging from 0.1 to ...
