Tagged Questions
0
votes
4answers
34 views
How to add/subtract months from given date in Excel
Here is my scenario:
I have to populate list with dates and some metadata of upcoming events for the following year in my Excel application. Some of the events are occurring on the same day each ...
1
vote
1answer
45 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, ...
-1
votes
0answers
17 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
13 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
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
30 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
3answers
29 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
41 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 ...
-1
votes
0answers
47 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
32 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
55 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
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 ...
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
122 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
35 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
67 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.
...
1
vote
2answers
45 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
64 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
262 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
41 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
52 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 ...
1
vote
3answers
70 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
2answers
64 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
120 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.
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
68 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 ...
2
votes
1answer
78 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
5answers
74 views
Excel - Sum of values in a column given the order type available in another column
I have excel 2010 and I am trying to do the following (the table below has been done manually)
Customer Order type Revenue Sum
001 Pears 50
...
0
votes
2answers
63 views
Auto orderlist Excel 2010
I'm using Excel 2010 to create a inventory of my shop.
Now I would like it to automatically create a order list in the second sheet.
My first sheet look like this.
Item: Stock: Min. Max.
...
0
votes
2answers
69 views
Copy multiple cells from one to multiple worksheets
I have a workbook in Excel 2007 with 52 worksheet each representing a week of the year, each worksheet is laid out the same with 16 rows and 26 columns to give me 14 personnel and 24 hours with other ...
0
votes
1answer
111 views
How can I get the hex value of an 8 row column with depending on background color?
I have a spreadsheet that I'm using to help visualize a character bit map for each ASCII character. For a 5x7 font, I use 5 cells wide (columns) and 7 cells tall (rows). I then marked the background ...
2
votes
1answer
197 views
Compare multiple columns (more than 2)
Okay, so let's say I have a chart with N columns of data. What I want to do is compare every column to every other column, and get a count of how many values are equal in both columns. I know I can ...
0
votes
1answer
68 views
creating multiple plot per level of factor (trillis graph) in excel
Is there way to automate the process to make graphs like the following (also known as trallis graph) in excel ? I know there are some R interface with Excel (some of them need to be purchased), but I ...
1
vote
1answer
75 views
How to find last and first value meeting criteria in a series of values?
This is a sample of my input:
Then, I have midnights in a consequent days and I would like to achieve this result automatically:
To put it in words: I have datetimes in one column and I would ...
0
votes
1answer
137 views
Excel Formula for filtering data from one sheet to another
I have table that have the following columns (Agent Name, Client, Policy #, Premium) with the name of agents repeating several times since they have multiple clients...
I don't want to give all the ...
-2
votes
2answers
55 views
MS Excel IF THEN formula
I am trying to write a formula such that, if T5 is 1, and AB5 is 1, then LOW;
OR if T5 is 1, and AB5 is 2, then HIGH;
OR if T5 is 1, and AB5 is 3, then EMERGENCY
etc., for the whole chart..... T5 is ...
0
votes
2answers
94 views
Modify formula to work with conditional values
I am currently using the below formula to calculate enablement costs for meters
=SUM(SUMIF($D74,{"New Business"}),$U74*5500)
This worked fine as the fee of $5500 was standard, however I now need to ...
0
votes
2answers
104 views
How can I turn a Calculated Column back into a data column in an Excel Table?
I have a large Excel Table (44 columns, 1000+ rows), but one of the columns has been accidentally turned into a Calculated Column (See Here). The result being that now when the users try to add new ...
0
votes
3answers
102 views
change link cell formula across sheets
I have a workbook named "stu", it has 1 to 30 sheets. In cell H4 I have linked a formula:
=[sub.xlsx]Sheet1!$B$2
in sheet1: cell H4 the formula in cell H4 should be =[sub.xlsx]Sheet1!$B$2
in ...
0
votes
3answers
203 views
show cell value if another cell is true, else go to next row
It's my table in Sheet1:
A B C D
------------------------------------------
1 220 John Good
2 210 Jack Bad
3 300 ...
0
votes
1answer
55 views
Ms Excel Formulas for Date and Day
I need help.
I have written a specific date, for eg 13/03/2013 in A1.
Now I want its corresponding weekday in A2 (i.e. 13/03/2013 (Wednesday).
In A3 I want it to show two days ahead (i.e. 15/03/2013).
...
