I have a set of 5,800+ data points between 0 and 1 that I would like to multiply together. When I use PRODUCT for the whole set, the formula returns 0. However, I can use a smaller subset of the data to return a very small number. I'm curious if Excel has a closest-number-to-0 or number-of-cells-for-PRODUCT limitation. Is there another way to perform this calculation?
View 4 Replies
I've attached a file that is the result of a product forecast.
What I'd like to do is minutely adjust the data in B4:K12 so that the totals shown in blue colour on Row 15 and Column O are respected.
Book1.xlsx
we use # of days per disbursement as a performance measure to ensure that we are providing out grantees with the appropriate amount of service. I keep a tracking chart that I manage with overseas partners that use these dates to prioritize the 30+ grantees in their portfolio at any given time. It would be great if this # of days to disbursement #1, disbursement #2, etc could automatically pull to show them who they have neglected.
"Sheet 1" = Overview sheet to see general information (where I'm trying to pull to)
"Sheet 2" = table to track information as the disbursements or other actions are processed per grant
Column A (on both sheets) gives the grant reference
Column B (data entry sheet) gives the date the payment was sent
When I do =SMALL(('Sheet2'!B:B),2), I get the 2nd smallest in the whole sheet, but then when I try to make an IF function to tie it to the specific grant...
=IF('Sheet2'!A:A,A2,SMALL(('Sheet2'!B:B),2)) --> this gives me a 1905 date
I've tried a bunch of different formulas and tried reformatting the dates... but I'm having very little success...
I have a problem and I can't figure out how to do this, I have tried using both macros and functions (INDEX for example). The problem is as follows, I have a dataset of 27 worksheets, each worksheet has between 30k and 60k of rows and 25 columns. They are set up as follows:
------------------1990 1991 1992 etc.
Firm_A-Variable_1
Firm_A-Variable_2
|
|
|
Firm_B-Variable_1
Firm_B-Variable_2
|
|
|
etc.
Now what I am looking for is an easy way to transpose the data, I would like to have it looked as follows:
-------------Variable_1 Variable_2 Variable_3
Firm_A 1990
Firm_A 1991
Firm_A 1992
|
|
|- ---- 2012
Firm_B 1990
Firm_B 1991
Firm_B 1992
|
|
| ------2012
etc.
It is basically impossible to do this by hand, each of the 27 worksheet has between the 3000 and 6000 firms and each firm has 57 variables (these are identical for all firms). Also the the firm names and the variable names are in the same column, these should be seperated as well (they are connected with a hyphen).
I am looking to calculate variance across a large data set and would like to know if a macro is possible to calculate for a specific unique cell ID. East, Central, or West and calculate variance across that region.
For instance, in my data set if I have something similar to below. How would I calculate variance in the different regions? Is it possible to automate this process? Also could the Analysis ToolPAk be used instead or in conjunction?
OrderDate
Region
Rep
Item
[Code]....
I have the following structure in values found within a large dataset
6A-6200 A
6A-6156-A
I used =SUBSTITUTE(CELL," ","") to remove the space in the first value: Works fine
I am trying to remove the 2nd occurence of the - in the second value: Desired Result 6A-6156A
And how I may be able to complete both within one formula.
I am accustomed to using filters to find a lot of my information in large datasets.
However, now I am trying to use formulas to return specific values. For simplicity's sake, I have included a sample below with a couple types of scenarios I am looking to solve through the use of formulas. Would this involve sub-arrays perhaps?
sabinfire1.xlsx
Excel questions.docx
We collect loan payments for 36 months from customers.
Column A lists 1000+ customers.
Column J lists the date we received payment 1 ... Column Q lists the amount we received on payment 1.
Column R lists the date we received payment 2 ... Column Y lists the amount we received on payment 2.
Column Z lists the date we received payment 3 ... Column AG lists the amount we received on payment 3.
This repeats for all 36 payments.
New customers are loaded in each month, so be aware that Column J, Column R, Column Z (and so on) have dates from 2011 and 2012 and 2013.
We'd like to create a list of all customers that have not made a payment for the current month as of a certain day (say the 12th). So this month, on January 12th, we'd like to search our data for all customers that don't have a payment listed between January 1st - January 12th.
I have a large dataset. under the heading Theme there are blocks of text, ie not single words. i have created a simplified version below
Sample table:
StateThemeVictoriatradeNew South Walestrade, ownershipNew South WalestradeVictoriaownership,
test textVictoriatest text, tradeVictoriatrade, ownershipVictoriaownership, trade
I want to count, for example the number of times the words 'ownership' OR 'trade' appear for Victoria.
The formula i am using is doubling up on the last 2 entries.
This is what I have:
=SUM(COUNTIFS(A2:A8,{"Victoria"},B2:B8, {"*ownership*";"*trade*"}))
I have a large itemised call bill that i need to do some regular analysis on and wondered if I could automate most of it.
In column C is a list of mobile numbers, in column F the numbers they called (this is an itemised bill so each line represents one call, meaning each number has multiple rows) finally in column K is the cost of each call.
I want the macro to look through column F (number called) and if there are less than 5 instances of that number that are under 0.30 each in cost to be deleted.
Example: if in column F the number 07500 100100 appeared once with a cost of 0.29 I want it deleted but if it appears 6 times with an accumulated cost of 3.50 i.e. more than 0.30 per call averaged out, then i want it to remain on the sheet
I have the following two bar charts. (see links below). I would like to overlay both these bar charts together and obtain the chart shown in link 3.
For example, at 4.4 GHz and 1.8m antenna, two values (downtime/year) are possible 15 min or 557min. This is represented in the third figure Since the first chart contains small values and the second chart contains large values for the x-axis, will I able to change this to log scale for ease of analysis?
[URL]
The code below works fine on smaller data sets. I tried it on a data set of over 165000 records and it gives me the error:
"Runtime Error '6': Overflow"
Clicking debug highlights this line:
rowCount = .DataBodyRange.rows.Count
Code:
Sub Fixtable()
Dim lo As Excel.ListObject
Dim loRow As Excel.ListRow
[Code]....
I've been unsuccessful in trying to write a formula that retrieves a single result based on two criteria (from a large set of data on a separate worksheet). I've tried various INDEX MATCH combinations but no luck.
A
B
C
D
E
1
DATE
TEAM
PITCHERS
RESULT
2
4/1
nyy
Sabathia
???????
[Code] ........
So this is a very simplified version of my real data set which is about 20 times this size. The first worksheet is where I want to store my retrieved results (lets say D2 for example). I want to retrieve data from the second worksheet that matches two criteria (exactly) originating from my first worksheet. The two criteria to be matched from the first worksheet are, for example, A1 (sabathia) and F2 (the date 4/8). The complicated part is the desired result should be from the corresponding K/9 column in the second sheet, which in this case (based on sabathia and 4/8 criteria) is I2 (result would be 3). It's complicated since I can't just tell the formula to look down a specific K/9 column, I need to search ALL the K/9 columns in the sheet (of which there are many). Is this even possible with some sort of nested INDEX MATCH? Any possibilities outside of VBA programming, or is that the only way?
I have a table that can at any point have from a couple hundred up to a couple thousand rows. Within this table lies a column entitled " Offer ". I want to plot the figures in the offer column as a frequency distribution chart.
I plan to do this by listing the x-values (Offer figures), and then using count if formulas to calculate the frequency of that x-value. Then using a simple clustsered column chart to create the visualisation of the frequency distribution.
My question is.... in my large data set, is there any way to get VBA to insert a list of the range of figures in the Offer Column, ? I can figure out how to copy down the countif formula to populate the corresponding frequency column, but how can I have some VBA to dynamically adjust my x-values (offer figures)?
For example... say in the first data set I have
Offer, Frequency
1 10
2 20
3 25
4 20
5 15
that's fine if I make the chart, but what if the data set changes, I want VBA to give me a list of all the offer values, and then I can write some code to insert and copy down along the frequency column the countif formula.
The ultimate goal is to have a frequency chart that will be synced to the self-updating dataset.
I'm trying to count how many production orders i have per week. However, there are duplicated production orders per week. I only want to count how many unique orders there are for each week. I only see the ability to "Count", which counts my duplicates as well so it over inflates my true quantity.
View 3 Replies View RelatedI am trying to use a PRODUCT formula to return the product of a list of numbers in a column, between 2 specified dates. The spreadsheet is arranged as follows:
Column BA list of dates
Column C & DNumerical data not used in any calculations
Column EThe numbers to multiply together
Cell A1Start date to be used in the PRODUCTformula
Cell A2End date to be used in the PRODUCT formula
The formula I have to multiply the data in column E, subject to the start and end dates in Column A is:
{=PRODUCT(IF(B15:B1000>A1,IF(B15:B1000<=A2,INDEX(B15:E1000,0,4))))}
This returns zero though works appropriately if I replace “PRODUCT” with a “SUM”.
I'm working on a order spreadsheet system, and I have one sheet called Product Details, where the product name, list price and product code are found, these link to the Sales order page, and I need them so that they can be added to.
So far, I have created a dynamic named range for the Product name on the Product details sheet, and linked the the range via a list validation on the Sales order sheet.
The drop down list displays the products and can be added to by typing new product names on the other sheet.
What I need now is that when a product on the sales order page is selected, it draws the the list price and product code data automatically from the sheet, I tried using a normal vlookup, but I couldn't get it to work. I also need the list price and product code columns to be 'dynamic' so new values can be added further along.
I would like to have a Macro to go from the first tab called "Start" and end up with the second tab "End" automatically. BAsically I need to take the product on each line under Tab Start and reproduce it for sizes 35-41 always ending with "-(size)". Then this new Product with Size needs to be multiplied one below the other for as many times as I have pictures (number shown under column B of the tab Start). Next to this value in column B of tab End I'd like the same name listed again but with the number 1, then 2, then 3, etc. as needed and the extension .jpg.
View 4 Replies View Relatedi need to look up a product based on the number and the qty. see the example attached.
On tab "Printer - S" 1 need to lokup the costs based on the product code 8 and the qty 100. How do i do this and make it adjustable for any range of priduct codes and qtys. Again, becuase this will be used on a Mac Office, i cannot use macros.
In the first sheet I have two columns, one for the product_id and one for the name of the product. So the Sheet1 is like a small database. The second sheet is for the orders.What I want is when I type the product id in the A column of the orders sheet(Sheet2) to auto insert the product name in the B column so i dont have to write it every time.
View 2 Replies View RelatedI am having trouble finding an efficient way to expand a set of data that I have by adding another column to it.
Look at attachment : help.xlsx‎
I can do it manually but I have 5000+ rows of data that I need to selectively expand do accommodate the data from the new data set.
I'm trying to create a supply order form. Is it possible to enter a product name in one cell and have the price for that product automatically appear in another cell? Is there a formula that I can use to make this happen?
View 2 Replies View RelatedI've got a 80 Mb CSV file and would like to open and work with it. Too many lines (90000 or so).
Is there a way to split ( ) this file so I can open two files instead?
Trying to add a vlookup to a data set and cannot remember how to do.....
I have a table with State and Suburbs. I want to look at both these within another sheet to ensure the post code is correct......
In the attached I want to show in Sheet1 Column D the post code from Sheet2. As there may be suburbs with the same name within Sheet1 I need to ensure the correct Postcode for the suburb and the state.
(Checking manually entered data....)
Exceltest.xlsx
I have a grid that I need to lookup and return the project hours for each system from the data set. The grid has the projects listed in the rows and the systems listed across the columns. The data set has a list of each projects systems hours. I am having problems with the formula when the data set has multiple records (multiple systems) for each project. It seems very simple, but I just can't get my arms around it. Please see the attached for example.
View 7 Replies View RelatedI was recently helped very much by the forum moderator and code from D_Rennie in a file to transpose about 25,000 rows into a few organized columns. The code worked very well. I have a slightly new twist on the problem. I have uploaded a different file with a different data structure in the columns but the same need for the data to be transposed to the columns under the same headings. This VBA worked very well before, but I can't seem to re-use it for this new dataset.
View 8 Replies View RelatedI have a spreadsheet which has general usage and summary data at the top for ease of use. Begining at Row 15 the data is a standard table which includes columns for the following.
Cust Name | Status | Loan Amt | etc.
I have created a macro to make it easier to add new data rows at the top of the data already in the file (this makes it easier to view with the newest data at the top). I would like to create a conditional sum of the data in "Loan Amt" if the value in "Status" ="Active". I had no problem achieving this with the conditional sum tool but the problem is that the range changes whenever a new row is added to the top of the list. Unfortunately the range in the conditional sum adjusts as the old data shifts down for the new row and the sum does not contain the new row data.
I have to do a search on a dataset (about 3000 entries) and part of the exercise is to look in some project titles if there is a country or other key words included.
I have look on the web and unable to find a formula that works. Basically what I want to do is to have a formula a looks in the column of titles for a list of keywords on another sheet in the same book.
a macro (that i will link to a control button) that will autofilter a data set. The problem i have is the macro i wrote below, It might not be the best looking macro in the world, but it would work for my purposes if i can get the part that does the autofiltering to be more dynamic. meaning, instead of a hard coding "Retail" in the macro, id like it to reference a cell so that the user can type whatever they want, then click the button and it will filter based on what they type in.
this is what i have
Sub Filter_Button()
With Sheet2
AutoFilterMode = False
Range("A6:M6").AutoFilter
Range("A6:M6").AutoFilter Field:=2, Criteria1:="Retail"
End With
End Sub
I want to create a simple data base of times but it must be as I have in col A .
I am not able to change from using these : .
I have tried scrolling down but when I get to .99 it rolls over to .100
My data starts at 44.00 seconds and wanting to go up to 9:00.0 minutes .
GOOD Â A1TIME259.97359.98459.9951:00.0061:00.0171:00.0281:00.0391:00.0410
All the way up to 1:00.97111:00.97121:00.98131:00.9914
Then next is 1:01.0015Then next is 1:01.01 16Then next is 1:01.0217
Then next is 1:01.0318Then next is 1:01.0419Then next is 1:01.0520Â 21
Wanting to go all the way to 229:00.0 and end there
