I'm trying to use the 2007 histogram tool (or frequency) to show me the frequency of data, but my data is in a different form than required for either of these tools. Let me use the following simple case -- to show the frequency of student grades both tools assume I have a list of student's test grades. What if I instead have more than one column, e.g., "grade" and "number students with that grade"??
My application is actually different, but it's still 2 columns, a numeric property and a weighting factor (e.g., fraction of the total).
I've been trying to make this work with Chart Wizard and can't. I gave out 331 surveys and the data in each column is the answer selections for each question on the survey - A,B,C,D,E. I'd like to do a histogram for each column simply with the percentage of people that chose each answer selection.
I'm trying to achieve a table of weighted ranking based on correlations, a table I already have like the one given below
John / Products Owned 1 2 3 4 Shoes Socks Polish Brush Lace Trousers Shoes Socks Belt Polish Wallet Belt Trousers Shoes Socks
What I'm trying to achieve is this:
for all the top 4 correlations given for each product owned, i need to consider the ones not owned by John and recommend overall rank of the following nature:
For instance, the rank of socks here will be (1+2+4)/3 = 2.33, the rank of belt will be (5+3+1)/3 = 3 [ I'm considering the rank of belt as 5 for shoes, since it doesn't occur in the top 4 ]. Shoes, here will not be ranked since it is already owned by John.
I'm struggling to translate the logic into code here. Currently I perform it in a cumbersome manner, copying all columns to one column, then performing a pivot to find repetitions and so on.
Perhaps there's a function that could scan a dynamic sized table here, find the unique items and number of times they're repeated?
I have been searching to see if it's possible to create a histogram with non-numerical data and it doesn't seem like it's possible. Is there something similar where I can. Count number of occurrences of a string and organize them from most frequent to less frequent? There are about 60 possible strings.
e.g. ABCDEFGHIJ 1ddhykygghd 2jlqqwertya 3ukhghjklzx 4tukyuuiioa 5xypyjkkadh
Then output the counts of each of the strings (letters in this case) present. I was thinking of just putting them in the same row or column and manually counting, but it doesn't seem like it would be that difficult have it automated.
I am writing a macro that finds the prime numbers between to numbers chosen by the user. I want to create a histogram showing the distribution of the primes. I was going to use the Histogram tool in the Data Analysis add-in. My problem is in setting the input range. I do not know how many entries there will be in the input range. How can I write the argument so that it can be "dynamic" I can determine how many entries there are after I determine the prime numbers but I am not sure how to put that info into the Histogram argument.
I am looking for an formula to calculate the bin width for a histogram chart.
I am thinking of creating 50 bins for the chart. The range (max - min) of the data can be from £10 to £5,000,000.
A straighforward formula will be (max - min)/ 50. But I don't want decimal point in the bin numbers.
I am thinking of using Floor/Ceiling function, to round the bin-width to whole numbers, but 10/50, 100/50, 1000/50,..., 1000000/50 all need to round up/down to different scales! for example, I want 100/50 round to 5, 1000/50 round to 50 etc.....I was wondering is there a formula I can use to calculate the bin-width based on the criteria above or similar?
Those "False, False, False, False" parameters to run a histogram, whatever do they mean? Application.Run "ATPVBAEN.XLAM!Histogram", Range("Inputs"), Hist.Offset(0, 3), Worksheets("OutputSheet").Range("Outputs"), False, False, False, False
Through recorded code, I've seen that to create a histogram, the third False is a "True"... but let's say I've already got the blank histogram created. I'm running a simulation (where the inputs range is being updated with every run), and I want the histogram to be filled in at the end of all the runs. What do these parameters have to do with it? Having them all as false, like I've been told, isn't working.
We were working on a set of stock returns using histogram option from the analysis tools. We have not been able to figure out why we have produced two different sets of frequencies over same set of data and one predefined bin range.
Only a few classes present different counts. Can this be due to two different versions of excel were used?
I'm trying to come up with a histogram chart that adjusts Bin Size based on user input. I've come up with the formulas for Bins and Frequency, but am struggling when it comes to charting the data to dynamically update when the user changes Bin Size. How to make the x-axis dynamically adjust with the number of Bins.
I have a large dataset that includes information on the investment payout ratios of US companies over the period 2004-2006. I need to calculate the weighted average investment ratio for each company---see sample below
Microsoft Excel - example.xls ....
The corresponding weight for each year must be as follows: 0.5 for 2006, 0.3 for 2005 and 0.2 for 2004. Then, for company A, the weighted average will be: (0.5*B2)+(0.3*C2)+(0.1*D2) = 7.8 (see cell E2)
The problem is that for some companies there are missing data for some years. For example, for company B, there are no data available for year 2004. In this case , the weights have to change to 0.6 for 2006 and 0.4 for 2005. Then, the weighted average investment for company B will be (0.6*B3)+(0.4*C3) = 16 (see cell E3). Similarly, in cases when there are data for only 1 year , the weight has to change to 1. Then, for company C the weighted average is 35 (see cell E4)
Is there any formula to give me the weighted average as it appears in column E without having to adjust it for each single case?
I'm doing a GCSE project for which I need a little help with Excel.
I have a set of data about a persons monthly spending habits on
Food 300 Entertainment 200 Clothing 100 Transport 260
The pre calculated budget allowance for the month was 500
I would like to write a formula that would do the following; give the person an score out of 1.1-10 based on how well he spent on the above items which are in order of priority (i.e. it is better to spend more on food than on entertainment etc) And then an over all score (1.1-10) that will reflect how well they spent.
In Col DL are digits ranging from 1-36, in Col Dm are the number of times they were drawn in 36 draws. I have a weighted avg formula in Col DN, that I found thru search, but it doesn't appear to be working right or maybe I don't understand the formula. I have no idea if this can be done.
Column A remains the same, with one row for each key. The middle columns have their weighted average (where 6.66 = (5*20+8*13+7*46)/(20+13+46)), and the final column (DX) is the sum of minutes for all like keys.
I am trying to find a formula that will calculate a running total of employee time off. Time off is recorded in Excel spreadsheets that I created. V = vacation, S = sick, PD = personal day, and so on.
For example: If a V is entered in any cell in their attendance worksheet, I would like for a 1 to display under the vacation total column. The next time a V is entered in, I would like it to update the cell containing the vacation days used for the year to 2. If a 1/2 V is entered, a .5 would be added to the total used.
I have two columns. One has cost (column A) and the other has profit (column B). I'd like to calculate the percentage profit as a function of how much something cost. So, if it cost $100 and the profit was $20 I would like to calculate 20%. For all percentages, I'd like to average them. I understand this is a "weighted average" and was able to get this working with this formula:
I created an Excel tool into which really thousands of records are based on weighted averages calculations. And discovered Excel struggling in term of permormance.
The issue is: the user needs to uploads a couple of times in a day new data for further analysis, and the only way to make the Excel spreadsheat humanely usable (performance) was to calculate everything upfront.
To optimize the tool performance, I had to:
- First I made all weighted average calculations upfront when data is uploaded the first time. Leaving formulas in the tool was not viable, since this tool run live analysis, every click would have been a pain of 30 sec at best to re-calculate new conditions,
- Second optimization was to not leave formulas into columns > as soon as a calculation is done, the macro copy/paste values
The formula I chose is sumproduct based weighted averages..
Here is my question:
1. Does it exits specific formulas in Excel to handle nicely large amount of weighted averages to calculate ?
2. Should I consider to use an external application (an Access table, SQL or whatever) to perform those weighted average calculations quickly/quicker.
I am having trouble coming up with a formula to calculate the weighted average of students Finals grades. This is the format my sheet is in to give u an idea.
----------------------------------------------------------------------- Test # 1 Test # 2Test #3Assignment #1Assignment #2Final Test Final MarkPossible Points:1001001001010100Percent Worth:20%20%20%5%5%30%Students:Anderson, Ken6473788768 ?Carter, Bill72768473.587 ?Dubeers, Ashley84887610590Jackson, Kevin7666549873Parker, Jerome82657679.560Remini, Leah969290101089Thompson, Chuck5042806.5760Vaughn, Sarah7265658970 ----------------------------------------------------------------------- - I have to come up with students averages based on the mark, the percent each grade is worth and the maximum amount of points its worth. Anyone know the formula or how to calculate the final grade using all of these factors?
If I wanted to calculate weighted average of a bunch of salespeople, is there a way to not include some values, such as ones below a certain value? I'm trying to so something like, what would my weighted average be if I didn't include my bottom 2 salespeople or my top 2 salespeople.