Questions about using in-cell functions/formulas in spreadsheet software.
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
2answers
23 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 ...
7
votes
11answers
33k views
How to get the current column name in Excel?
What is the function to get the current line number and the current column name for a cell in Excel?
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
1answer
26 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: ...
0
votes
0answers
36 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 ...
-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 ...
1
vote
1answer
38 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 ...
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 ...
2
votes
1answer
4k views
Make COUNTIFS count duplicate values only once
I've got all this data on test runs:
controller start end start ts end ts
1 1:13 1:15 1:00 1:30
1 2:08 2:25 2:00 2:30
4 4:02 4:16 4:00 ...
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 ...
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
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
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
36 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
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
52 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 ...
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
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 ...
1
vote
3answers
3k views
Find a non-case-sensitive text string within a range of cells
I've got a bit of a problem to solve in Excel, and I'm not quite sure how to go about doing it. I've done a few searches online, and haven't really found any formulas that seem to be useful. Here's ...
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 ...
3
votes
1answer
652 views
Using the INDIRECT() function with table formats [closed]
My purpose is to create data validation dropdown lists in Excel that reference whole columns in a table-formatted set of data. Ultimately, I hope to create a dynamically changing set of data by which ...
2
votes
1answer
2k views
How do I extract excel data from multiple worksheets and put into one sheet?
In a workbook I have 7 sheets(Totals and then Mon to Sat),I want to extract rows which have the word "CHEQ" in its cell (this is a dropdown list with two options-CHEQ/PAID)from all sheets. On my front ...
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 ...
7
votes
3answers
19k views
Can you do Regular Expressions in Excel without VBScript?
I'm looking for an Excel function that I can put in a cell to do a regex search of the contents of another cell. Is there anything available to do this? I'd prefer not to have to add VB Script to ...
2
votes
1answer
2k views
Lookups targeting merged cells - only returning value for first row
I have a master worksheet which contains data that I wish to link to another 'summary' sheet using a lookup.
However, some of the cells whose data I wish to include in the summary sheet are merged ...
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 ...
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 ...
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 ...
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
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 ...
6
votes
5answers
42k views
Excel: how to count number of distinct values in a range?
I've got a large table that is already organized using filters etc. I'd like to add a summary underneath certain columns that contain the number of distinct values in that column.
There's no ...
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
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
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 ...
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 ...
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 ...
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
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
56 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 ...
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:
...
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:
...
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 ...
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 ...
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 ...
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 ...
-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 ...
