Questions about using in-cell functions/formulas in spreadsheet software.
1
vote
0answers
14 views
Update Excel table with newest available information and put old information on seperate sheet
I have combined several worksheets into one master spreadsheet. Unfortunately, each spreadsheet parts of the original spreadsheets had complete information. I need to clean up my information. What I ...
3
votes
2answers
24 views
Get first and last value from range in a spreadsheet using other column's data for filter criteria
I have data in my spreadsheet in a worksheet like this:
Where the data goes in this format
[ID] | [Date] | [Hour] |
And I have another worksheet where I want the data to be like this:
Where I ...
1
vote
4answers
51 views
How can I use drag-down to copy values horizontally?
I am dealing with one table full of digits and I want to use drag-down function to take digits horizontally every two or three columns if there is digits in the cell. Here is part of my table:
A ...
0
votes
4answers
49 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, ...
0
votes
0answers
37 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
29 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
18 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
14 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
40 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
34 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
2answers
24 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
39 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
31 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 ...
0
votes
3answers
53 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
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
34 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
57 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
27 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
2answers
25 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
123 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
47 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
68 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
47 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
57 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
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
277 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 ...
0
votes
1answer
65 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
71 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
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
122 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
129 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
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 ...
-1
votes
2answers
85 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 ...
