Charting Out All Possible Combination Of Multiple Ranges
Jul 10, 2014
I have developed an evaluation algorithm for residential mortgages, and am having difficulty testing. I am trying to ensure that my scoring model makes sense from every possible scenario. The grading model takes into account multiple factors that most financial institutions would find important when assessing a potential borrower for a loan. Examples of these factors could be an individual's credit score (ranging from 0 - 800), the type of dwelling (multiple options to choose from), etc. In total there are about 20 different factors, each with a minimum of 5 possible options to choose from.
I am trying to create a spreadsheet that lists out all possible combinations of the multiple factors, and trying to input it by hand . To give an example:
Credit Score
Property Dwelling Type
Property Value
100
Townhouse
$350,000
[Code] .....
View 6 Replies
ADVERTISEMENT
Mar 10, 2014
I have a log where I'm recording daily readings of a pipette. Each day three readings are taken and I have each stored as a dynamic range so that I can monitor how in control my process is. However, when I graph it, I would like it to be one continuous line graph (Three readings from day one followed by three readings from day two, etc.). I'm having difficulty doing any sort of combination.
View 7 Replies
View Related
Dec 5, 2012
I am having an issue with dynamic charting using named ranges in excel. I quite often create dynamic charts using IF and OFFSET formulas to check conditions in order to create charts of data based on user inputs.
The issue I am having is with linking multiple tables of data in a single named range. In order to make this spreadsheet easy to maintain I am trying to take 3 separate databases and link the spreadsheets through named ranges. The formula I have written is as follows:
=VLOOKUP(VALUE(LEFT(OFFSET(Processed!$B$1,1,0,COUNT(Processed!$C:$C),1),6)),Raw!$F:$O,10,FALSE)
For context this is;
=VLOOKUP(VALUE(LEFT(OFFSET("Serial number I am referencing",1,0,"Qty of rows containing data",1),6)),"Value I want to return from separate table",10,FALSE)
[The formatting of the serial number between the two sheets is slightly different but they share the first 6 digits (hence the wrapped VALUE and LEFT formulas).]
This formula is working perfectly except that it doesn't store the entire data table in a single cell, it only returns a single value. If I enter the formula into a cell and drag it down it returns all the information correctly, however if I create a named range using the formula and try to chart it only the first value returned is charted.
Any way to modify the formula to store the entire data array in the single cell value so it will chart when used as a named range?
View 1 Replies
View Related
Mar 25, 2014
So I was looking for reassurance or validation more than anything. From what I can tell you can in order to build a chart that is dynamic throughout a range, you use the offset and count or counta function - 1. That part isn't a problem. My question is once you created that for your charts do you just normally plot your chart range or do you have to reference the named range directly into the chart range?
View 2 Replies
View Related
Jan 18, 2009
In in Excel/VBA, I have been making OWC charts on a form. My aim is to chart categories (x axis) against values (y axis), showing several values in each category as markers. In addition, I have been calculating the mean for all values in each category, and plotting this as a line across the categories. I first set up the X axis at the charts level:
Set oChart = chtspcXIC.Charts.Add
With oChart
.HasLegend = True
.HasTitle = True
.Title.Caption = "Used fractions per timepoint"
.Type = chChartTypeLineMarkers
.SetData chDimCategories, chDataLiteral, XICtmpts
End With
Next, for the individual points, I made two corresponding arrays, one of categories and the other of values and attempted to send these to a new a seriescollection:
Set oSeries = oChart.SeriesCollection.Add
With oSeries..............
View 2 Replies
View Related
May 15, 2014
I'm having difficulty figuring out how to line graph two separate dates. I also want to show a trend.
I was first thinking I would need two graphs, but is there a way to merge into one?
View 1 Replies
View Related
Jul 28, 2008
I have two sets of data that I would like to be compared over a 4 day period.
One set (Mill data) has an associated date and time (29-Jun-08 00:42:02 etc.), and has a value generated every 4 to 6 minutes.
The second set (Dump Grades) has an associated time only (13:24 etc.), and has values generated at random time intervals throughout each day.
The first issue is that I must assign a date to the Dump Grades data, which I just can't seem to do.
The second problem is that I don't know how to get all of these data points graphed on the same graph, as the date and time thing is throwing me for some reason.
My data right now is arranged in columns on separate sheets, organized by dates.
View 9 Replies
View Related
Jan 12, 2009
I have several dynamic charts using named ranges, but I am charting 1 series of data. What about when I have multiple data sets or series and do not want to use PivotTables? Do I really have to make each one a named range? If that's the case, using VBA looks much simpler.
View 2 Replies
View Related
Jan 31, 2013
I have a data set where a1 is the starting cell and it needs to equal cells b9 or b6 and if the cells in the a column match the data in b9 or b6 it should be assigned an 1 if the cell data in column a does not match the data in cells b9 or b6 it should be assigned a 0. I've tried multiple formulas and have not been able to find the right one
View 1 Replies
View Related
Dec 14, 2012
The goal of this is to find the best combination of a captain (out of 5 possible choices), 5 crewmates (out of 25, no repeats), deck items (2 items, can be repeated, 20 total options), ram (1, out of 13), hull (1, 13 choices), and rudder (1, 7 choices)
Each choice in every category has 4 attributes - fighting, morale, seafaring, and speed. The ship parts also have a true/false field called "unlocked" - the false fields obviously can't be chosen.
A mission gives numbers saying what total attributes are needed for a 100% chance of success. If the mission has 2 attributes that are required (say seafaring and fighting, for instance) then it takes whichever chance of success is lowest and counts that as the mission success chance.
Example: A mission requires 2000 fighting and 2000 seafaring - I can reach 2000 fighting but 1500 seafaring, chance of success is 75%. It would be better for me to get a combination that gave me an 87% success rate in both fields.
How do I have excel go through every combination (and is that even plausable? I would imagine that being in the billions of combination choices) and highlight (turn a cell green or something) the combination that has the highest minimum chance of success in all fields?
View 2 Replies
View Related
Jul 12, 2012
I have a formula that I can't get to paste successfully in the forum - it keeps getting cut off?!? ... but I think I can probably simplify my explanation to get the answer I want anyway.
I need to only show the value from AUS!$H$2:$H$17 if the C2 & D2 combination are the same as the AUS!$B$2:AUS!$B$17 & $AUS!$C$2:AUS!$C$17 combination.
View 9 Replies
View Related
Apr 21, 2013
I've got several worksheets that all have the exact same layout that a user will enter unique information in to each worksheet. Then I've got a final worksheet that I want to have a button that the user can click and when they do, it will look to each worksheet and do the exact same process for each worksheet as follows:
It first looks to see if the worksheet is visible. If it is, I want it to copy the range A5 to K5 down until it gets to the last non-blank cell in column C. The first non blank cell that will be referenced will be C7. Then I want it to paste this information into the range A5:K5 on the final sheet named Sheet8 with the same values and keep cell formatting such as width and height, font. If the worksheet is not visible, it skips the sheet.
I want it to do this for each visible worksheet, placing the next visible worksheet info under the previous visible worksheet info. My current code as shown doesn't do that. It requires that something be inSheet8 A6 before it will even paste, then it pastes the info from A5:K5 but it doesn't do just the values nor does it keep the formatting. What I mean about not doing just the values is some of the info that needs to be copied comes from a drop down they can choose from and it copies the actual drop down menu. Also, it seems to copy all of the ranges from each sheet and paste it into just A5:K5 on Sheet8 and overwrites each other instead of pasting Sheet2 just below the information from Sheet1. So the only information shown after the entire process is completed is the information from the last visible sheet.
If Worksheets("Sheet1").Visible = True Then
Sheets("Sheet1").Range(Sheets("Sheet1").Range("A5:K5"),
Sheets("Sheet1").Range("C7").End(xlDown)).Copy
Sheets("Sheet8").Range("A5").End(xlDown)
End If
[Code]...
View 4 Replies
View Related
Jun 16, 2007
I inherited a spreadsheet that had an userform where the user checked off which 'pages' he wanted to print. The Ok button routine used if statements to run a routine for each 'page.' Here's an example of the original code for one page:
Sub Button2_Click()
Sheet7.Activate
Run "HorizontalPrintStuff" 'generic landscape pagesetup
With ActiveSheet.PageSetup 'specific pageset settings
.RightFooter = " Construction Assumptions"
.PrintArea = "CONSTRUCTION" 'the named range to print
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1 'this changes depending upon the page selected
End With
ActiveSheet.PrintOut
End Sub
The problem was it printed each page as a separate print job; and if you print to adobe, you get serveral files, not one file. That and it took a long time to run.
So I tried a different tack. If the checkboxes has true, then the printarea is set to that named range. If there were more than one named range on a sheet to be printed, I consolidated them. I did this with a bunch of if statements - very cumbersome.
Sheet3.Activate
'Sheet3.ResetAllPageBreaks 'disabled due to errors
Run "HorizontalPrintStuff" 'generic landscape pagesetup
With ActiveSheet.PageSetup 'specific pageset settings
.PrintArea = "DEVBGTALL" 'the named range to print
.FitToPagesWide = 4 'this changes depending upon the
.FitToPagesTall = 1
End With
I haven't shown all the code cause it goes on for 12 sheets containing 16 different printareas.
My current muck ups are .....
1) it prints every printarea/named range on a given sheet (I took out all the if statements trying to debug everything.) Is there another conditional argument that allows for multiple 'trues'?
2) the pagebreaks in printarea/named ranges that are multiple pages (like a 48 month schedule) won't stay set. I've tried both VPageBreaks(3).Location:= and .VPageBreaks.Add Before:=
3) the Sheet1.select false argument is always adding a random sheet to the end of the print job. Don't know why.
I can do all this in a recorded macro, just not the selection userform. I've thought about copying to another sheet or hiding columns and rows then printing, but that seems just as cumbersome.
To recap, i want to print out, as one print job, multiple printareas from mulitple sheets, based upon checkbox selection on an userform.
View 6 Replies
View Related
Jul 6, 2007
I need to be able to track changes on selected ranges on multiple sheets, but Excel does not appear to be able to do this. It only appears to allow me to select multiple ranges on the same sheet.
is there a way to track changes on multiple selected ranges on multiple sheets
View 9 Replies
View Related
Jun 15, 2008
In the attached file i have multiple tables for different types of conservatory roofs (16 of them in total). The ranges at the top and side relate to milimeter measurements and the data in the middle relate to the price for that sized conservatory roof. The table works where the two ranges intercept each other. I have a formula to do this for one of the tables only. What i would like is a way of choosing which type of roof to use (i.e. which table to use) and then to be able to input the measurements and the price to be displayed. All of this needs to be done in one query so its as user friendly as possible. i've had is to use a pivot table, i feel it is not possible to use a pivot table to do this sort if thing after research into them, although i am un-familiar in the making of them
View 4 Replies
View Related
Feb 21, 2013
Im trying to make a vlookup so I can say find a record where column A is >4, B is =2, C is 1.
Example - The lookup would find these 2 ranges.
7
2
3
2
6
4
[code]....
View 1 Replies
View Related
Aug 15, 2008
I have an existing chart and I want to update the plot utilizing data that has been calculated and stored in 2 arrays X(N,1) and M(N,1) via my VBA macro. How do I do it?
View 10 Replies
View Related
Jul 1, 2006
I am just reusing some code that works most of the time to reset the seriescollection of specific charts. Sometimes is will just error when I try to set the .values object to my range of values. It's strange because it will work for 10 seriescollections/identicle ranges on the same chart and then error out; and sometimes it will work perfectly.
I've checked:
The chart reference object
The range I'm setting
The code I've copied from
It's all correct. Here's a short sample.
ValRng = Range("AR7:CY7")
Set usechart = ActiveSheet.ChartObjects(1)
With usechart.Chart.SeriesCollection.NewSeries
.Name = "Prime 2000"
.Values = ValRng ' (also tried it this way) "=" & ActiveSheet.Name & "!" & Range("AR7:CY7").Address
End With
The series is created fine, and the name is set properly.
On my new sheet here it won't set ANY values, which is a real problem. If anyone knows what is going on, please let me know.
View 6 Replies
View Related
Mar 26, 2014
I have a large number of files containing thousands of X and Y coordinates. I prepared a macro which loads these X and Y data from all the files into one workbook, and it writes the file names into each row. So, in the end I get a file with this formation:
X Y filename
x1 y1 filename_1
x2 y2 filename_1
x3 y3 filename_1
.
.
.
xi yi filename_2
.
.
xn yn filename_m
It would be great if I could make then an XY Chart which contains the information of "filename" column too. I didn't mean labling, because in the end I'll have hundred thousands of points, so if I added labels to each point it would be impossible to see anything. I would only set that if I move the mouse to any point of the chart, it would show not only the X and Y coordinates, but the related filename too.
Is it possible to set it so in excel?
View 5 Replies
View Related
Dec 2, 2013
produce graphs/charts based on inequalities monitoring information - gender, nationality, age etc. I'm capturing info from several events, one sheet per event. Had a look at COUNTIF but not sure it's what I want and looks like I'd need sub-sheet for each? Capture.PNG
View 7 Replies
View Related
Nov 22, 2011
I am working on a project at work. I need to track our VIP guests. I only have their arrival and departure date.
Is there a way to graph and track the days between so that I can have a total of all arriving and In-house VIP guests on all dates in the range?
Here's a sample of the data.
10/18/201110/19/2011HOERMAN/SCOTT10/17/201110/21/2011SMITH/JOHN M
10/10/201110/13/2011WARNER/AMY10/18/201110/19/2011GERONDALE/RON
10/11/201110/12/2011BUCHANAN/JEFFREY10/17/201110/18/2011HUND/LARRY
10/20/201110/23/2011HADDOCK/CHRISTOPHER10/9/201110/12/2011
BLOOMER/ROBERT10/20/201110/23/2011COLE/ELIZABETH10/7/201110/8/2011IWASA/KENNETH
View 6 Replies
View Related
Sep 4, 2012
I have a line graph which plots the PERCENT_YIELD of units built (UNITS_PASSED / UNITS_BUILT) every week. I want to fill all future week cells with a formula so dragging the formula is not a manual operation every week (my overall goal is to automate a complex spreadsheet and charts as much as possible).
The formula is =IF(Q1080,Q109/Q108,)
Where Q108 is UNITS_BUILT
Where Q109 is UNITS_PASSED
My theory is if units were built (Q108 not equal to zero), plot the ratio. If no units were built (Q108 is zero) I do not want to plot any value on the chart.
The chart plots the correct values for the True conditions; however the chart plots a zero value for the False conditions. How do I not plot a zero for a false condition?
View 2 Replies
View Related
Aug 31, 2007
I have a simple sheet to record and graph my bloodpressure . The list of values is as follows:
col A is always todays date
cols B and C user input - integer
col D is a constant value 120
col E is also a constant value 80
Currently I am manually charting the range A1:Exx each day after I have added a new line for my daily readings. Whilst this works fine, I'd like to automate the charting process as currently if I select a range full of blank cells then my chart displays loads of blank values.
So I could use a Button on the graph which works out how big my cell range is ie; A1:E66 and graphs it for me or alternatively a background macro that does the same after I have entered the C col entry for each day
View 9 Replies
View Related
Nov 9, 2006
i would like to find out about:The different essential building elements for different chart types (like 3DBubble requires XValue, Value, Name and BubbleSize, for example, but what about clusteredColumn and the others?)How to address the building elementsThe optional building elements (changing background color and stuff)
View 2 Replies
View Related
Mar 26, 2008
I have a workbook with Sheet1 and Sheet2. I’ve programmed information from to be calculated and results inserted into sheet2. A new result every Row. I plan to do a graph taking the X-axis and Y-axis values from Columns A and B respectively in Sheet2. What is the VBA code for dynamic updating of graph? Meaning, I want the graph (a curve) that will have points added to the curve, extending it, whenever a new row is added into sheet2 – Columns A (X-axis) and B (Y-axis). The rows are not defined. It can have 100 to 200 rows or more..
View 6 Replies
View Related
Nov 26, 2013
I need to create a simple Talk Time Chart displaying our call center's talk time in 30 min increments.
TimeATT
08:00:00:02:03
08:30:00:04:06
09:00:00:03:56
09:30:00:02:57
10:00:00:04:57
10:30:00:03:29
11:00:00:04:41
[Code] .....
View 1 Replies
View Related
Feb 26, 2014
I have a spreadsheet with 4 columns. The first column is a Date/Time object and the next three columns represent the R, G, and B values of a color. The color values are 8-bit numbers(0-255). What I'd like to do is create an area or bar chart where the x-axis are the dates and the y-axis is the overall brightness of the color (a simple average of R,G,B would suffice). However, I'd ideally like the color of the bar to be displayed by the R, G, and B values from the cells. Is this possible? I've attached a dummy file for reference.
View 6 Replies
View Related
Mar 18, 2014
I have several years worth of monthly data that I would like to keep hidden so it is printer friendly but I also want to have a chart of all that data... Is this possible? Only idea I have is to basically recreate the data sheet but I don't really like that solution since it adds steps to the process and increases the file size...
View 2 Replies
View Related
Mar 26, 2012
I have data I use in a Pareto chart. This data is found somewhere in cell range A18:AF20. I want the chart to adjust itself to only the cell range with data. It may be A18:M18 or perhaps A18:AB18.
Is there any way to set the cell to look at only the cells with data in them?
View 1 Replies
View Related
May 7, 2009
I have three rows that each attempt to pull in data from a range on different worksheets. All but one return nothing but "#N/A" values. The one row returns values from the proper range. (User selections determine which row will have data).
Second, I successfully pull data from the row containing actual information into another row, using the following formula: "=OFFSET(E$36,CHOOSE($Y$1,0,1,2),0)" on a cell-to-cell basis. The value in $Y$1 chooses the row to look at, based upon a dropdown selected on another worksheet.
So I now have a row with =OFFSET(E$36,CHOOSE($Y$1,0,1,2),0),=OFFSET(F$36,CHOOSE($Y$1,0,1,2),0),=OFFSET(G$36,CHOOSE($Y$1,0,1,2),0) and so forth. I can find the last value in the row, but I cannot find a way to extract the address from that, and create a table that will use as a series the last 26 values in the row.
For reference, I use "=LOOKUP(9.99999999999999E+307,E49:FD49)" to determine the last cell with a value (gleaned from a Dueling Excel Youtube Video, - very helpful!).
I hope the above will be clear enough, but if not, I'll be happy to provide additional information / clarification.
View 9 Replies
View Related
