Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Weighted Average & Rank


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 Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Weighted Average
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 Replies!   View Related
Weighted Average In VBA
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 Replies!   View Related
Weighted Average ...
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 Replies!   View Related
Conditional Weighted Average
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 Replies!   View Related
Weighted Average With Grades.
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 Replies!   View Related
Using Weighted Average In A What If Analysis
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 Replies!   View Related
Weighted Average Calculation
I just got Excel 2007 and wanted to know what formula to use to calculate the weighted averages in the attached excel file.

View Replies!   View Related
Weighted Average Of Grades
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 Replies!   View Related
Criteria Weighted Average
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 Replies!   View Related
Weighted Average Formula Population
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 Replies!   View Related
Linear Weighted Moving Average
I have done quite a bit of looking on Google and looked over the posts in this forum, however, I can't find an example Excel worksheet for a linear weighted moving average.

The data set I am applying this to has 180 data points and the linear weights should extend back over the last 30 points.


View Replies!   View Related
Weighted Average To Come Up With A Speed Value For Each Hour
I have a spreadsheet with the following data that elapses an entire year, broken down into 24 hourly sections per day:

column A: hour of the day (from 0 to 23)
column B: vehicle group (separated into "Passenger", "SingleUnit", and "TractorTrailer")
column C: average speed for that vehicle group for the corresponding hour
column D: total volume/number of vehicles in that vehicle group for the corresponding hour

I would like to apply a weighted average to come up with a speed value for each hour (i.e. combine the average speeds for each of the 3 vehicle groups based on their relative volumes across the entire dataset).

My final table should show only:
Hour (from 0-23), Avg. Speed (containing the weighted averages from the dataset)

Attached is a screenshot example.

View Replies!   View Related
Weighted Average Excluding High/Low
I'm trying to calculate a weighted average excluding the top and bottom value, and am having trouble creating a formula that can be dragged across.

my values are in E2:L17, and the weights are in M2:M17. Basically I want a weighted average of each column, using M2:M17 as the weights.

I'm sure someone's done something like this before, care to assist?

Example:

Data: 1, 2, 3, 4, 5
Weights: 3, 4, 5, 6, 7

Weighted avg ex hi/lo: (2x4+3x4+4x6)/3 = 44/3

View Replies!   View Related
Calculating Weighted Average With Criteria
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 Replies!   View Related
Weighted Average With Text Criteria
how to do a weighted average with a text criteria. I have 3 columns and many rows. The first column has either "buy" or "sell". The thing is I need to find the weighted average based on the product of the other 2 columns for buy and sell separately e.g.:...............

