Overlay Two Bar Charts Together / Large Dataset For X Axis
Feb 19, 2013
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]
View 1 Replies
ADVERTISEMENT
Jan 6, 2014
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
View Related
Mar 29, 2013
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...
View 12 Replies
View Related
Apr 3, 2013
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).
View 1 Replies
View Related
Nov 25, 2013
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]....
View 4 Replies
View Related
Jun 16, 2014
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.
View 3 Replies
View Related
Jul 12, 2014
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
View 5 Replies
View Related
Jan 2, 2014
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.
View 14 Replies
View Related
Nov 3, 2011
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*"}))
View 9 Replies
View Related
Feb 9, 2012
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
View 3 Replies
View Related
Jan 29, 2014
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]....
View 4 Replies
View Related
Jan 17, 2013
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?
View 8 Replies
View Related
Dec 16, 2013
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.
View 1 Replies
View Related
Oct 15, 2012
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 Related
Jan 26, 2009
I recently started keeping a new chart to track several different stocking measures. After running the data this morning, I noticed my horizontal axis had blank position holders for data over the weekend. How would I set the chart so that if there is no data in my data range, it doesn't put a place holder in the axis? Sample attached.
View 4 Replies
View Related
Jan 24, 2008
I am trying to create a spreadsheet with a chart to show gas consumption in our house.
I have sufficient data to produce the standard "S" curve for a 12 month period and would like to be able to have one chart with the standard "S" curve on it plus a curve of progress in the year to date of gas used so that an instant comparison between planned and actual usage can be made.
The problem is that the 2 curves require a different X axis. The standard "S" curve has an X axis that is 12 months long whereas the current one is as long as it is from the start of the year being measured. Both curves will have data inputs on different dates in the year.
View 9 Replies
View Related
Apr 1, 2009
Is there a way to limit the range of values on the Y-Axis on the lower end? Like I don't wish the Y-axis to begin from 0 but say 500. Is that possible?
I have a set of data values ranging from 650 - 850, but the Excel graph however looks too fine because the Y-axis begins from 0.
View 6 Replies
View Related
Sep 28, 2011
I would like to know how can I plot my x axis of a chart with the maximum and minimum value that I have in this variable. Note that my variable is no going from the lower value to the highes one, it is random. So what I did is to use the autoscale of the chart, but it is not working as I would like. Any solution?
The code that I use is:
Code:
' TO GET THE INITIAL and FINAL VALUE and the STEP
Worksheets(wsheet(27)).Activate
ActiveSheet.ChartObjects(chartName(11)).Activate
ActiveChart.Axes(xlCategory).MinimumScaleIsAuto = True
ActiveChart.Axes(xlCategory).MaximumScaleIsAuto = True
ActiveChart.Axes(xlCategory).MajorUnitIsAuto = True
xScaleMin = ActiveChart.Axes(xlCategory).MinimumScale
xScaleMax = ActiveChart.Axes(xlCategory).MaximumScale
xScaleStep = ActiveChart.Axes(xlCategory).MajorUnit
'After I use them (xScaleMin,xScaleMax,xScaleStep) for several charts, in this way:
ActiveSheet.ChartObjects(chartName(11)).Activate
ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Text =VarName
ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Select
ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Font.Size = 11
ActiveChart.Axes(xlCategory).MinimumScale = xScaleMin
ActiveChart.Axes(xlCategory).MaximumScale = xScaleMax
ActiveChart.Axes(xlCategory).MajorUnit =xScaleStep
' but if my data was between 50 and 250 it is plotting between 0 and 300
View 2 Replies
View Related
Sep 10, 2012
I have a combination column and line chart with the line series on the secondary axis. Is there a way that I can tell excel to scale the secondary axis so as the line always appears above the columns? As it is it appears above in most instances but sometimes appears below the columns.
View 2 Replies
View Related
Oct 4, 2013
There is POLICY statement that determine an what ACTION should be applied to a paticular building given a score for "LIFE" & "RISK" which results in varing policies A-D. (The Policy intervention points differs slightly for varing types of building. This excel sheet works just fine, and provides the correct guidance for our engineers by suggesting Policy A-D on input of tbuilding type and LIfe/Risk score. However the Policy is a guide, and they do need to apply a level of judgement with the data close to intervention points.
My Question,
I would like to provide further graphical representation to aid decision making. I have a graph plots the X Axis ( LIFE) 0-100, and y Axis (risk) 0-5 and show the , but what i need to be able to do is dynamically adjust the position where the x&y axis cross, so showing visually the intervention point when different building types are selected.
View 1 Replies
View Related
Jul 28, 2009
Produce a chart (not a problem) that can be updated should the length of the X and Y data change?
This may not even need to be a macro, if it is possible to have a chart whose X and Y axis updates depending on the contents of a column?
If you search through the history of even this forum you will see quite a lot of people are looking for this solution.
View 9 Replies
View Related
Oct 23, 2007
The first data point of my chart appears cutoff along the y axis
I formatted the x-axis by clearing the check box "Value (Y) axis crosses at maximum category"
If I add the tick mark back in then my first data point does not begin at the origin.
Screen Shot attached of both cases
View 5 Replies
View Related
Jul 7, 2014
I have a dynamic column chart that may contain positive or negative (or both) values. The columns contain Data Labels positioned 'Outside End'.
I want to avoid the Data Labels (for the negative values) overlapping the x axis. Is there a way to do this via VBA?
I know how to update the chart axis via vba linked to cell values, but I can't figure out a formula I can use to calculate the minimum which takes into account the distance needed to avoid the overlapping problem.
View 1 Replies
View Related
Feb 8, 2009
Two questions:
1) How do make values on x-axis as words? E.g. apples, oranges, etc.
2) Is it possible to have the numbers on x-axis in descending order? If I can figure out 1), I can do this as well, but it would be nice to know if it is possible to have the numbers on x-axis in descending order.
View 8 Replies
View Related
Jun 12, 2008
I need some VBA to copy data from one table to overlay another. The source table's columns need to be overlayed on the destination column where the numbers in the first row of each table are a match. I have attached a sample worksheet.
View 5 Replies
View Related
Mar 13, 2014
I am doing a budget sheet in Excel for the different expenses accrued by the different classes we offer in our university department (honoraria for guest lectures, tickets to shows or museums, etc.). The sheet is formatted as a table and I have the Total Row enabled at the bottom.
However, we have a reserved budget for different things. For instance, Fall 2013 museum tickets has, say, a budget of $1,000. Fall 2013 honoraria might have a budget of $2,000. I want to be able to filter to see only Fall 2013 honoraria: the totals row will reflect this, but I also want it to automatically tell me how much money is left in that budget (i.e. I want it to know to subtract $2,000 from the totals figure because that is the budget for Fall 2013 honoraria).
It gets a little more complicated because we also have a budget for each class too. One class might have both honoraria for guest lectures and field trips to a museum, and we might say "We don't want this class to spend more than $500." Again, I want to filter to see just this class, and have the Totals row also show me how much money is remaining for this class.
View 4 Replies
View Related
Oct 22, 2008
Basically Im crap at excel and need to start learning the in's and outs more but I have drafted up a double sided spreadsheet that I would like to run a series of prints 'inhouse'
I would like it if I could insert an overlay serial number on the sheet that would enable me to carry out future prints adding an incremental number to the serial number EG printing off 100 prints where the serial number runs from
CODA/BL1_00001
CODA/BL1_00100
IS this possible using general tool tricks of the day ie VB, macros or does an add on program exist that will enable this to be generated on a fly between the program and the printer.
View 13 Replies
View Related
Aug 28, 2002
I would like to create a histogram from experimental data with the normal distribution curve overlaying the histogram.
View 7 Replies
View Related
Feb 28, 2014
I 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.
View 4 Replies
View Related
Mar 1, 2014
I have a spreadsheet with golfers handicaps, golfers names down the left in column A in rows 3-35. Row 2 has the event numbers titles 1 to 18 (18 events in a year). i need a graph that has the person as the vert axis and the horizontal axis needs to be the event numbers, so i can read down for the person and across to see how there handicaps change from each different event. i have attached the data below.
View 1 Replies
View Related
