Managing Categories Based On 2 Variables And A Chart.
I have a list of about 700 guys with their names, ages and a coolness factor between 1 and 10. I have different categories that I want them to fit into based on their age and "coolness". The charts at the top of the sheet I've uploaded. Ages are on the top and their coolness rating is on the side. So for instance if somebody is 27 and has a coolness of 6 they would be lumped in the World category. How do I create an IF command that would automatically list what category they're in based on age and coolness?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Count By Categories Within Pivot Chart/Table
I can not get my Pivot Chart to count, sort and categorized my data. I have included a sample file for your viewing. I basically want the data in cells C2:E18 counted, sorted and totaled by categories and locale. I thought the pivot charting was simple, but I'm having quite a challenge with this one. The pivot chart data should reflect as my sample indicates in cells E24:K27
View Replies!
View Related
Managing Cell Selection
I've seen a preference setting somewhere that allow me to specify what happens with the cursor after entering a new value in a cell. such as moving down or moving right or not moving at all? How do I code the cursor to NOT MOVE out of the cell after a new value has been entered?
View Replies!
View Related
Chart With Multiple Variables
I am trying to create a column chart in which i want to incorporate 2 variables. However I don't want to use the line for the second variable. What I would like to do is to add the information of the number of deals a company has done on top of the column. So you see a column which gives you the average size of a deal, and the you should see a number at that column to see the number of deals done with that company. To make this more clear I have attached an example. There are 2 tables of which the information I would like to see in one chart.
View Replies!
View Related
Matching Categories And Summing
The ones we need to focus on are Columns A,C, H, and AG. Daily this sheet ‘Raw’ will be filtered and data sent to sheets 100,200,300. In the Raw sheet we have many rows of data, but when this is transferred to the other sheets we will only see one row of data in each sheet. 1. The Date from Cell(C2) should be copied to Cell ‘A3 or next empty cell on column A’ on all 3 sheets(100,200,300) 2.In Column A there are 3 accounts – 101,201,301 If Column A value = 101 then send the corresponding Column H (P&L) value to Sheet”100”, If Column A =201 then send to sheet ‘200’, If 301 then to sheet ‘300’ Now that’s not quite what I need, the Column H values in some cases have to be added up – by category. You can see this on sheets 100 – 300. Columns B to K on Sheet(100) have the different categories of products. What I want is a code that would go through Column AG in Sheet(Raw) match the category name to the category name in the Range(B2:K2) and copy the P&L figure (Column H value in ‘Raw’) there. e.g. I need all the rows pertaining to account 101 with the same label ‘Interest Rate’ to have the P&L (Column H) added up. So I would get the value 188760 in Sheet ‘100’ Cell D3. (I just added H2,H3,H12,H13) ...
View Replies!
View Related
Sum Categories At Each Change In Value
Could somebody please suggest a formula that will sum values at each change in category? I've played around with a pivot table, but for this it really doesn't fit. Column A is the category and there are totals in column B for each entry. The only place I need a formula is at the change in First and Second...so for any second all of the third's plus that second and for the first everything below. Attached is an example with outcome in column C xxxx First xxxx Second xxxx Third
View Replies!
View Related
Grouping Data Into Categories
I have a given set of data. Each row of data fits into one of 7 different categories. Besides doing a simple filter to be able to pull out a certain "category", how can I do this differently? Is there a code/macro process I need to go through? Basically what I need to do is find a way to group these categories together, so that someone searching for a certain category can easily pull it from the data.
View Replies!
View Related
Sum 1 Column, Based On 2 Variables
A B C D COL 0 COL 0 1 COL 0 COL 0 1 COL 0 EDM 0 I need to add the number of 0's in column B if Column A is COL and C OR D is 1. correct function yields 3 for this example. I've played with =sumproduct(if)) and =sumproduct(if(and))) etc... can't figure it out.
View Replies!
View Related
Sum Of A Range Based On Two Variables
I'm trying to create a simple checkbook application for a friend. I've gotten pretty much everything I want figured out except for two functions, which are basically the same thing. I'm trying to find the sum of all deposits and of all withdrawals based on two entered dates. Basically, they enter the dates, and the formula finds the dates, then sums all the deposits/withdrawals between them. I attached the workbook with some sample data in case I wasn't really clear.
View Replies!
View Related
Sum Column Based On Two Variables
I have data in three columns. The first column contains the machine name (Robot 3, Robot 4, and Robot 5), the second column contains the paint color (1-20), and the third column contains the data that I need to sum. For example the data could be paint purges, the number of purges from each robot. Each row of data will contain a number of purges for each robot, for each color. The data is provided by day, so I will be summing the data weekly.
View Replies!
View Related
Sumif Based On Multiple Variables
I am trying to do a sumif based on two different columns. For example In col A I have part #s, in col B i have quantity, and in col C, I have the different representatives. I want to import the qty sold onto another worksheet. In the cell for the total number I want to know, how many of part# 401 were sold by rep A. Here is the sheet that the information will be drawn from: Part#QTYREP 4011A 4023A 4031B 401 1 A Here is the sheet that the information will be output: Rep Part#AB 4012 4023 4031
View Replies!
View Related
Count The Number Of Categories In A List
I am building a tracking list for several items and have categorized them Software Hardware Networking I will have several of these and the list keeps getting bigger. I have a table above the list that summarizes these catergories and the count of them. How can I count each categorie and so that it updates each time I add one. The formula would have to sumproduct or determine what categories are there and how many (unique list) and update when I add one. Currently its a manual process of creating a unique list and doing a countif.
View Replies!
View Related
Data Formatting (choose The Different Categories)
1. I would like to be able to play with it by choosing different categories, sub-categories etc (adding different ones etc.). I tried pivot table but to get this data in pivot table format is a very time consuming task to start with. 2. The sample file has only one month. I get this type of data every month. So have to annualize using other months etc. I could get different months in a column also. So for example column d could have Jan, Feb, March etc. for row of data. But that would mean I will have 1922 * 10 = 19220 rows. And when I update this every month row reference from formulae (If I had formulae) will need to be updated. I have the flexibility of getting this source data rearranged little bit but not too much flexibility there.
View Replies!
View Related
Summing Dates & Categories
How do I summarise the attached table. I want to have a drop down menu in the MONTH cell (B13) that summarises the month nominated. Also a drop down menu in CATEGORY that I choose (B14). The value of the category in any particular month is totalled in B15. I know how to create drop down menus and how to define ranges - just not sure of the formulas to use in order to get the correct answer in cell B15....
View Replies!
View Related
Deleting The Rows Based On A Couple Of Variables
I have a worksheet ( adage inventory ) and I am trying to clean up some un-needed rows. I need this deleting of rows based on a couple of variables. I need to keep any row that Col F has the text "QCCONTROL" and I need to keep any row that COL N has the text "HOLD" and I need to keep any row that COL N has the text "REJECTED". Sub Delete_OK_Lots() lr = Sheets("adage inventory").Cells(Rows.Count, "A").End(xlUp).Row For x = lr To 2 Step -1 If Sheets("adage inventory").Cells(x, "N") "HOLD" Or Sheets("adage inventory").Cells(x, "N") "REJECTED" Or Sheets("adage inventory").Cells(x, "F") "QCCONTROL" Then Sheets("adage inventory").Rows(x).EntireRow.Delete End If Next x End Sub
View Replies!
View Related
Creating Variables Based On Cell Location
if Row i Column j is activated then a corresponding variable Aij, Bij, Cij or Dij would be created (if not already existing), or cumulated by 1. The problem as I see it - completely generalised - is to create a variable where the variable name itself is formulaic, as well as its value. I know SAS would have no problem with this due to the order in which it parses data steps, but I'm pretty amateur with other programming languages so I don't know if this is blindingly obvious or downright impossible!
View Replies!
View Related
Find Correct Data Based On 2 Variables
I have a project that looks at a table of train schedules for a range of dates. The data is formated so that arrivals and departures are on the same row but differnent columns. The dates are to the right and the grid is completed with the number of arrivals for a paticular day. I have created a formula that looks at the data and returns the arrival time for that date. It worked fine until the schedule was altered so that the same train could arrive at 2 different times on 2 different dates. So train 976 arrives at 4a on some days and 5a on others. In the example Ihave posted train 976 has 2 passengers arriving at 5a but the formula shows 4a. Is there a way I can have the formula return the correct time based on the day of the month? I should point out that the same problem could exist for departures.
View Replies!
View Related
Assign Formula Values Based On Variables
I have three variables; Independent variables x & y, and dependent variable z. x & y can take the values 1,2,3 and can equal each other z can also take on values 1,2,3 but can never equal x or y. I tried using if/and statements to solve this (see below), but I would have to do it for each case and it exceeds Excels embedded function limit. =If(and(B7=1,B10=1),riskduniform(E4:E5),if(and(B7=1,B10=2),3,if(and(B7=1,B10=3),2,if(and(B7=2,B10=2),riskduniform(1,3),if(and(B7=2,B10=1),3,if(and(B7=2,B10=1),3,if(and(B7=3,B10=3),riskduniform(1,2),if(and(B7=3,B10=1),2,if(and(B7=3,B10=2),1)))))))))
View Replies!
View Related
Code To Generate Data Table Based On Variables
I was trying to use the VBA code to generate a data table: Private Sub createDataTable(WS As Worksheet, initialRow As Integer, numCol As Integer, numRows As Integer) Dim initialCell As Range 'specify the upper left cell Dim RefCell As Range 'the reference cell on the caculator sheet 'activate source sheet ActiveWorkbook.Sheets("Calculator").Activate Set RefCell = ActiveSheet.Cells(2, 3) WS.Activate Set initialCell = ActiveSheet.Cells(initialRow, 1) initialCell.offset(numRows, numCol).Select Selection.Table ColumnInput:=RefCell 'the calculation should be automatic, if not, then calculate WS.Calculate End Sub and it kept giving me an input celll not valid error on this line: Selection.Table ColumnInput:=RefCell the thing is, I have defined RefCell as a range object, so it should be OK rite?
View Replies!
View Related
Pivot Table Interpreting Same Category As Two Separate Categories
I am using a pivot table to summarise information with a list of tonnes and sources from civic amenity sites. All was fine until I got to this month to do monthly reports. The pivot table appears to be treating what appears to be exactly the same categories of "CIVIC" and "TRANSFER" differently. By this I mean that although the values in the column appears to be "CIVIC" or "TRANSFER", some get treated differently in the pivot table making it pretty useless. I have made sure that all spellings of the words are the same. there are no spaces before or after the word e.g " CIVIC" but to no avail!
View Replies!
View Related
Sumif Multiple Criteria: Sum Spend In Certain Categories
I have a column which lists total spend by vendor. I am using the sumif formulae to sum spend in certain categories, for example spend greater than £500,000. I would like to use two criteria, for example greater than £300,00 but less than £499,999. I am also using countif to get the number of vendors in each range of spend. How do I adapt my current code of "=SUMIF(D2:D1463,">300000",D2:D1463)" to look at greater than £300,000 but less than £499,999. I'm sure this is simple but like most things only simple if you know how.
View Replies!
View Related
Copy Input Data To Output Table Based On Variables
For what I'd like to do, the attached workbook actually represents 3 worksheet. one input sheet and two output sheets (output1 and output2) I plan to use command button " CommandButton1" on input sheet to start the calculations Here's what I want to do after pressing the " CommandButton1" : 1. start with case 1 and find the value of Input A to Input J from input table using Vlookup and paste those values to respective palceholder for inputs in blue colour area above the input table for each input. 2.for empty cell in the input table paste 0 (zero) 3. after this will give results for case 1 inputs on sheets "output1" and "output2" on basis of formulas used for each output 4.then copy the results for case 1 inputs from sheet "output1" and "output2" and paste them on "input sheet " in Output table (its below Input Table) next to case1 5.Do this for all cases (in actual workbook there will be more than 100 cases)
View Replies!
View Related
Transfer Data To Separate Tables Based Two Variables (date And Site)
I have a spreadsheet where raw data is entered ("RAW_DATA") for each month it is collected. There are 7 questions that are tallied and each of those responses are transfered to a separate data table for analysis (7 individual tabs within the spreadsheet). I'm currently manually data entering the values from the raw data tab to the other seven tabs. I would like to have VBA code that could transfer the data from one month ("RAW_DATA") onto the other 7 tabs based on the date and facility identified in the raw data tab. I have not attempted the VBA since I do not know where to start with searching on two variables.
View Replies!
View Related
Chart Coloring Based On Data Range
I need this chart to show 3 bar colors based upon values from b5:b14 (data column). This chart is based upon the last 10 draws, so since there are 3 positions, the hit frequency expectancy would be (10 draws/3 positions = 3.33 rounded to 3). So now I would like my bar graph colors RED for any values above "3" in b5:b14 (data column), GREEN for any values equal to "3" in b5:b14 (data column) and BLUE for any values below "3" in b5:b14 (data column). Attached is a sample chart
View Replies!
View Related
Print Correct Chart Based On Last Month
to print last months results each month. I have a cell (range name = LastMonth) with formula =now()-15 to display last months full name since I will always do this in the first half of each current month. I want it to look at that cell on the "FY" sheet, see what month is displayed then go to the "Monthly Audit By Cell" sheet & print the correct chart. Each chart is named "Jan Audit" and so forth I have this - only first six months done so far. Problem is nothing happens-no printing! Doing this today on June 14th should print out the May chart. ' Print the Audit for the current month
View Replies!
View Related
Setting Chart Marker Based On Cell Format
I have a dynamic chart which I would like to show markers in a SeriesCollection anywhere the backcolor of any of the corresponding source cells is ColorIndex = 36; otherwise no marker is shown. Something along the lines of: VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes". With Sheets("Chart").ChartObjects(1).Chart.SeriesCollection(1) For i = 1 To UBound(.Values) If "something here to check if .Interior.ColorIndex = 36" Then .Points(i).MarkerStyle = xlMarkerStyleCircle Else .Points(i).MarkerStyle = xlMarkerStyleNone End If Next i End With
View Replies!
View Related
Chart Based On Pivot Table Event Processing
I have an Access application with a form containing an Excel chart based on a pivot table based on an Access query based on an Access table which is refreshed every 5 minutes using a Timer event. When the table is refreshed the Excel chart is refreshed. The chart monitors some processes and is active throughout the day. As the day progresses more and more data points are available for plotting on the x-axis. The x-axis labels get very crowded as the day progresses and I need to programmatically change the x-axis scale at various times of the day. To that end I am trying to find an event that is activated at some point during the automatic refresh of the chart/pivot table. I have tried the Workbook_Change event, the Worksheet_Change event, and the Chart_SeriesChange event. None of these are executed during the refresh process or I have done something wrong in trying to use them. At the moment they only have a MSGBOX message in them so that I can determine if they are even being executed.
View Replies!
View Related
Flowing Month Data Into A Chart Based On Weeks?
I have a spreadsheet that I am using to try to track Project Man Hours. What happens is: I enter in the amount of weeks a Project Phase is in and when the Project Starts. I also enter in how many "Men" we are going to need. i.e. Project Start - January 2009 Phase 1 - 4 weeks with 1.5 Men Phase 2 - 20 weeks with 2 Men. What I want to happen is: Excel then adds that data to a chart showing EACH MONTH and the amount of men needed for each phase. Project 1 showing a line chart with January-December and the lines correlating with the amount of "Men" needed each week based on the Start Date and Sequential Phase Week Amounts.....
View Replies!
View Related
Format Chart Bars Based On Cell Reference
I'm trying to get the bars of a chart to change colour depending on the selection from a drop down in a cell. The drop down is validated to be one of four options which changes the figures displayed in the graph using index matching.I would really like the colour to change to represent what the option being selected is. I used some VBA code before for changing the cell colour depending on the selection made to get over the 3 condition limit. Could this be easily edited to change a bar graph colour? All of the references I can see on line refer to changing the colours of bars based on their value, I don't want to do this, more an independant cell value! My previously used code is below: Private Sub Worksheet_Change(ByVal Target As Range) Dim iColor As Integer If Not Intersect(Target, Range("d9:d129")) Is Nothing Then Select Case Target.Cells(1) Case "Commercial Final State" iColor = 49 Case "Commercial Interim" iColor = 31
View Replies!
View Related
Date Based Bar Chart Macro Code
I am working on a manning document which tells me when people report, and depart based on their job position. I have all of the info in one worksheet, and want to generate a bar like graph on another worksheet in the workbook (by just changing the cell color for ease) for a snapshot to view holes in job positions. The snapshot worksheet has the list of jobs on the left, and months/years in a line acorss the top i.e. 2006 | J | F | M | A | M | J | J | A | S | O | N | D | Job Position X X X X X X Job Position X X X X X X Here was the code I started with, and it doesn't do anything! Private Sub Workbook_CreateCalendar(ByVal Sh As Object, ByVal Target As Range) Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim Fcol As Date Dim Lcol As Date Dim Lencol As Long Dim CalcMode As Long Dim ViewMode As Long 'Set the Start Date of your Sheet Dim DatTim1 As Date DatTim1 = #1/1/2006#
View Replies!
View Related
Change Bubble Chart Color Based On Cell Value
Let Sheet 1 have data in 3 columns to support a standard bubble chart Col A: X Values Col B: Y Values Col C: Bubble Size Id like to be able to color the bubbles according to the Y values, whether the Y values fall within the following ranges Green for Y <=2 Orange for 2<Y<=5 Red for Y > 5 Im assuming some straightforward VB code is in the works but Im not quite there yet
View Replies!
View Related
Changing Chart Source Data Based On Running Date
I currently have a simply bar chart that is based on a running 5 days figure so the X-axis is like 01-Mar 02-Mar 03-Mar ... Y-Axis is the data that I am trying to present - Total $ The source of this data is simply two columns - Column A is the running date, Column B will be data. Daily I need to only present 5 days historical data based on the latest date i.e. today and the past four days (the source data is updated daily). Can a macro be used to auto update the bar charts based on today's date?
View Replies!
View Related
Create Surface Chart Via Macro Code Based On Selection
I want to write a macro to add a surface chart for a Data Range which includes 6000 data points. But the series selected for the chart don't cover all Data Ranges, only part of them. Public Sub AddChart2(LastRow As Integer, LastCol As Integer) Dim cht As ChartObject, currentSheet As Worksheet Dim rng As Range, newRow As Range, srcRange As Range Dim colIdx As Integer colIdx = 5
View Replies!
View Related
Gradient Colorize Chart Points Based On Cell Reference
Conditional formatting is lacking. I need to colorize each data point on the graph according to a characteristic cell (not an x-axis or y-axis variable). I have done something similar by simply graphing a group of points as a series and changing the series color. I'm looking for a more graceful solution. From the example picture attached (i would attach a workbook, but the data is sensitive), a point is charted based on two variables and colorization is attempted based on the third variable. i would find the range of the third value, assign a gradient based on the spread, and colorize each point. My VBA syntax is lacking for chart functions
View Replies!
View Related
Color Column Chart Columns Based On Thresh-hold Line
I want to show 8 members of staff's names, with their sales closing percentages. I want then to produce a chart showing their percentages as a column with a line showing 16% going across the chart showing who is above and who is below the target. Ideally i would also like to change the colour of the columns or background of those below the line to show who is under target.
View Replies!
View Related
Function Back Variables: Function Give Two Or More Output Variables
Can a Function give two or more output variables. e.g. Sub a() x = 5 result = Y(x) End Sub Function Y (x As Integer) As Integer Dim B B = ... * x Y = ... * B this will give back Y as a result. But if I want to get 2 or more output variables (let's say I need to get also B into sub) from one function, how should I do that? I need this because function works with large matrix and I want to extract some values appeared in between.
View Replies!
View Related
Defining Variables Using Variables
I'm trying to loop through a range in excel from access, checking where the titles (in Excel row 1) match with the fields (in a recordset in Access that is passed to the function) - and where they do, I want to dimension a variable to hold the column number - I'm not sure it's possible, but I'd be interested to know either way. The line I'm asking about is at the bottom of the code - the rest of the code is just to give context... Sub ImportGeneric(rsImported As ADODB.Recordset, rsConfirmed As ADODB.Recordset) Dim fd As FileDialog Dim xl As New Excel.Application Dim wb As Excel.Workbook Dim ws As Worksheet Dim iFilePicked As Integer Dim strFilePath As String fd.Filters.clear fd.Filters.Add "Excel files", "*.xls" fd.ButtonName = "Select" iFilePicked = fd.Show If iFilePicked = -1 Then strFilePath = fd.SelectedItems(1) Else ..................
View Replies!
View Related
Erase Variables All The Variables Or To Set The Value Of Them To "0"
i have a "problem" to empty / reset my variables. I defined them as vHour1_KW2 where the "1" is from 1 to 21 and the "2" starts from 1 to 53. Now I want to erase all of this variables or to set the value of them to "0". At moment I use following vHour1_KW1 = 0 vHour1_KW2 = 0 ... vHour1_KW53 = 0 vHour2_KW1 = 0 vHour2_KW2 = 0 ... vHour2_KW53 = 0 until...............................
View Replies!
View Related
|
TRACKER
