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.
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?
I am trying to combine data from multiple worksheets and make a chart. I have about 200 keywords in every worksheet (about 50), and some of them repeat themselves through worksheets and some don't. For every keyword, I have an associated value in the next column that I want to portray over time (each worksheet is for a different period).
So what I need to figure out is how to be able to pick any 10 keywords from the worksheets and put them in a line chart where I can see the associated value for each period for every worksheet so I can compare my keywords' efficiency. The tricky part is that some worksheets do not contain the keyword and other worksheets contain the keyword in a different cell than the previous wsheet.
I have a need to populate a summary worksheet using two variables to find data in two or more other worksheets.
I find writing out what I want helps some times so let me try it here.
So my variables are:
Product (there are 22 products) Supply Less than (inset number)
These are the two criteria I want to use to produce a result.
The next issue is I have 300 stores that carry said 22 products. Each store has a unique number 0001, 0002, 0003 etc. So in a separate worksheet I have a list of the store numbers, and then the products. So each product has the store's number to the left in Column A, Column B has the product name, Column C has the quantity on hand.
What i would like to do on the summary page is select the product, and then select the supply less than or equal to 'x' and then have the stores with the selected product less than or equal to x display below.
The last part of this is then to display (data from an other sheet) on the summary page which contains the quantity of the product selected available at the warehouse for that store.
I know the last part is totally wrong (symbols arranged in that manner), but to clarify i would like the date entered in C1 to override the other statements in the fomula to make it read "completed". If no date is entered in C1 then the formula will return either "overdue" or "outstanding" depending on the other dates in A1 and B1.
I'm looking to find a formula to calculate the red cell (actually all the cells in the "type" fields) by determining:
1) If the Product in the table matches the one in row 3 2) If the dates in table columns H, J, L, N and P took place in the month before the billing date 3) If those two conditions are met, sum the matching columns in I, K, M, O and Q.
For example for the red cell would currently equal $11,380.02 as the only revenue collection dates in range would be cells I1 through I3.
I am looking for a syntax to ask a question with three variables
As an example If Blade size = 114 (only 3 blade size choices) and the Panel Size is between 500 and 700 ( This will also be dependant on the blade size) and the tilt bar is clearview (Three choices) then the result will be x = 182
The answers will depend on the above variables: 154, 159, 179, 177, 182 and 202
I need to count up the scores from a questionnaire I have developed, which I am having trouble coming up with a formula for.
I have 5 different types of sales channels in column A. In columns C, E and G there is the call method they use (inbound, outbound or both) with a 'Y' / 'N' option next to it. Basically, I need a formula that if a sales channel has a Y next to Inbound, then the value 'Inbound' to be returned to the destination sale and if it has a Y next to Outbound then the value 'Outbound' returned to the destination cell and so on.
I have attached a spreadsheet to make my problem easier to understand.
I have in cell c40 a data validation list with source equal Royal (=Royal) has been created.
Royal is a namebox that has a defined list of 7 options (6 actual options plus one that says "select via drop down") :
FA4 = Select via Drop Down FA5 = option 1 FA6 = option 2 FA7 = option 3 FA8 = option 4 FA9 = option 5 FA10 = option 6..............
The concept is that FA5 is associated with a value defined in FB5, FA6 is associated with FB6, so on
What I want to happen is when I select a value via the drop down selection in C40 eg. "Option 1" that in cell D40 the value associated with Option 1 (found in FA5) returns the value found in FB5 ($10).
How do I do this? I have tried a number of "if" statements but no luck.
I'm trying to count a range but I have multiple criteria which I would like to use, I have attempted to use countif/sumproduct with no avail. The data is spread over two columns, one contains the rank of various employees and the other is the number of hours they have worked. I am trying to count how many employees fall in to set hour ranges.
I am trying to run the same test on multiple cells, to get the column letters of cells while there are less than 78 columns in use (these column letters will later be used for the Range().Select work involving copy & paste-ing into a word document). I originally thought I could use GoTo statements in conjunction with variables but, having researched it and coming across this, I think I need to find another approach.
At present I have a long section of code that looks like this (I am writing and testing at present so what i make will be put into a larger macro):
VB: Sub FindColumnLetters() Dim i01Feb As String, i02March As String, i03April As String Dim NumbTemp As Integer, NumbTemp2 As Integer
However VBA didn't like the use of variables with the GoTo function and there are not decent ways around. how I can do the original but in less space? FYI the codes above are repeated for 3 columns, where I am actually doing this for ~20 column refs so the space it takes is HUGE at present.
Is it possible to find the formula of a result that is based on three other values.
Here's my example:
I give three cases, but I am sure all three use the same formula. I made the team names up but the values are 'real'.
MATCHES TEAM NAME TEAM RATING OPP. RATING POINTS DIFF. NEW RATING
Match1 Blumps -2.62 5.05 8 -1.28
Match2 Champs 2.8 4.08 -18 2.37
Match3 Sharps 8.26 -3.53 -16 3.68
I want to know what formula was used to calculate the New Rating in the last column. I am not 100% sure if the formula to calculate the New Ratings as shown above includes a value for home ground advantage of which I might be unaware. For what its worth, the Blumps and Sharps were home teams and the Champs was a visiting team for the three matches shown.
I am working on a survey analysis and I'm having trouble comparing two columns.
Ex. 100 surveys Column A being age (scale of 1-5) Column B being willingness to pay (scale 1-5)
I am trying to correlate age with willingness to pay by saying =countif(a1:100 = "1" and (B1:B100 = "1" and "2")) Basically checking to see if the 1st age group has a strong willingness to pay; I would repeat with B1:B100 = "4" and "5".
Is it possible to modify the sub "Continue" below so that the "If s.Name <> wsKeep" line compares multiple variables? The code is for the purpose of setting a workbook to a required state, i.e. hide all worksheets except ones stated in variables. I have further subs that would also benefit from me knowing how to do this.
I am very new at in depth excel formulas, usually in my daily work I can get by with simple summations, if statements etc. This one has thrown me for a loop.
I'm making a spreadsheet on soil compressibility and I feel like I am just knocking my head against the wall. Here is what I am working with. I need to calculate values in a column, we'll call them "Rm" values.
There are a few basic things that dictate which "Rm" equation you use. I'm substituting V,W,X,Y,Z in for the equations to simplify the question.
I currently run a calculation for that associate determining what % of total time was spent on each function. I multiply that by the floor & ceiling of each range, summing the floors & ceilings to produce a weighted range. Then, I compare their production total to that range. I have all this figured out. What I'm running into, though, is that this table is duplicated on a separate worksheet for each month of the year. Beyond that, each member of management maintains their unique workbook listing this information for their associates only. What I would like to do is publish/maintain only one "master" workbook for all associates, regardless of manager.
I tried creating a master table that inserted columns for Month & Manager in front of the columns above. I tried to use a PivotTable (brand new to that process), but I think that because I have certain variables (i.e., Role & LearningCurve) that are "stuck in the midst" of my data, I can't make heads or tails of the PT.
Currently, I have 3 columns per function (Count, Time & LC) as only one LC would exist in a given month for a given function. I figure I can set up the master sheet to have 8 columns per function (account for Count & Time per each of the 4 LC's possible), but that begins to become more cumbersome than what I have already.
In the end, I want to have a table that I can filter by month and/or manager and/or associate to which I can then apply the appropriate calculations. This would allow calculation for a single month or range of months as well as being able to calculate results for an associate regardless of potential reassignment to a different manager, and conversely being able to aggregate results for a given manager using data for whichever associates reported to them each month.
I'm trying to create a spreadsheet that will tell the user how much of two products to use in order to get a desired percentage of total fat. For example: if the total pounds is to be 3000 at a fat percentage of 30%. There would be two products (a lean meat and high fat meat). The percentage of fat in those two meats will change each time. The user will enter those percentages and then the formula needs to tell how many lbs of each to use in order to get the desired percentage of fat.
Each time there is a "target" amount of each product to use. For example: 1000 lbs of Lean Meant and 2000 lbs of Fat meat. Those are based on the ideal levels of fat in each.
However, sometimes the fat is not at the ideal so the formula has to be adjusted in order to compensate.
I have a form that when you select from the drop down box and click submit it opens up a message box with the appropriate response however I want it to also return the information from a couple of Text Boxes as well on the same pop up box and some information that is always the same. I am making it display the correct information from the drop down box by a case statement.
My workbook contains 20 worksheets named Legal1-Legal20. Each worksheet has identical column headings. My goal is to Concatenate the contents of Legal1:Legal20 Cell $D$2(same in all worksheets) IF the text in Legal1:Legal20 Cell $H$2 (same in all worksheets) reads "Open of Record". The contents should be seperated " and ". Please note that cells my be blank on one or more worksheets. Secondly it is possible that only 1 cell meet the criteria and therefore should not be prceeded with " and ".
I have successfully come up with a formula using a similar situation above utilizing SUMPRODUCT noted below.
=SUMPRODUCT(SUMIF(INDIRECT("'Legal"&ROW(INDIRECT("1:20"))&"'!H2"),"Open Of Record",INDIRECT("'Legal"&ROW(INDIRECT("1:20"))&"'!I2")))
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:
What I am trying to do is calculate a particular charge based on two main variables the city and weight chosen (each of which will correspond to a particular value) .I have these cities and weights which correspond to a particular price per 100kg when two are selected
My excel data is set up like this
(A).....................(b)......................(c)..................(d)........................(e).........................(F) .......................Weight..................0-999Kg.............1000-1999kg..........2000-4999kg..........5000-9999kg 1. City 2. Toronto....................................$5.50..................$4.00..................$3.75..................$3.50
(I added a,b,c,d and 1,2,3,4 for reference when explaining the formula)
I have a separate row where I am able to enter a weight in one column (A24) and and choose the city from a list in the second column (B24). I need the next column (C24) to display the dollar charge per 100kg from my data that corresponds to the city I have chosen and the weight entered. I am trying to figure out a formula for C24 that allows for instance $5.50 to be displayed when I type in 800kg for A24 and choose Toronto from a drop down list in B24. This row will look like this for reference:
..................A............................ B..............................C. ..................Weight..................City..................Charge per 100kg 24...............800..................Toronto..................$5.50
There is a lot going on, including a check box, which I dont have much experience with and data on two different sheets. I need to sumif the box is checked (true cell) and the item matches the item on another sheet then I need to sum the qty in the adjacent column.
I will have a similar formula in both C and D, but this will be for what I need in D, and I should be able to figure C out from that. What I have to get my starting number is "=SUM('Proc-Pack SUMMARY'!B16-'Proc-Pack SUMMARY'!C16)". Now I need to subtract what is sold from that number to give me a current on hand number.
Type date qty packaged qty bulk
Inventory sold sheet:
The type column is a drop down box with that matches the type column on the inv sheet. The "cyl" and "lbs" columns have check boxes in them. So if C (cyl) is checked and A matches A on the inventory sheet I need to subtract D.
Type Cyl Lbs Qty
Ive tried various variations of this "=SUM('Proc-Pack SUMMARY'!B16-'Proc-Pack SUMMARY'!C16)-sumif(Sold!F:F,"true",Sold!E:E=Inventory!A:A,Sold!H)"
Also is it possible to link so you can only one of the two check boxes in a row? I know the option button does this, but I didnt think that would work with the formula.
In VBA i have 3 userforms, the first 2 take the data and store them in variables. The 3rd form is supposed to take the variables from the other two forms and do some calculations with them. Although i cant get the 3rd form to take the stored variables from the other two forms. Is there a way to use stored values from other userforms?
I have a drop down list in a column called Report Type (example below).
Report Type - Drop-Down Menu in Column F Business/Operational/Work Plan Budget Report Performance Report Program Quarterly Report Program Mid-Year Report Program Annual/Year-End/Final Report Service Quarterly Report Service Mid-Year Report Service Annual/Year-End/Final Report Financial Quarterly Report Financial Mid-Year Report Financial Annual/Year-End Report Auditied Financial Statements In-Year Reallocation Annual Reconciliation Report SRI Report Other Report
I need to count all the cells that have: Budget Report, Financial Quarterly Report, Financial Mid-Year Report, Financial Annual/Year-End Report, Audited Financial Statements, In-year Reallocation, and Annual Reconciliation Report
I have a worksheet that has 6 columns of numerical data that all column datasets are referenced together for each row. So for example the data in row 5 in all colums is related.
Column C through F have 4 numbers that refer to the data in the first 2 columns. These are also ordered by row.
I need to look in column A for 0.00, and column B for 3.14 Both of these must be in the same row or I need the closest value. When these values are found in columns A & B, return the values from the same row in Columns C through F.
I'm working on a complex spreadsheet and I'm working on a complex spreadsheet system for pulling and measuring data. My VB programming skills are about minimal/average, so you may see me on here asking various questions . In any case, what I'm trying to do is create isolation macros for "Kickback" data (erroneous). I'm trying to remove data with certain criteria and isolate it on a separate "kickback" sheet for one for taking a second look at. I've made the easy macro of creating a new spreadsheet:
Sub Create_Kicbacks_Sheet() ' Create_Kicbacks_Sheet Macro ' Creates "Kickbacks" sheet for invalid information. Sheets.Add After:=Sheets(Sheets.Count) Sheets("Sheet4").Select Sheets("Sheet4").Name = "Kickbacks" Sheets("Kickbacks").Select End Sub
This coding works correctly. The problem area I'm finding is the sorting data. My goal is to look at Columns A and B for certain criteria and either leave it alone, move it to the "Kickbacks" sheet or delete (due to not being necessary in data calculations). Basically, here's a synopsis of what I'm looking for:
if Column A = Y and Column B = Mandatory -> Leave Alone if Column A = Y and Column B = Best Efforts -> Move Row to Kickbacks if Column A = Y and Column B = Empty Cell -> Move Row to Kickbacks if Column A = Empty Cell and Column B = Mandatory -> Move Row to Kickbacks if Column A = Empty Cell and Column B = Best Efforts -> Delete Row
Here's the code I have in excel (modified from one I found online)... Which only is doing some of what I want it to do:
Sub Moveto_Kickbacks() Dim r As Range, LR As Long With Sheets("Data") LR = .Range("A" & Rows.Count).End(xlUp).Row Set r = .Range("A2").Resize(LR - 1) .Range("A1").AutoFilter field:=1, Criteria1:="" .Range("B1").AutoFilter field:=2, Criteria1:="Mandatory"