Histogram From Weighted Data

Jan 12, 2010

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).

ADVERTISEMENT

Non-numerical Histogram

May 9, 2009

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.

View 4 Replies View Related

Creating Histogram From VBA

Apr 25, 2006

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.

View 4 Replies View Related

Histogram: Intervals Below Bar

Oct 5, 2006

I'm trying to make a histogram with the data analysis tool. The problem is that whenever I make a chart it doesnt show my intervals correctly. It puts them under the bars instead of between them.

View 3 Replies View Related

Histogram Intervals

Oct 6, 2006

I'm making use of a histogram to visualize my data. The only problem is that 90% of the data has a value which lies close to eachother but 10% is very different.

For example:

Interval-----Frequency
0-10------5
10-20-----7
20-30-----6
30-40-----0
40-50-----0
50-60-----0
60-70-----0
70-80-----0
80-90-----0
90-100----0
100-110---2

Now the histogram I will obtain won't look very good. Is there a way I can have an interval like 40 - ... which resembles everything above 40?

View 4 Replies View Related

Plot Bin Average On Histogram

Apr 19, 2012

On Histogram, is it possible to plot Bin averages?

In my data there are two columns, 1) File Size 2) Months and then I have created Bin(months) like 0,6,12,18,24

I want to plot average file size in those Bins. For example I would like to plot what was the average file size in past 6 months, past 6-12 months so on.

View 1 Replies View Related

Histogram From 1 Column Of Data

Jun 11, 2009

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.

View 9 Replies View Related

Histogram Data Collection

Oct 27, 2006

I have a large table (20,000 plus entries) and I need a way to pull out data that matches criteria in buckets.

For example:
April data (column B) that is from 0 to 30 (column C)
April data (column B) that is from 31 to 45 (column C)
... 46 to 60
... 61 to 75
... 75 to infiniti

Then do it again for May, June, July, etc. I have used DCOUNTs to pull this type of data, but it's a bit cumbersome. Any other ways to pull this type of data from the table?

View 3 Replies View Related

Histogram Bin Width Calculation

Jul 11, 2007

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?

View 4 Replies View Related

Histogram Parameters To Be Filled In At The End Of All The Runs

Nov 30, 2009

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.

View 4 Replies View Related

Histogram Analysis - Two Different Sets Of Frequencies

Mar 25, 2012

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?

View 1 Replies View Related

Histogram Plot With Normal Curve Overlay

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

Dynamic Histogram That Adjusts Bin Size Based On User Input

Jul 2, 2014

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.

View 5 Replies View Related

Histogram Function: Find Number Of Cells That Falls Within Each Of These Max Values

Nov 21, 2006

i m given 12 max value of my 292 cells. now im asked to find number of cells that falls within each of these max values?? im asked t use histogram. how will i do it?

View 3 Replies View Related

How To Do A Weighted Median?

Nov 2, 2006

Is there any way to do a weighted median in Excel?

Consider this example: ....

View 7 Replies View Related

Weighted Average

Jul 25, 2007

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 ....

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

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?

View 9 Replies View Related

Weighted Scoring

Mar 15, 2008

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.

View 9 Replies View Related

Is This A Weighted Avg Formula

May 1, 2009

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.