View Replies!   View Related
Weighted Average After Filter Selection
I am looking for a formula that calculates the weighted average list price. In the attached file you'll find a formula which i've got through Ozgrid. The formula in cell I1 is: sumproduct((i4:i69)*(j4:j69))/sumproduct((j4:j69)*(i4:i69<>"")
That formula is perfect untill i filter data. For example:

The weighted value for the entire datasheet is 25.83%. After i made a filter selection on France the weighted value is still the same. How can i modify this formula so that it only calculates the weighted average for the selected records?

View Replies!   View Related
Calculated Weighted & Conditional Average
The attachment is only a small portion of the data I need to analyse, but is representative of the full set of data. What I am trying to do is calculate a weighted average of each of the chemicals shown by location and machine usage and also still be able to use the filter or sort funtions. I have created a sample calculation in cell F9 to further explain this calculation.

I would like to be able to use the A-Z sorting icon in any of the columns, while still computing the weighted average of the respected chemicals. As it is now, the referenced cells just remain as the 6-7 cells above the calculation instead of the 6-7 cells that apply to the specific chemical at the location and machine (in this example - caustic at location1 on mch-03). For example, if I try to sort in column F, I'd still like to see my example calculation to reference the same values as it is now, calculating the same resulting 60.719.

View Replies!   View Related
Dollar Weighted Average Sumproduct Formula:
I've been trying to figure out a dollar weighted average formula in excel.

See attached file...

In sheet 1, I need to pull data from sheet 2 using a sumproduct formula to find the dollar weighted average maturity of a bond portfolio. Basically, I need to know how many days the portfolio has left to mature according to the portfolio's weighting by the amount in column H.

View Replies!   View Related
Resolving Weighted Average #Value Error In Worksheet
I have created a spreadsheet containing 10 different worksheets and 11th worksheet is created to show summary results of all first 10 worksheets.

Based on the situation sometimes the summary table (on each worksheet) could have "NA" Not Applicable value/text in the summary table (summary table has three distinct rows showing result of each category.

For resolving the problem please review worksheet # 10 and advise solution:

I have resolved the issue of "NA" for the first row in summary table (c25), but I cannot resolve the formula for C26 and C27. I.e., if there is "NA" in either C26 or C27, the result in C28 comes up as #Value.

Secondly, there is another worksheet named "results". This worksheet is created with an objective to show consolidated results in weighted average(summary results for each worksheet from 1,2,3.....10) but I am lost at which formula to use to get weighted average in this worksheet.

View Replies!   View Related
Formula That Will Provide A Weighted Average Rate
I would like to a formula that will provide a weighted average rate (yellow cells) for a provided volume level, given the tiers on the left. I have already performed this the "dumb" way with lots of IF statements to figure the volume by tier and then taking a weighted average of the products with the rates by tier, but there's got to be a more efficient way. My file is huge! ....

View Replies!   View Related
Easy Weighted Average For Thousands Of Items
I am trying to calculate weighted average cost for a list of thousands of items.

I will try to explain this as best I can.

Column A is item. Column B is Mode. Column C is Volume. Column D is Cost

There are only 2 Transmode Per Item. So each item is pretty much listed twice and I need to calculate the weighted average cost per item.

I know how to use the SUMPRODUCT function to calculate weighted average and could slowly go through the spreadsheet item by item calculating this, but is there a way to do this with some sort of formula? Similar to a Subtotal that would recognize each change item and calculate the Weighted Average at that change.

View Replies!   View Related
Formula For Possible Weighted Average Or % Completed For A Process
I am trying to figure an easy way to create a formula or possibly even a simple macro to determine % completed of a particular activity (milestone) within a process. Within each activity there are 5-10 items that need to be accomplished. Each Activity could have a different number of items. In order to provide status at an activity level, each item within the activity will be designated with an N/A (0%), Not Started (0%), Just Begun (25%), Half Complete (50%), Wrap up (75%), or Completed (100%) from a drop down box that the user will select. Each one of these criteria can have a weight assigned to it that is in parenthesis. How do I come up with a formula based on the choices above to reach an overall percentage for the activity at any given time and then tell me status level in descriptive terms of the overall activity....

View Replies!   View Related
Multiple Criteria Text Weighted Average
It is a database in which each row represents a unique individual.

Columns A - G contains information which is irrelevant for this question
Columns H - CQ contains answers to the individual's questions (Yes/No or "-" for N/A)
Column CS contains a array formula which determines the % of "Yes" answered only in Question 1 (Columns H to X).
The formula finds all "S1*" (within H1 to CQ1) and if the corresponding "S1" has a "Yes" (within eg. row 2 - H to X) it adds it and divides by the total number of "S1" (17 in this example excluding the "-")

Eg:
In Row 2: S1 = 17, Yes = 7, "-" = 0, % = 7/17 =35.29%
In Row 3: S1 = 17, Yes = 8, "-" = 1, % = 8/16 = 50%

So I have the array formula in column "CS" which does the above.

What I want to achieve:

I want to get the average of all the individual rows in column "CS". This is shown in "CS1 (48.32%". I am looking for a way I can achieve this with just one formula - A formula which first calculates the individual %s and averages the %s within the same formula.

View Replies!   View Related
Conditional Weighted Average Within Time Span
have the follwing worksheet"

Date Rate Qty
2:06:56 PM 199.5 116
2:06:49 PM 199.5 343
2:06:40 PM 199.5 226
2:06:28 PM 199.5 48
2:06:20 PM 199.4 162
2:06:11 PM 199.5 95
2:06:04 PM 199.4 449
2:05:58 PM 199.6 1488
2:05:40 PM 199.5 9
2:05:40 PM 199.5 9
2:05:37 PM 199.4 161
2:05:28 PM 199.6 101
2:05:23 PM 199.35 4605....................................

I would like to calculate the weighed average rate( rate*qty/ total qty durig a period) for different periods say 2.00 pm to 2.01 pm, 2.01pm to 2.02 pm etc. Is there a way to do this by using database(dsum,daverage) functions. What will be the content of the criterion range? Or is there any other way of solving this.

View Replies!   View Related
Multi-Condition Volume Weighted Average Calculation
I am trying to figure a way -- preferably in a single- cell formula -- to calculate a conditional volume-weighted average of a series of associated prices and quantities located in two separate columns based on the date (or dates) the transactions occurred.

I can do a simple Vol. Weighted Average (VWA) of all the prices and quantities over the five-day period with the following formula: (I have created named ranges for the price column (A1:A30=price), the quantity column (B1:B30=quantity) and the date column (C1:C30=date):

= SUMPRODUCT(price, quantity)/SUM(quantity)...

View Replies!   View Related
Calculate The Weighted Average Of The Win Rate Based On Volume Of Calls
I have 3 sets of data for two different groups:

Group 1 - Inbound
- Total volume
- Gross adds
- Win rate (gross adds/total volume)

Group 2 - Outbound
- Total volume
- Gross adds
- Win rate (gross adds/total volume)

I need to calculate the weighted average of the win rate based on volume of calls. Is there any way to do that?

View Replies!   View Related
Multiple Ranking: Rank The Values In Column B And Then Rank The Values In Column C
What I am trying to do is give the rank in column D based on the values in columns B and C. Some of the values in column B will have then same rank, and as such I want to add further criteria on which to rank them. I would first like to rank the values in column B and then rank the values in column C, which should give the rank in column D. For example Dog and Frog have the same value of 400 from the Non UK column. Therefore, rather than having these as both rank 1, I want them to be ranks 1 and 2, so want to add another criteria (UK). As Dog is greater than Frog in the UK (i.e. 10>7), I would like to rank Dog as 1 and Frog as 2. Goat will be ranked as 3 because it had the thrid highest value in the Non UK.

ABCD
1Non UKUKRank
2Cat20055
3Dog400101
4Eel200114
5Frog40072
6Goat30023

View Replies!   View Related
Is This A Weighted Avg Formula
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 Replies!   View Related
How To Do A Weighted Median?
Is there any way to do a weighted median in Excel?

Consider this example: ....

View Replies!   View Related
Weighted Scoring
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 Replies!   View Related
Weighted SUM By Criteria
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 Replies!   View Related
Histogram From Weighted Data
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).

View Replies!   View Related
Calculate Weighted Median
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 Replies!   View Related
Calculate Weighted Percentage
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 Replies!   View Related
Weighted Averages Using SUMIF And COUNTIF
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 Replies!   View Related
Weighted Percentage With Reference To The Date
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 Replies!   View Related
Calculate Weighted Averages Using High, Medium, Low
On another thread I found a solution to creating a weighted average given a pair of columns of numbers but I have column B consisting of values given as High, Medium and Low and the weighted column C of 1, 2 or 3.

How can I use the formula below to accomplish this, or is this maybe not the right way to created a weighted average?

=SUMPRODUCT(B2:B11,C2:C11)

View Replies!   View Related
Weighted Count Of Specific Segments In A Cell
I need to counting formula (I'm calculating weighted frequency of specific letters). My data is as follows:

A1 "lingual"
B2 "28"

The formula needs to look at the word in A1 and count the number of times a given letter (e.g., "l") appears in the cell (any position), and then multiply the result of the count by the value in B2. In the example above, the answer should be 56 (the letter "l" appears twice in "lingual", so it's 28X2).

View Replies!   View Related
Average And Vlookup: Average The Grades With Appropriate Names
i have two columns...a and b (a w/ names, and b w/grades). then i have the table lookup with names and grades all mixed up for many rows. i want to be able to average the grades with appropriate names.

=average(vlookup(name, table, column, false))?? i don't get it to work and how can i specify the grade to average?

View Replies!   View Related
Worksheet.function Average Returning Error "Unable To Get The Average Property Of The WorksheetFunction Class"
TotHCInv.Value = WorksheetFunction. Sum(KRInv, PBLInv, CRInv, PVInv)
If i >= 34 Then CPSCtphRMA.Value = WorksheetFunction.Average("G" & (i - 30) & ":G" & i)

The first line runs properly, but the second line bugs out with the error message "Unable to get the Average property of the WorksheetFunction class". I can simply do the math, but I thought that using the worksheet function would be easier than summing and dividing. I'm curious, though, as to why I can't seem to use the Average function.

View Replies!   View Related
Grade Point Average Using AVERAGE IF AND
I am attempting to calculate Grade point averages for my students for all classes. There are 5 columns of grades to be considered. I frist assign values of 0-5 to the grades then average the points. My problem is that I want the AVERAGE to ignore the zero but it calculates it as well. (I enter a 0 if I have no grade for that class.) I have tried the{ } to make it an array formula but this also did not work. Here is what I have, can anyone help?

=SUM(AVERAGE(IF(AND(L2>89,L2<100),5,IF(AND(L2<90,L2>79),4,IF(AND(L2>69,L2<80),3,IF(AND(L2<70,L2>59), 2,IF(AND(L2>0,L2<60),1,0))))),IF(AND(T2>89,T2<100),5,IF(AND(T2<90,T2>79),4,IF(AND(T2>69,T2<80),3,IF( AND(T2<70,T2>59),2,IF(AND(T2>0,T2<60),1,0))))),IF(AND(AB2>89,AB2<100),5,IF(AND(AB2<90,AB2>79),4,IF(A ND(AB2>69,AB2<80),3,IF(AND(AB2<70,AB2>59),2,IF(AND(AB2>0,AB2<60),1,0))))),IF(AND(AJ2>89,AJ2<100),5,I F(AND(AJ2<90,AJ2>79),4,IF(AND(AJ2>69,AJ2<80),3,IF(AND(AJ2<70,AJ2>59),2,IF(AND(AJ2>0,AJ2<60),1,0))))) ,IF(AND(AR2>89,AR2<100),5,IF(AND(AR2<90,AR2>79),4,IF(AND(AR2>69,AR2<80),3,IF(AND(AR2<70,AR2>59),2,IF (AND(AR2>0,AR2<60),1,0)))))),-1)

I know it is huge. The syntax is correct. It calculates the average, but always for all 5 columns. It will not ignore a 0 in a column.

View Replies!   View Related
Formula =AVERAGE(B16:L16) To Give The Average
I'm using the formula =AVERAGE(B16:L16) to give me the average.

However I have a couple of problems with this. Firstly I would like to exclude the value zero from the average. Secondly to also ignore the lowest and highest values.

Example, if the values in the cells are 0,1,2,3,4,5,6,7,8,9,10 then the current result shows 5, by ignoring the 0 and lowest value 1 and highest value 10 the average should be 4.5.

View Replies!   View Related
Avoiding Average Of An Average: Times
Ok, I for some reason just cant wrap my head around this. I need to to get the average time per call of two rows, but they are based on how many calls taken.

so in one column i have 50168 calls taken at 4:21 seconds per call. and in the next row i have 597 calls taken at 5:20 per call. I need to see what the new average will be with them combined, and I need it to display in m:ss form.

View Replies!   View Related
Sum And Rank
I am stumped on this one. I a spreadsheet with 5 columns (Quarters, Months, Level or Rank, Name, Sales). I need to have a formula to pull in the new Level 1 (rank) after Q4 sales are summed up across months for each name. See the attachment for detail.

View Replies!   View Related
Rank For Two Columns
I have two columns of data - weights recorded during an angling competition. I currently have a rank set (in C) for the first column (A - pounds) to give a position for the highest weight recorded during the competition, the second column (B) records the ounces.

I need to be able to include the second column in the rank to cover the eventuality of two people recording the same number of pounds but different ounces.

View Replies!   View Related
Rank The Values
I have 10 sales figures in C2:C11.

In D2:D11, I want the rank of those values, with one twist.

If the value in B2:B11 is X, then that value is excluded from the ranking.

I am trying to basically do:
=RANK(C2,IF(B2:B11="X",0,C2:C11))


View Replies!   View Related
Rank If Forumula
Trying to figure out how to write a formula to rank values in B if the values in A are the same? I tried rank(if(A2:A17=A2,B2:B17))but this did not seem to work.

View Replies!   View Related
Rank My Students
A1 has column heading Name.
B1 has column heading Credits.
C1 has column heading Rank
A2 has student's name and B2 has his Credit value
Row 3,4,5 are blank.
A6 has a student's name and B6 has his credit value.
Row 7,8,9,10,11 are blank.
A12 has a student's name and b12 has his credit value. So on so forth.

All I need to do is rank those students in C2, C6, B12 so on so forth.

Its taking a lot of time and I am not getting any result. I tried reading about rank but it tells about asc/desc order, which I dont understand how it fits in above example. I just need rank of a student at a particular cell.


View Replies!   View Related
Count Then Rank
I know I can go about this using a pivot table, but I would rather not. However, if it really is the best solution, then I’ll go that route.

************************************************************************>Microsoft Excel - School Cases - Assigned.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCDEFGHIJ1Case NumberOpened DateClosed DateAccount NameCase ReasonStatusSpecific issueOpenClosedAge (Days)2243301/02/0801/02/08Vincennes UniversityDisbursingClosedDisbursing Agent Process Issue0103243801/02/0801/02/08The Culinary Institute of AmericaDisbursingClosed-0104244801/02/0801/02/08Southern Methodist UniversityCertificationClosed-0105245101/02/0801/04/08Regent UniversityProcessingClosed-0126246001/03/0801/03/08Vincennes UniversityDisbursingClosedReturned Funds0107246901/03/0801/04/08Pennsylvania State University (The)DisbursingClosed-0118247001/03/0801/14/08Vincennes UniversityCertificationClosedAccount Issue01119247701/03/0801/04/08Michigan State UniversityDisbursingClosedLoan Level-Change amount01110249701/04/0801/04/08Illinois Institute of Technology - Main CampusProcessingClosedRequest for loan status change01011249801/04/0801/04/08Southern Methodist UniversityCertificationClosedDidn't receive01012251101/04/0801/07/08Rutgers UniversityDisbursingClosedLoan-Level Change date01313251301/04/0801/04/08Capella UniversityDisbursingClosed-01014251401/04/0801/07/08Saint Johns UniversityDisbursingClosedLoan-Level Change date01315251501/04/0801/07/08Regent UniversityDisbursingClosedLoan-Level Change date01316253301/07/0801/07/08Purdue UniversityDisbursingClosedLoan Level Check Status01017254301/07/0801/15/08Saint Johns UniversityProcessingClosed-01818254701/07/0801/08/08Saint Johns UniversityProcessingClosedRequest for loan status change01119256401/07/0801/18/08Clark Atlanta UniversityProfileClosedReceived revision to profile011120258101/08/0801/08/08Saint Johns UniversityDisbursingClosedLoan Level-Change amount010Jan [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.

What I basically need it to do is count the number of times a school is listed in column D then rank the top ten.

View Replies!   View Related
Rank Criterion
I deal with Income statements of varying length. What I'm trying to do is figure out a way to rank only the expenses.

The account names change from sheet to sheet, and the number of accounts always changes.

However, Expenses always START after a cell in the "A" column marked "OPERATING EXPENSES" and always ENDS after a cell in the "A" column marked "TOTAL OPERATING EXPENSES". However, I don't want to rank "TOTAL OPERATING EXPENSES" because it's the sum of all the indivicual expenses.

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved