Formula for Weighted Average

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Excel Misc
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - Hiding and Showing Sheets using Macro Hi Does any have any ideas on how to create a macro for this or if this is possible? - I want to have 1 workbook with about 13 spreadsheets in it. - When I open the workbook I only want to see "sheet 1" (with the other 12 spreadsheets hidden) - On sheet 1 i want to have 3 buttons, 1. Commodities 2. Forex 3. Options - When I click on the 'Commodities' button, I want spreadsheets 2,3,4 & 5 to show/be unhidden - I also want there to be a button on spreadsheet 2,3,4,&5 to say 'Back to Spreadsheet 1' and when clicked will navigate back to spreadsheet 1 and spreadsheets 2,3,4 & 5 hidden again (like when i first open the workbook) - When i click on the 'Forex' button, I want spreadsheet 6,7,8 & 9 to be shown/unhidden - I also want there to be a button on spreadsheet 6,7,8 & 9 to say 'Back to Spreadsheet 1' and when clicked will navigate back to spreadsheet 1 and spreadsheets 6,7,8 & 9 hidden again (like when i first open the workbook) - When i click on the 'Options' button, I want spreadsheet 10,11 & 12 to be shown/unhidden - I also want there to be a button on spreadsheet 10,11 & 12to say 'Back to Spreadsheet 1' and when clicked will navigate back to spreadsheet 1 and spreadsheets 10,11 & 12hidden again (like when i first open the workbook) I'm not sure if what i want to do is even possible and would really appreciate any help on how to do this or on a better way to do this. Many thanks in advance! Tag: Formula for Weighted Average Tag: 784233
  - 2
    - True/False for finding text in cells Is there a simple formula for checking if a cell contains a text string, which returns a TRUE/FALSE? It is possible to combine "ISERROR" and "FIND", but this becomes a pain when you have to do it all the time. Instead of using the reverse logic of: =if(iserror(find("text",A1))=FALSE,... it would be so much easier to write: =if(contains("text",A1),... This is a very basic thing you can do with an autofilter, so it seems like there would be a function that does this without returning error values. Any suggestions? Thank you. Tag: Formula for Weighted Average Tag: 784232
  - 3
    - formula to change back ground cell color according to a value I need a formula to change the back ground color of a cell according to a certain value Tag: Formula for Weighted Average Tag: 784231
  - 4
    - Using VBA to Create New Sheet That Consolidates Others Hi, there: Ron de Bruin's site provides the following wonderful code, which reads through all of the sheets in a workbook and then creates a new sheet that consolidates all of the rows on them into one new sheet (called "Summary," in this case). I've left his explanatory comments in: Sub BuildSummarySheet() Dim sh As Worksheet Dim DestSh As Worksheet Dim Last As Long Dim shLast As Long Dim CopyRng As Range Dim StartRow As Long With Application .ScreenUpdating = False .EnableEvents = False End With 'Delete the sheet "Summary" if it exist Application.DisplayAlerts = False On Error Resume Next ActiveWorkbook.Worksheets("Summary").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "Summary" Set DestSh = ActiveWorkbook.Worksheets.Add DestSh.Name = "Summary" 'Fill in the start row StartRow = 5 'loop through all worksheets and copy the data to the DestSh For Each sh In ActiveWorkbook.Worksheets 'Loop through all worksheets exept the RDBMerge worksheet and the 'Information worksheet, you can ad more sheets to the array if you want. If IsError(Application.Match(sh.Name, _ Array(DestSh.Name, "LOOKUPS"), 0)) Then 'Find the last row with data on the DestSh and sh Last = LastRow(DestSh) shLast = LastRow(sh) 'If sh is not empty and if the last row >= StartRow copy the CopyRng If shLast > 0 And shLast >= StartRow Then 'Set the range that you want to copy Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast)) 'Test if there enough rows in the DestSh to copy all the data If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then MsgBox "There are not enough rows in the Summary Sheet to continue." GoTo ExitTheSub End If 'This example copies values/formats, if you only want to copy the 'values or want to copy everything look below example 1 on this page CopyRng.Copy With DestSh.Cells(Last + 1, "A") .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False End With End If End If Next ExitTheSub: Application.GoTo DestSh.Cells(1) 'AutoFit the column width in the DestSh sheet DestSh.Columns.AutoFit With Application .ScreenUpdating = True .EnableEvents = True End With End Sub I'm not sure, however, how to include a head row on the newly-created summary. Rows 1 through 4 of all of my subsidiary sheets are identical. How can I tell the macro to copy and paste the heeads on the first iteration of the loop, but not thereafter? The subsidiary sheets are not named the same thing--and in fact, their names may change--but the column rows/heads will always be the same. Can anyone help? Thanks in advance! Tag: Formula for Weighted Average Tag: 784225
  - 5
    - Question to MailMerge expert Hi, I have been using MailMerge for some time, usually to get 1 Word page from each Excel record. Sometimes I use Skip a record if... when I want to skip some records. Now I have a new task and problem. Fields (columns) in an Excel sheet are grouped into 3 groups, each connected with different subject. Each subject must be merged into a separate Word page. However in some records all data for a subject can be equal to zero, so from 1 Excel record I can get 1 or 2 or 3 Word pages depending on the data. So the question is: how to get 1 or 2 or 3 Word pages from each Excel record using the MailMerge? I still use the MS Office 97 but if necessary I can install w newer version of the programs. I would be very grateful if someone could help me. Regards, Zoska Tag: Formula for Weighted Average Tag: 784216
  - 6
    - Auto calculate end date Good Day, I have a column with start dates and I want to have the column beside it display 5 years forward, e.g.: column A: 2008-05-11 , column B: 2013-05-11 How can I do this automatically? Thanks! Carol Tag: Formula for Weighted Average Tag: 784202
  - 7
    - issue.... hi guys , please i need your advice on this one : i have the following given : A B 1 max 10 2 ralph 20 3 max 30 4 max 40 5 ralph 50 i would like to have the related results of "max" and "ralph"...how can this be achieved ??? index(...,small(...) ) maybe ??? HOW ???? Tag: Formula for Weighted Average Tag: 784191
  - 8
    - Date in formula Hi All, I need your help in showing me how to put a date in the formula, for instance: =If(A2<4/01/2008,"old","ok") I know I can't just put a date there, I think there must be something at each ends of the date. Thanks. Tag: Formula for Weighted Average Tag: 784168
  - 9
    - How: automatically re-adjust number series I have the following number series: B1=1000 B2=1005 B3=1010 B4=1015 B5=1020 B6+1025 etc etc all in increments of 5. However, I have some completely random cells in the same column that I must leave blank. Since I used the fill handle to copy down the number series, how can I delete random cells and have the number series readjusted automatically e.g as follows: B1=1000 B2=blank B3=1005 B4=1010 B5=blank B6=1015 Greatly appreciate your help. Excel 2003. Tag: Formula for Weighted Average Tag: 784164
  - 10
    - Transpose column into rows for use as mailmerge data I have data in the following format and would like to convert into an Excel spreadsheet for use as mail merge database Mr Chris Acton ADH Services Ltd Unit 5 The Oaks Down End Crediton Devon EX17 1HN Mr Peter Alexander Mains Of Mause Blairgowrie Tayside PH10 6TE Mr James Anderson Bowmer & Kirkland Ltd High Edge Court Church Street Belper Derbyshire DE56 2BW If the addresses were only three lines then I could use the following method. However some addresses have 5, 6 or 7 lines. Can anybody help get it in the right format for a mailmerge? In B1 enter this formula =INDEX($A:$A,(ROWS($1:1)-1)*3+COLUMNS($A:A)) Drag/copy across to D1. Select B1:D1 and drag/copy down until zeros show up. Select columns B:D and copy. Edit>Paste Special(in place)>Values>OK>Esc Delete Column A -- Nash Tag: Formula for Weighted Average Tag: 784161
  - 11
    - Column with currency & text - how to format? Hi I have a column in a worksheet which has both currency & text 50.00 N.F.S. 90.00 90.00 N.F.S. N.F.S. 60.00 60.00 50.00 N.F.S. 120.00 which I use as the data source to make labels in Word The currency appears fine on the labels, however, the text come out as 0 & I have to manually correct each one. Can anyone tell me how to format this column please -- Martin ©¿©¬ Tag: Formula for Weighted Average Tag: 784160
  - 12
    - A Pivot question Hi, I have a pivot with two recurring lines : Budget & Actual. They are 2 values of the field. They are on top of eachother (as it's supposed to be). I would like to add another row beneath them that shows the subtraction between the two (doesn't matter which way). How do I do that within the pivot table ? Thanks, Assaf Tag: Formula for Weighted Average Tag: 784151
  - 13
    - Why does this macro crash? I have to deal with CSV files output by some commercial software which is not properly written. It gives long lists in which some of the rows have more columns than others. These lists can be thousands of lines long, so I wrote this macro, which moves the offending rows back to where they should be. First, I manually open up enough columns so that the rows can be moved to the left. Then I establish which is the rightmost column containing data, and I go to Row 1 and run the macro. It works fine, except that it crashes at row 65535, instead of simply ending because it has dealt with every cell in the column. I have highlighted where it crashes, and would appreciate anyone who knows why this happens. Sub Line_up_cols() ' ' Keyboard Shortcut: Ctrl+Shift+L ' Dim myRange As Range Dim Cell As Range For Each Cell In ActiveSheet.Columns() Selection.End(xlDown).Select Selection.End(xlToLeft).Select Set myRange = ActiveCell Range(Selection, Selection.End(xlToRight)).Select Selection.Cut myRange.Offset(0, -1).Select <<<<---- Crashes here ActiveSheet.Paste ActiveCell.Offset(0, 1).Select Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Select Next End Sub Tag: Formula for Weighted Average Tag: 784145
  - 14
    - formula Hi, I would like to create some formulas that regonises a word in the first column and then adds up the number across from it. Thanks.... -- MickM Tag: Formula for Weighted Average Tag: 784140
  - 15
    - drop downs Hi, I would like to create a list of drop downs in the first column of an excel spreadsheet. I would like input a list for the drop downs and also have the icon for the drop down visible in each and every cell. Thanks.... -- MickM Tag: Formula for Weighted Average Tag: 784138
  - 16
    - help.. i have the following given : A B 1 asih 234 2 aa 334 3 bb 434 4 asih 534 5 cc 634 6 dd 734 7 asih 834 i managed to do the following : {INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=C1,ROW($A$1:$A$7)),ROW(1:1)),2)} and i dragged it to cell C3. NOW..... my purpose for this is to be able TO enter the word "asih" in C1 and C2 and C3 in order to be able its coresponding data which are : 234 , 534 , 834 MY QUESTION NOW IS : what should i do to insert the following formula : index(....),match(....) ) WITHIN THE PRECEDENT FORMULA: {INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=C1,ROW($A$1:$A$7)),ROW(1:1)),2)} AND THAT IN ORDER TO GET FOR EXAMPLE (734) IF I HAD TO TYPE (DD).???? THANKS FOR YOUR HELP Tag: Formula for Weighted Average Tag: 784137
  - 17
    - Merging Workbooks Hi!..Does anyone have any ideas on how you would do the following? I want to have 1 main work book (workbook 1) with only one sheet. On that sheet I want to create 3 buttons. Button 1 creates a link/marco to open another workbook (workbook 2) automatically when the button is clicked, button 2 creates a link/marco to open workbook 3 automatically when button is clicked and button 3 creates link/marco to open workbook 4 automatticall when button is clicked Is this even possible? Many thanks for any help in advance! Tag: Formula for Weighted Average Tag: 784134
  - 18
    - assign values to specific cells in an excel sheet through text file Hi list I am very new to macros or any visual basic codes. Kindly guide me with materials or ideas to assign values to specific cells in an excel sheet. For example, I wish to assign values 1,2,3,4.... into A1, B1, C1, D1...... in Sheet1 of C:\data.xls. with thanks pushparaj -- s_pushparaj Tag: Formula for Weighted Average Tag: 784133
  - 19
    - Formula/Macro to place data on the next blank row Hi everyone, I'll be grateful if someone can help me set up a formula/macro through which data can be placed on the next blank row. My intention is to create a database of info and update the list sometime before I choose new dat (ie, the existing quotation details can be saved in the database before the new quotation is generated). Thanks. Tag: Formula for Weighted Average Tag: 784131
  - 20
    - saving excel file Hi, I have some formulas and data which is shared for the users to get their answers instantly. Is there any way not to allow users to save the excel shared file if they make any changes. Is it possible if the users close the file with some changes excel exit without prompting or saving??. i tried read only, protect sheet etc. its of no use. Thanks Kevin Tag: Formula for Weighted Average Tag: 784129
  - 21
    - Pulling data by start/finish dates I have a spreadsheet of work orders with start dates in column G and finish dates in column H. I would like to be able to pull all records between certain start and finish dates - this would change each month (ie all records between April 1 (start) and April 30 (finish) and place on a blank sheet. I want to be prompted to enter the dates. How would I do this? Tag: Formula for Weighted Average Tag: 784124
  - 22
    - Min Value Here is an equation I'm using to calculate intersection sight distance: ISD = 1.47 (Vmajor) [T + (G - 3%)0.2 + (W/12)0.5] Each variable will be a cell, for instance, G could be B12. When G is less that 3%, I don't want it to do that part of the equation. The actual value of G could be 2.57%, but when it is, I don't it to "subtract 3%, then mult. by .2 In the EQUATION, i want that term, (G - 3%)0.2, to be a minimum of zero. See, when G is less than 3%, that whole term is negative, and i don't want that. Can anyone help me think of a way to not do that part of the equation when G < 3%? Is there some Excel function I'm not thinking of? I'm trying to avoid tons of nested IF statements. Much appreciated, dk Tag: Formula for Weighted Average Tag: 784119
  - 23
    - Space in text when I copy text from on sheet to anoth, the destination after the paste has one space in front of the text. how do i remove the space from multiple cells at the same time Tag: Formula for Weighted Average Tag: 784102
  - 24
    - is statement want to do this in a formular but dont know how: if the text in sheet 1 cell c2 = the text in sheet 2 column d, then return text in sheet 2 column e associated with text in column d. I've tried using vlookup, it works with some of the sheets but not all of them. I have to continiously copy text between the two sheets for it to work. Please help. Tag: Formula for Weighted Average Tag: 784100
  - 25
    - VLOOKUP Combining 2 Reports. How would I combine these two reports together using 05? Report 1 (Sales report) Item # Description 1 Qty Sold Publisher 12 360 BLAZING ANGELS 1 UBISOFT 25 360 CIVIL WAR 1 ACTIVISION 43 360 ELDER SCROLLS IV 1 TAKE 2 71 360 GHOST RECON AW 2 1 UBISOFT 78 360 HARDWARE ARCADE 1 MICROSOFT 91 360 LIVE 1 MONTH CARD 1 MICROSOFT 94 360 LIVE 3 MONTH CARD 1 MICROSOFT 97 360 LIVE POINTS 1600 3 MICROSOFT Report 2 (Qty On-hand) Item # Description 1 On-Hand Publisher 12 360 BLAZING ANGELS 6 UBISOFT 25 360 CIVIL WAR 2 ACTIVISION 43 360 ELDER SCROLLS IV 4 TAKE 2 71 360 GHOST RECON AW 2 3 UBISOFT I just need to add the on-hand qty from report 2 to report 1. Report one only generates items that have sold which is normally several hundred items. Report 2 will generate the entire inventory database which is more than 10,000 items. Do I have to use item # to use VLOOKUP? If Column A was a UPC # or Description instead of item # how would I use VLOOKUP? Tag: Formula for Weighted Average Tag: 784099
- Next
  - 1
    - If statement in macro Hi I want the macro to type in column on "c" the status "On Time" or Delayed" depending of the following criteria: - if A= MTL and B = 2 then C= "on Time" - if A<> ATLN OR VAN and B =1 then C ="on time" - if A=ATLN ot VAN then C="out of ON PU" - Else "Delayed" A B c MTL 2 ATLN 5 VAN 7 LOND 1 I wrote the below proceduce but there is an error. Could anyone help me to write this procedure properly. Thanks in advance. Sub Job() Range ("C1).Select ActiveCell.Value = "=if ActiveCell.Offset(0,-2)= "MONT" and ActiveCell.Offset(0,-1)=2 then Active.Cell.Value="On Time" Else if ActiveCell.Offset(0,-2) <> "ATLN" or If ActiveCell.Offset(-2,0)<> "VAN" and ActiveCell.Offset(0,-1)=1 then ActiveCell.Offset.Value="On Time" Else if ActiveCell.Offset(0,-2) = "ATLN" or If ActiveCell.Offset(0,-2)= "VAN" then ActiveCell.Value="Out of ON PU" Else ActiveCell.Value="Delayed" End if End () Orquidez Tag: Formula for Weighted Average Tag: 784098
  - 2
    - How do I separate numbers? I have a web query which gives me the result "385/1700" and all I want is the first part "385" to allow me to do calculations with, but because it's a web query, it wont let me just format it as a fraction and times it by 1700.. it comes up with the #VALUE! error. So I dont know what else to try, Can anyone help? Thanks, Aden Tag: Formula for Weighted Average Tag: 784096
  - 3
    - Automatic Display of ApproveIt Menu upon Excel Opening How do I eliminate the ApproveIt Menu from automatically appearing every time I open Excel? -- Bonnie Tag: Formula for Weighted Average Tag: 784095
  - 4
    - is this filter possible? I have a list of customers on sheet 1 cell A1. I need a filter that will display specific cells for specific customers. For instance, when the list in A1 reads ABC Lumber Co., I would like to see cells columns A through F lines 601 through 622 directly below the list cell. Is this possible? -- bob Tag: Formula for Weighted Average Tag: 784085
  - 5
    - calculate moles in excel HELP! How in Excel do I work out how many moles are in 1kg of water. Desperately need an answer for Monday morning. :) Tag: Formula for Weighted Average Tag: 784083
  - 6
    - Disabling Automatic Date Thing When i type '5-10' for example, and then press enter or leave that box, the '5-10' i typed automatically changes to '05-Oct'. It's really irritating and it happens with others, such as '10-15', basically anything that looks like a date. How do I turn this off? Tag: Formula for Weighted Average Tag: 784079
  - 7
    - Disabling Automatic Date Thing When i type '5-10' for example, and then press enter or leave that box, the '5-10' i typed automatically changes to '05-Oct'. It's really irritating and it happens with others, such as '10-15', basically anything that looks like a date. How do I turn this off? Tag: Formula for Weighted Average Tag: 784078
  - 8
    - Row-Entire row changes color (and stays) selecting first entry I have a sheet with 63 rows and 30 columns. I would like the row to change color and stay as I slide across columns. Freeze panes works almost but to change color would be better. Tag: Formula for Weighted Average Tag: 784077
  - 9
    - Numeric formatting Hi, I need to format numbers as 10E-3, 10E-5, or 10E-6. I need to maintain the 10E-x format. Not scientific or engineering notation. It's for recording Bit Error Rate measurement results. Any ideas? Thank you for your help. Tag: Formula for Weighted Average Tag: 784074
  - 10
    - Excel "If" Function formula Hi I need some help figuring out if it is possible to create a formula. I'm trying to link information from different pages in a spreadsheet that contain id's for people and prices they are paying for some items my church is selling. We have a main worksheet that lists their names and the ids and then we have another worksheet that lists all the items they can buy with it's price. Is it possible to create a formula in the name page that says "If you find id #1 in column A, then sum the prices from column D". The ids will be spread out throughout column A so we need a formula to locate each instance of the id and then grab the price listed each time in column D and sum those prices up. Is this possible and if yes can someone tell me how to do it. Thanks Tag: Formula for Weighted Average Tag: 784068
  - 11
    - Min Sumproduct formula returns a zero. I have a spreadsheet. Which can be viewed on my public box link http://www.box.net/shared/movk8u3cws My headings are A3 Date, B3 Name, C3 Contribution E3 12/08/07 Date=A3 Name=B3 Contribution=C3 12/8/2007=E3 Andy=F3 12/8/2007 Andy 1 12/8/2007 Andy 40 12/8/2007 Andy 3 12/8/2007 Andy 4 Date=A11 Name=B11 Contribution=C11 12/8/2007=E11 Andy=F11 12/8/2007 Andy 1 12/8/2007 Andy 40 12/7/2007 Andy 3 12/8/2007 Andy 4 In E3, I entered 12/08/07 and in F3 I entered Andy. I want my formula in J11 which is =SUMPRODUCT(MIN((B6:B9=F5)*(C6:C9=G5)*(D6:D9))) to return the minimum contribution that Andy made on 12/08/07. When I substitute Min for Max, the formula works with no problems at all. As soon as I switch to Min the formula returns a zero. It will work if all the dates and all the names are the same. But as soon as the Date or Name changes I get a zero fpr the Min formula. I even entered it as an array but it won't work. Tag: Formula for Weighted Average Tag: 784062
  - 12
    - vba programming in excel 2007 how i can use vba programming in excel 2007 to sort in column in ascending order give me reply as a i am beginner. Tag: Formula for Weighted Average Tag: 784058
  - 13
    - Count after given condition is met Hi all would like your help for the following Need to count which and how many numbers appear after a given number ie After After After After After After 1 2 3 4 5 6 1 2 1 1 3 1 1 1 1 4 1 2 3 1 1 5 2 1 6 1 this was obtained from data on column c, that is displayed as follows: 3 5 4 5 3 4 4 2 4 3 4 4 3 5 6 4 4 3 3 4 2 3 2 Tks in advance Antonio Tag: Formula for Weighted Average Tag: 784053
  - 14
    - Excel - how to autofill thousands of cells quickly I have a spreadsheet with 35000 rows. Is there an easy way to autofill a formula for all of the rows instead of dragging down all 35000 rows? Tag: Formula for Weighted Average Tag: 784040
  - 15
    - Pivot Table % Calculation Issue I am bringing in the data below from SQL Server. I cannot add a calculated field because it is removed when it is refreshed. The issue is with the SUM of the GP% ((GP $/Sales)*100). I manually added another column to the right for only the ones that are wrong and what it needs to be. Spent too many hours trying to resolve this one. Data Store Order No Description Sales GP $ GP% GP% Needs to be BR 00000231 SPRING RED 11.99 6.49 54.13 00000231 Total 11.99 6.49 54.13 00000232 SPRING RED 11.99 6.49 54.13 00000232 Total 11.99 6.49 54.13 00000233 BALL RACK 49.99 24.99 49.99 00000233 Total 49.99 24.99 49.99 00000234 BLUE PUP 39.99 19.49 48.74 00000234 Total 39.99 19.49 48.74 00000236 BLUE PUP 39.99 19.49 48.74 SPRING RED 11.99 6.49 54.13 SPRING YEL 11.99 6.49 54.13 00000236 Total 63.97 32.47 156.99 50.76 00000237 READ RACK 14.99 7.49 49.97 BLUE PUP 39.99 19.49 48.74 SPRING RED 11.99 6.49 54.13 00000237 Total 66.97 33.47 152.83 49.98 00000238 BALL RACK 49.99 24.99 49.99 00000238 Total 49.99 24.99 49.99 00000239 SPRING YEL 11.99 6.49 54.13 00000239 Total 11.99 6.49 54.13 BR Total 306.88 154.88 620.93 50.47 Grand Total 306.88 154.88 620.93 50.47 Any suggestions would be greatly appreciated. Jeannette Tag: Formula for Weighted Average Tag: 784035
  - 16
    - comparing from two worksheets Hi all, I am trying to compare from two worksheets. What I have is the inventory that my headoffice is telling me I have and the other is the actual inventory I have I want to compare the two by product number and discription giving the head office total per item, my total per item and the difference between the two. I have a general knowledge about this but I thinking its over my head. Help required. Thanks Tag: Formula for Weighted Average Tag: 784032
  - 17
    - display specific next highest value from a column containing multi Just cant seem to get it. I am working with data in F10:F100 that contains numbers like (1,2,3,4) as well as lettered numbers like (C1,C2,C3,C4). I can not come up with a formula that will index this column and return the next highest C number. F9 needs to display the next highest C number. I can get this to work with the standard numbers only (1,2,3,4). Any suggestions Tag: Formula for Weighted Average Tag: 784030
  - 18
    - e mail same body different subject Hello I copied the code below. Now is it possible to change the line Subj = "Your Annual Bonus" Here is the thing, the body of the e mail will be the same for everyone but the subject will be different. How do I do that? thanks Sub SendEmail() Dim cell As Range Dim Subj As String Dim EmailAddr As String Dim Recipient As String Dim Bonus As String Dim Msg As String Dim HLink As String For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants) If cell.Value Like "*@*" Then 'Get the data Subj = "Your Annual Bonus" Recipient = cell.Offset(0, -1).Value EmailAddr = cell.Value Bonus = Format(cell.Offset(0, 1).Value, "$0,000.")V Tag: Formula for Weighted Average Tag: 784025
  - 19
    - working with graphics and formating them how to create amortization tables Tag: Formula for Weighted Average Tag: 784022
  - 20
    - Validation malfunction I'm using Excel 2004 11.4.1 on Mac OSX 10.4.11. On a page with several columns of cell validations, randomly, one cell will control the input to a different cell or shows a double pull down arrow on the right and will not clear or delete. What's going on? Is there a fix for the software and the affected files? Tag: Formula for Weighted Average Tag: 784021
  - 21
    - Customize a macro button I would like to create my own symbols so that when I add a macro button to the command line it's easier for me to understand. Is there a way that I can create my own symbol so that it's available in the modify button dialog box? Tag: Formula for Weighted Average Tag: 784016
  - 22
    - Where did MS hide it in 2007 ?? In 2003 -- View > Toolbars > FORMS Where is the above in Office 2007? Bob Tag: Formula for Weighted Average Tag: 784012
  - 23
    - Compatibility Checker Where is the "FIND" in the Compatibility Checker. Some of the formulas in this workbook are linked to other workbooks that are closed. When these formulas are recalculated in earlier versions of excel without opening the linked workbook, character beyond the 255 character limit can not be returned. The help portion of excel instructs to do the following to resolve! In the Compatibility Checker, click Find to locate cells that contain array formulas that refer to another worksheet, and then make the necessary changes to avoid #VALUE! errors. Can anyone walk me throught the steps so that I can correct and remove the links - how do I find where the links are> Tag: Formula for Weighted Average Tag: 784010
  - 24
    - Deleting and Copying Marco code Hi, Can any one please help me with writing a code in visual basics? What i am trying to do is this: I want to create a button called 'close' in row 1(sheet 1) and when I click this I want a marco to run which will delete the entire row that the button is on and then paste that row into row 5 in worksheet 2. I want to repeat this for the next row however, when the second row is pasted into worksheet 2 i want the second row to be pasted in row 5 and the first row move down to row 6... I hope this makes sense and would really appreciate any assistance from anyone as i am not to familiar with writing up codes...THANK YOU in advance! Tag: Formula for Weighted Average Tag: 784007
  - 25
    - Help, please Worksheet is sorted by dates in Col. A. Dates have varying number of rows; i.e., 5-6-08 may have 29 rows of data; 5-7-08 may have 14 rows; and 5-8-08 may have 42 rows. How can I automatically transfer to another worksheet the values in the last row for a date? Tag: Formula for Weighted Average Tag: 784004