************************************************************************>Microsoft Excel - F5 HOT-COLD.xlsx___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutDN3DN4DN5DN6DN7=DLDMDNDO2DIGITSHITS 34125 47105 53395 62185 72885 DIGITS [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

View 9 Replies View Related

Weighted Average In VBA

Nov 10, 2009

I have a spreadsheet which basically looks like this

2, 5, 15, 20
2, 8, 12, 13
2, 7, 19, 46
F, 1, 10, 26
P, 1, 10, 39
P, 6, 22, 89
P, 7, 14, 46
P, 2, 11, 14
...

Column A is the key. If multiple rows have the same key, they're related.

The last column (DX on my spreadsheet) represents minutes participated, and is the weight variable. The middle columns are data that I'd like a weighted average for.

Here is the output I'm looking for given the above example:

2, 6.66, 16.83, 79
F, 1, 10, 26
P, 4.91, 16.73, 188

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.

View 9 Replies View Related

Weighted Average ...

Nov 16, 2009

I have long lists of data which has name of manufacturer, number of items and fault rates of items.

E.G

Manufacturer.......Number of Items......Fault Rate
Dell....................40000....................0.2%
Dell....................42200....................0.4%
Sharp.................303003..................0.1%
Microsoft.............312340..................0.04%
Dell....................3249321..................1%
Panasonic............300020...................2%

I need to find the weighted average of each manufacturers fault rate for just themselves, and also how the average of all the manufactueres would be if they weren't in it.

So for example for Dell I would need their weighted average of their three data entries and also then the weighted average of Sharp, Microsoft, Panasonic together.

I have been doing this by splitting the data into sections so for the example above I would do:

Manufacturer.......Number of Items......Fault Rate
Dell....................40000....................0.2%
Dell....................42200....................0.4%
Dell....................3249321..................1%
Panasonic............300020...................2%
Sharp.................303003..................0.1%
Microsoft.............312340..................0.04%

I would sum the Dell items, then sum the number of items excluding Dell then do two weighted average calculations giving me the ones that include then the ones that exclude Dell.

Then I would do...

Manufacturer.......Number of Items......Fault Rate
Sharp.................303003..................0.1%
Microsoft.............312340..................0.04%
Dell....................3249321..................1%
Dell....................40000....................0.2%
Dell....................42200....................0.4%
Panasonic............300020...................2%

And do the same process but with Sharp totals etc then move onto the next one.....

This example is with a very small data set so I'm sure you can see that this process is very time consuming, but I don't know any other way of doing it without splitting out the data first.

View 9 Replies View Related

Weighted SUM By Criteria

Jan 22, 2008

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.

View 2 Replies View Related

Weighted Numbers Greater Than Whole

Apr 15, 2014

I have a set number that I need to allocate over a flexible period of say 5 -11 days.

I have applied the traditional split per day to each number i.e. a monday normally results in 20% of transactions, Tuesday 25% etc.

The issue comes in when I try to allocate the budget over a period longer or shorter than a week.

Therefore the budget for the 9 days is being allocated over the period of 9 days which results in a overall allocation of 150%. (duplicate mondays etc).

Is there a way to limit the entire period to a set number and have the numbers adjust themselves accordingly up or down to match it other than multiplying it by a factor?

Example attached: Excel Problem.xlsx

View 3 Replies View Related

Conditional Weighted Average

Apr 20, 2009

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:

View 8 Replies View Related

Weighted Average With Criteria?

Dec 5, 2012

Ceate a formula for weighted averages according to criteria?

I want to know what was the average price I bought a product at on today's date. The average price needs to be weighted against the quantity of product sold.

View 3 Replies View Related

Optimizing Weighted Averages?

May 21, 2014

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.

View 2 Replies View Related

Weighted Average By Range

Sep 9, 2012

Price
units
percent discount
weighted percent discount (1.0-2.0 price range)
1.32
100

[Code]...

So from the above example I would like to determine the weighted average for percent discount (weighted by units) by price range.

View 4 Replies View Related

Weighted Average With Grades.

Dec 7, 2008

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?

View 9 Replies View Related

Using Weighted Average In A What If Analysis

Sep 4, 2009

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.

View 9 Replies View Related

Calculate Weighted Median

Aug 22, 2008

I have a task wherein I have to separate data in the Excel sheet and then calculate its median.

For example the data is recorded as follows....

View 9 Replies View Related

Criteria Weighted Average

Jan 5, 2007

I have trouble finding the right formula for a weighted average price. The formula which i used in cell B2 of the attached file doesn't exclude irrelevant numbers. In the formula "= sumproduct(a5:a21;b5:b21)/sum(b5:b21)", i like to exclude cells B8, B9, B15, B19, B20 because the list prices are missing. How can i exclude these irrelevancies in the formula above?

View 3 Replies View Related

Weighted Average & Rank

Jul 25, 2007

Each individual enters 6 different qualifying numbers (columns) resulting in a "place" in each column. Placement in each of those columns is equal to the other columns or as important as the other. Is there a way to set up a formula to reflect each individuals "place" in each column and then have those columns weighted equally and averaged to determine the Winner?

View 9 Replies View Related

Weighted Average Calculation

Sep 28, 2007

I just got Excel 2007 and wanted to know what formula to use to calculate the weighted averages in the attached excel file.

View 6 Replies View Related

Calculate Weighted Percentage

Oct 18, 2007

In my spreadsheet (testing.xls) I have a chart to mark the quality of a phone call based on a list of 8elements being judged. I have a likert scale for the quality result for each element, say for example 5 for highest, 3 for average, 1 for the poor, 0 for very poor and NA for an element which is not counted.

At the bottom of my table I have a cell where it would work out the % based on the outcome of the result from the different cells being ticked, what would i need to do to have excel work this out for me? Please bare in mind that if there is for example 1 element out of the 8 elements marked as NA the result would be divided 7 instead of 8.

View 9 Replies View Related

Weighted Average Of Grades

Jan 4, 2008

I am in need of some more assistance regarding finding out a formula to find out the average grade of a pupil which ranges from letters 'A to E. This maybe be a simple task for experts, but unfortunatley spreadsheets are not my strong point. D7:D14 is the coloum that i wish to have an average of the grades acheived by each individual pupil in thier correspongin rows (on the right hand side of D7:D14) http://i41.photobucket.com/albums/e2...2/example2.jpg

View 5 Replies View Related

Reversed Weighted Average Formula?

Jun 6, 2014

What I am trying to do is rank my employees based on certain metrics. They are weighted in three different catagories. Talk time is 40%, Quality 50%, Attendance is 10%. I can weight the Talk Time and Quality, as those are basically the higher the score the better the weight. Attendance is the opposite, the lower the number, the higher the weight. Meaning, if employee has 1 absence that would better than someone who is at 4 absences. Is there a way to do this. I am somewhat educated on VBA, I understand if,then,else statements and/or Select/Case statements, so if I needed to do it in VBA I can. I just need direction to get it going.

View 3 Replies View Related

Weighted Averages Using SUMIF And COUNTIF

Jul 6, 2009

DATA: D1:D11 contains either blank cells or a dollar figure from a payroll check.
E1:E11 contains either blank cells or the date of the check.

Current Situation: Using the following formula I obtain a weighted average of the pay checks, not including the largest. =(SUM(D1:D11)-MAX(D1:D11))/(COUNT(D1:D11)-1)

Problem:
I desire to calculate the weighted average based on the checks issued only prior to a certain date. I have used SUMIF() in other parts of the workbook successfully but needing to withold the largest value has presented me with a problem I can not find a solution to. =SUMIF((E1:E11,">="&TargetDate,D1:D11)-MAXIF(E1:E11,">="&TargetDate,D1:D11))/COUNTIF(E1:E11,">="&TargetDate,D1:D11)

View 4 Replies View Related

Calculating Weighted Average With Criteria

Aug 25, 2009

I have a data set that looks something like the following:

TypeAmountCost
A100$50
A200$40
A300$35
B250$40
B275$55
B500$60
A700$20
B350$25
B450$35
C400$80
C400$60

I am having trouble creating a formula that would calculate the weighted average cost depending on the type (i.e. weighted average cost of $28.84 for A, weighted avg cost of $70 for C).

View 2 Replies View Related

Weighted Average Formula Population

Jul 13, 2007

See attached picture. This shows Profit and Growth for the stores in each region.

The percentage total is weighted based on the profit of each store in the region.

What I have is an excel sheet with hundreds of regions (going down), all with a different amount of stores. I need a way to populate each of its Region % Total with the weighted average formula (in my example, cells C8, C12, and C20) while also varying the formula to include only the stores in the region.

For example, C8 calculates the weighted average for %s in rows 2 through 7 (6 stores), but C12 calculates it for rows 9 through 11 (3 stores). I need the formula to vary based on the number of stores.

View 9 Replies View Related

Weighted Percentage With Reference To The Date

Jun 8, 2008

I am making a spreadsheet that requires multiple dates and percentages. Is there any way that I can make the percentage that is related to the most recent date weighted more than a percentage with a date from a month or two ago?

For example:
X//Y//DATE
90%//85%//3-March
85%//70%//21-May
87%//90%//6-June

Is there formula so that I can have the 90% and the 87% be the most important (or weighted), the 70% and the 85% be the second most important, and the 85% and the 90% be the least important when I average them out?

View 14 Replies View Related

Weighted Average Gross Margin

Mar 1, 2012

I'm trying to calculate a weighted average gross margin but am having difficulty because I've instances of positive cost of sales and negative revenue. On a line by line basis I've had no problems calculating the margin but when it comes to calculating the weighted average margin as a whole I come unstuck. Looking at the data below (A1:D14) you can see that the margin should be somewhere between 90-99%.

Total RevenueCost of SalesGross ProfitGross Profit %4,805(449)4,35690.6%4,992(330)4,66293.4%3,467(246)3,22192.9%4,974(317)4,65893.6%3,873(130)3,74396.6
%3,756(258)3,49893.1%3,955(128)3,82796.8%2,872(59)2,81397.9%1,401(1)1,40099.9
%(10,987)21,45810,47195.3%3,042(172)2,87094.3%2,748(40)2,70998.6%2,682(172)2,51093.6%

View 4 Replies View Related

Calculate Weighted Average Sum Product

May 29, 2013

I am trying to calculate a weighted average and have used a sum product to do so. My problem is that the values decrease over time and I have done this in cells d13 and e13 and this seems to work ok. My problem is that I have many months so I don't want to use the formula this way as it will be very long. I tried to use sum and offset to try and effectively sum each row but this doesnt work.

Weighted Average

B
C
D
E
F
G

3
months

4
rate
original value
1
2
3
4

[Code] ...

View 4 Replies View Related

ADVERTISEMENT