Is there a general formula for Weighted average? I have a row of numerical
data, from 1 to 5. If it's 1, the weight is 0%, 2 - 25%, 3 - 50%, 4 - 75%, 5
- 100%. Is there a formula which I can do this?

Thanks

RE: Formula for Weighted Average by Joel

Joel
Sun May 11 20:55:00 CDT 2008

In this case I would use sumif and count as shown below:

=((0.25*SUMIF(A1:A100,2))+(0.5*SUMIF(A1:A100,3))+(0.75*SUMIF(A1:A100,4))+(1*SUMIF(A1:A100,5)))/COUNT(A1:A100)

"MJ" wrote:

> Is there a general formula for Weighted average? I have a row of numerical
> data, from 1 to 5. If it's 1, the weight is 0%, 2 - 25%, 3 - 50%, 4 - 75%, 5
> - 100%. Is there a formula which I can do this?
>
> Thanks

Re: Formula for Weighted Average by Earl

Earl
Sun May 11 22:08:45 CDT 2008

MJ,

I think you want 100% if they're all 5s, 25% if they're all 2's, and stuff like that. This
will treat the 1s as 0, but will still count them in the average, which I think you want.
That is, it won't ignore them. Empty cells, or cells with 0 will change the result.

=(AVERAGE(A4:E4-1))/4

It's entered as an array formula -- press Ctrl - Shift - Enter, not just Entter, any time
you've edited it. Format for %.

If that don't get it, give examples of some data, and the expected result.
--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"MJ" <MJ@discussions.microsoft.com> wrote in message
news:EAF45EA9-E801-4A05-9A34-D682BDE8C963@microsoft.com...
> Is there a general formula for Weighted average? I have a row of numerical
> data, from 1 to 5. If it's 1, the weight is 0%, 2 - 25%, 3 - 50%, 4 - 75%, 5
> - 100%. Is there a formula which I can do this?
>
> Thanks