Formula for calculating positions

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Excel
- 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
    - Formula "Let me know if this does not make sence". I have listed everything on my web page www.mykaltx.com I have posted my information on my web site for anyone who would like to help me. I hope this will clarify what I am looking for. The sheet labeled ?sheet 2? is my database. If I type the number of peaces I want in the column D14 and the ft in Q14 and inches in S14 the formula will pull the data off of the database for G14. All cells will work together to calculate the weight for the various steel items. This is great for our company. Now I would like to modify this. I will also need it to work for steel plate. The weight of steel is 2836 per sq inch. The formula to get volume is WxHxL. As I have listed in the spreadsheet the height will go with the plate ?PL 3/16? this will be the height. The width will be the second dimension listed next to the height 15 which will be the width. The length will be listed in the ft and inches field. As you can see in my database called sheet two. The formula pulls off of the database to calculate the weight. The problem is it calculates a square for plate. Sometimes the plate will be rectangular. I have listed a second spreadsheet that is highlighted with baby blue at the time and is labeled estimate. This has a formula similar to what I am looking for. I do not like it though. It will not pull off of my database. -- mykaltx ------------------------------------------------------------------------ mykaltx's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=22055 View this thread: http://www.excelforum.com/showthread.php?threadid=393339 Tag: Formula for calculating positions Tag: 68631
  - 2
    - excel keys Please help, when I type the letter r in a cell....up comes RUN, when type S in a cel up comes search. have i pressed a short cut somewher please. cant find the answe -- john_ ----------------------------------------------------------------------- john_c's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2596 View this thread: http://www.excelforum.com/showthread.php?threadid=39332 Tag: Formula for calculating positions Tag: 68630
  - 3
    - Macro to insert to move values of a cell to next cell in the same row Hello, I would like to know the Macro code for Moving the Values of a cell to next cell based upon values in a particular cell. Ex:- Col A(DATE) Col B(0-30 Days) Col C(30-60days) ColD(60-90day) Jan-05 10 Feb-05 10 Based upon date in Col A i want move the value in col b and c to the next cell.Basically this for aging the items in the work sheet Tag: Formula for calculating positions Tag: 68628
  - 4
    - exporting to SPSS There have been a lot of questions about this, but I need to get excel data into SPSS --IN A PARTICULAR FORMAT-- because I will be appending it to an existing master dbase that has variables defined already. I can make SPSS read my excel data, I just can't make it read as string vs. numerical. Does excel have a function where I can define value labels ie., male=1, female=2 so that if the string in excel is 'male' then SPSS will read it as '1'??? Tag: Formula for calculating positions Tag: 68626
  - 5
    - how to delete source file? An excel spreadsheet links to another excel file. However Iwant to remove this link completely since the file does not exist anymore. How do I remove it? I know that by going into the spread sheet and doing a find ! I get all the cells that call to this external file. If I delete them all (and there are lots) will I still get the prompt when opening the spreadsheet that it can't find this source File? How shall I best handle this situation? Can I simply remove the source link somehow? I don't get that option (rather, open source, change source). TIA, Brad Tag: Formula for calculating positions Tag: 68625
  - 6
    - How can I use range objects to solve this problem Hi all, I have couple of tables that can have many number of rows. The number of rows can vary depending on the number of items that I need to put into each table. The last row of the table is always will be "Total and some number". The work sheet contains may items and the above tables. The number of tables could be anywhere from 1-5. I am trying to generate a vba code that can copy only the tables from this worksheet and then assort all the tables to new a worksheet with an empty row in between the tables. Is there a way to use range objects to get this done? How can we specify a range object that is between the start of the table to the end of the table? Thanks in advance for all the suggestions. shi Tag: Formula for calculating positions Tag: 68624
  - 7
    - Passing Strucutres from VBA to DLL and back Hi, I need to pass a matrix type data structure between Excel VBA and a external DLL (XLL actually). Because of the bug in Excel, where a call t Application.Evaluate("MyXLLFunction()") will actually call my pro twice, I want to be able to declare the external method in VBA an call it, without using Evaluate. This is easy enough with simple types, but I want to be able to retur a more complex data type back and forth, one similar to the Excel SDK' Multi type, where you can return multiple rows of multiple columns. Does anyone know the best way of doing this? Thanks Mike Davie -- mikedu ----------------------------------------------------------------------- mikeduk's Profile: http://www.hightechtalks.com/m2 View this thread: http://www.hightechtalks.com/t220627 Tag: Formula for calculating positions Tag: 68622
  - 8
    - problem when linking to another excel document ... Im' in excel 2003, with all office updates installed. When I create a new (or existing) excel sheet, I try to add a hyperlink to a regular word like 'test' which is typed into a cell. This works when I link to a txt file, but excel crashes when I link to a xls file!! It does work when i DONT save, but when I save the document, I can't click the link anymore .... . sometimes excel saves the link like this: \\server\Data\data.xls or sometimes it saves the link like this: ..\..\data.xls both ways excel crashes. I tested this on 3 pc's on dofferent locations/companys. Anybody knows a solution?? Tag: Formula for calculating positions Tag: 68619
  - 9
    - FIle Path What is the VBA code to return a currently open workbook's directory? Manu Thanks Tag: Formula for calculating positions Tag: 68616
  - 10
    - blank cells v. zeros Hi- I have a database full of different tables on each worksheet. On eac table, I input test scores and then run my macro. The test scores go t the last worksheet labeled 'data'. My problem is when I leave a tes blank on the table (when there is no score for that specific test), zero appears under that test in 'data'. I tried turning off zero value but that got rid of every zero, even the ones that actually represent test score. To make a long story short, I need blank cells to sta blank and cells with zeros to stay zero. If anyone can help me, i would be much appreciated! -Jessic -- JessN ----------------------------------------------------------------------- JessNY's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2522 View this thread: http://www.excelforum.com/showthread.php?threadid=39328 Tag: Formula for calculating positions Tag: 68615
  - 11
    - Excel 2002 SP1 We use Excel 2002 SP1 to open workbook(s). The PC is a GX270, PIV 3GHz, 1GB RAM. There is a seperate hard drive for storing Excel files. Each workbook is anywhere between 80MB and 140MB. We are able to open 1 130MB workbook without getting an error. If we open two workbooks of about 130MB, Excel gives an error: -- Excel cannot complete this task with available resources. Choose less data or close other applications. -- These workbooks do NOT have Pivot Tables or VLookups. They DO have formula calculations within tabs (which are individual spreadsheets) in the same workbook. What is the best way to get atleast two workbooks open at the same time? Tag: Formula for calculating positions Tag: 68613
  - 12
    - Intro to Visual Basic Hi! Although I am a quit experienced programmer and used to use several programming languages such as C, C++, Basic, Perl, Fortan, LISP, PROLOG, etc., I do not know how to make use of Visual Basic in EXEL. I am thus looking for a quick intro. Any pointers? Tag: Formula for calculating positions Tag: 68604
  - 13
    - Shame Question on copying lines after the next line I did forgot even the vert basics, so please excuse the next question which is very easy or so I think. I have a sheet. If one criteria is met, I copy the line, and paste it in another sheet, after the last line already copied. Up to now my code is. If Len(Range("b" & i)) = 79 Then Range("A" & i, "D" & i).Select Selection.Copy Sheets("Exports").Select Range("A1").Select Selection.End(xlDown).Select Selection.End(xlDown).Select Selection.End(xlUp).Select ActiveSheet.Paste Range("A1").Select Sheets("EasyScreen Datas").Select A big part of that is useless, I realize, and moreover, I am only crashing the previous line as I copy the new one on the same place. After: If Len(Range("b" & i)) = 79 Then Range("A" & i, "D" & i).Select Selection.Copy Sheets("Exports").Select Range("A1").Select What should I do... I know it is not a sophisticated question ... Tag: Formula for calculating positions Tag: 68595
  - 14
    - Right click dialog Morning All, I've just had a thought and wondered if it was possible. Is there a way I can add in functions to the right click dialog you get when you right click on a cell in Excel ? I was wondering if I could add in things like Freeze Pane or Centre Align etc. Anyone know if this is possible? Regards Adam Office 2003 Professional - Windows XP Pro SP2 Tag: Formula for calculating positions Tag: 68593
  - 15
    - Run a macro x time in a row Hello there, another dumb question. I want to run the macro x times in a row, lets say 10 times for instance, what code should I add ? Thanks a lot for your help Daniel Tag: Formula for calculating positions Tag: 68589
  - 16
    - Pivot Tables with OLAP Cube - Dimension Properties Excel 2K Hi! I use Excel Pivot Tables as OLAP Cube Browser, for cubes which are on an SQL Server Analysis Services Server. This works fine and I'm happy with it, but some of my cubes contain dimension properties, that only Excel 2003 can show, and my company's unfortunately working with Office 2000, Excel 2000 doesn't handle the dimension properties. Does anyone know an Add-in or something I could install for Excel 2000 to access the dimension properties as well? Or Does anyone know a free asp web cube browser such as http://www.microsoft.com/downloads/thankyou.aspx?familyId=d13da3cd-d84e-45a1-8f12-3bfb326ea53b&displayLang=en (ASP, not ASPX if possible) but a little more enhanced? (with dimension properties handling etc.) ? Or A version of Office web components that wouldn't need Office 2003? (Not sure if it solves the problem though as I don't know OWC) Thank you very much! Tag: Formula for calculating positions Tag: 68588
  - 17
    - Moving Objects I have a picture and an object (Line) that I have placed on top of chart area. After I do work on other sheets in the workbook and retur to the sheet with the objects, they sometimes move slightly from where placed them. How can I get them to stay put. I have tried to lock an protect them, but they still move. Not everytime, but often -- icetre ----------------------------------------------------------------------- icetrey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2032 View this thread: http://www.excelforum.com/showthread.php?threadid=39320 Tag: Formula for calculating positions Tag: 68584
  - 18
    - How can I include a workbook name when specifying a Range in VBA code? How can I include a workbook name when specifying a Range in VBA code? Thanks, Keith Tag: Formula for calculating positions Tag: 68579
  - 19
    - page setup - paper size - legal? Hi, I have a landscape worksheet whose Paper Size I'm trying to make legal. My paper size drop down menu is not offering me the "legal" paper option. The worksheet is certainly long enough to warrant it as it spills onto multiple pages. Thx. -- sflin ------------------------------------------------------------------------ sflin's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=13780 View this thread: http://www.excelforum.com/showthread.php?threadid=393165 Tag: Formula for calculating positions Tag: 68575
  - 20
    - How to name a range in a different workbook? How can I name a range that exists in a different workbook? Thanks, Keith Tag: Formula for calculating positions Tag: 68561
  - 21
    - [Fwd: Run macro on exit worksheet/workbook] -------- Original Message -------- Subject: Run macro on exit worksheet/workbook Date: Tue, 02 Aug 2005 22:47:57 +0200 From: toontje Organization: Versatel Nederland B.V. Newsgroups: microsoft.public.excel.worksheet.functions I am looking for a macro that opens a msgbox when exit the workbook to remind you like "you fill in your working hours?" is that Private Sub AutoClose() ? and where do i put it? Tag: Formula for calculating positions Tag: 68560
  - 22
    - Counting unique values with variables I have a similar question, except, I need to count unique instances where there is another variable. Sample Data: Program Student University 1 John North University 1 Steven North University 1 James South University 2 Susie Case University 2 Laura West University 2 Lisa North University So, in this sample data, I would need to count the number of unique universities per program, that is, Program 1 - 2 univeristies, program 2 - 3 universities. Additionally, I need to ensure that the formula updates upon opening, since the data set is in another sheet, and will be updated frequently. Tag: Formula for calculating positions Tag: 68555
  - 23
    - Spin Buttons and Percentages When setting up a spin button, is it possible to use something other than whole numbers? I want to use between 60% and 100% and move by 1% increments, but it seems only to let me use the numbers 60, 100 and 1. When I key in percentages, it rejects it. Is it possible to do that? Thanks! Tag: Formula for calculating positions Tag: 68554
  - 24
    - multiple program windows? How can I enable a second excel program window to view on a secon monitor connected to a laptop -- tafstewar ----------------------------------------------------------------------- tafsteward's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2593 View this thread: http://www.excelforum.com/showthread.php?threadid=39304 Tag: Formula for calculating positions Tag: 68549
  - 25
    - sorting text & numbers I have a large list that I want sorted like this: Ward 1 Ward 2 & 3 Ward 4 Ward 5 Wards 6-8 Ward 9 Ward 10 When I sort it, it says: Ward 1 Ward 10 Ward 2 & 3 Ward 4 Ward 5 Wards 6-8 Ward 9 If I just take out the words Ward and Wards, it makes some of the numbers into dates, and does something else to some other cells that returns a big number. Anything I can do? -- billjr ------------------------------------------------------------------------ billjr's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25927 View this thread: http://www.excelforum.com/showthread.php?threadid=393020 Tag: Formula for calculating positions Tag: 68537
- Next
  - 1
    - How to use count with multiple conditions I have a table in Excel: The first row is time in years. The second row is method name,say,"A","B","C". I want to count the number when the time is less than 5 years AND "A" method is adopted. I tried this: count(if(AND(C2:Z2<5,C3:Z3="A"),C2:Z2) but it didn't work. Any suggestion on how to revise the formula? Thanks! In the mean time, count(if(C2:Z2<5,C2:Z2))worked as well as countif(C2:Z2,"<5") Ming Tag: Formula for calculating positions Tag: 68527
  - 2
    - Thanks Bernie & another question Your solution worked great! I'm now trying to come up with a spreadsheet as follows. Say I have a group of listings of parts we call assemblies in column B. These groups would each have a different name such as group1 etc. Each assembly would consist of 4 different parts in column C. What I would like to do is enter "1" for assembly 1 or "2" for assembly 2 and have excel find the 4 different parts for this assembly and bring them into cells d4 thru d8 along with their costs into column e. I've been trying different functions for this but just can't seem to get it. Thanks! Tag: Formula for calculating positions Tag: 68522
  - 3
    - Protecting sheet disables Grouping buttons I am delivering a worksheet to a client, and have protected it. As well as stopping people changing the data, though, it also stops them using the Grouping buttons. How can I enable grouping on a protected sheet? Tag: Formula for calculating positions Tag: 68521
  - 4
    - HorizontalAlignment Error in Excel 97 Wrong forum section, thread to be deleted by a moderator thx -- Werner ------------------------------------------------------------------------ Werner's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24304 View this thread: http://www.excelforum.com/showthread.php?threadid=392942 Tag: Formula for calculating positions Tag: 68519
  - 5
    - Name, Date and Address Formats Hi, I have a number of different spreadsheets that I need to impirt into a single database. Unfortunately some of the information is in different formats. Would anyone be able to provide me with a solution that can turn: A B Brown ABC Brown *A B C*Brown* Brown, A B Brown, Anthony Bernard Brown Anthony Bernard Mr A B Bernard Into separate fields for title, forename, initials & surname On a similar theme I also need to do much the same thing with addresses: 1 Victoria Road, Victoria, Victoriashire, VC1 1VC etc And lastly (for now anyway) I also have some dates that I need to convert from: 01012004 (dd/mm/yyyy) 112004 (d/m/yyyy) 20040101 (yyyy/mm/dd) 200411 (yyyy/m/d) to 01/01/2004 (dd/mm/yyyy) Hope someone can help cheers Tag: Formula for calculating positions Tag: 68516
  - 6
    - How to create a reference using VBA to a source list in different Workbook? Can someone point me in the right direction? I need to perform the the equivalent of the following steps using VBA code instead of manual entry. Create a Reference to the Source List 1.. Open the workbook in which you wish to use the list in Data Validation. 2.. Choose Insert>Name>Define 3.. Type a name for the List, e.g. MyList 4.. In the refers to box, type a reference to the named range. Start with an equal sign, then the workbook name and an exclamation mark, followed by the range name, e.g. =DataValWb.xls!CustName 5.. Click OK Thanks, Keith Tag: Formula for calculating positions Tag: 68512
  - 7
    - reading from excel into a webpage I've been asked to create a webpage that reads certain cells from an excel spreadsheet. Is this possible? -- karambos ------------------------------------------------------------------------ karambos's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=16262 View this thread: http://www.excelforum.com/showthread.php?threadid=392892 Tag: Formula for calculating positions Tag: 68508
  - 8
    - Extending Excel with more formulas Subject: Extending Excel with more formulas Hi, Is there a way to extend Excel foruma base to include more formulas? Thanks, DDUP. Tag: Formula for calculating positions Tag: 68507
  - 9
    - Excel 2003 Our company has just upgraded us to XP, along with that we now are usin Office 2003. I like some of the new Excel error tracking, but am havin recurring problems with a couple of other aspects. The first is - having set the toolbars with all the things I like t have to hand - when opening some sheets I suddenly get WEB an REVIEWING toolbars popping up - not only do these reduce the amount o screen when closing said sheets they remain - WHY DO THEY APPEAR? If right click and uncheck the box they go, but not permanently. The other annoyance is I use CTRL+INSERT to copy then ENTER to past after a couple of goes the stupid clipboard appears hiding everything go to options and uncheck everything and start cutting and pasting an the hideous thing pops up again. How do I permanently stop these two infuriating things from happening -- philiphale ----------------------------------------------------------------------- philiphales's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1864 View this thread: http://www.excelforum.com/showthread.php?threadid=39288 Tag: Formula for calculating positions Tag: 68505
  - 10
    - Lookup question I have a large spreadsheet which is not a table or database (lot of blank lines). As an example of my problem, in row 500, column F I may have a formula such as =F200. In column C of row 500 I would like to pull the data from column A of row 200. I need the formula in C500 to recognize the reference from F500 and pull the associated info from column A. Tag: Formula for calculating positions Tag: 68501
  - 11
    - isolate number from string of text I have output data from a program, .txt files, with multiple lines of text with one or two numbers on each line. The number I want to isolate is always in-between a descriptor, like x or y, then a units descriptor at the end, like lbm^2. Here are some example lines from the output file. I 676158.2296 lbm in^2 Axis X 0.9881 in Y -0.0059 in Z -0.1538 in The numbers do have a variable number of decimal places, like 0.9881 or 0.99, so the number of characters in each line can vary. Is there a way to have excel isolate the numbers that have the decimal in them? Thanks, Stephen R. Tag: Formula for calculating positions Tag: 68497
  - 12
    - Pop up message if marcos are disabled Hi All, Is there anyway to force a simple pop up message every time particular spreadsheet is opened ONLY IF the user opening it has macro disabled? The pop up message needs to be of the form... 'You have macros disabled' [with one 'OK' button] Any help would be greatly appreciated CHEERS The Grinc -- The Grinc ----------------------------------------------------------------------- The Grinch's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=244 View this thread: http://www.excelforum.com/showthread.php?threadid=39286 Tag: Formula for calculating positions Tag: 68492
  - 13
    - Marcos and Validations I am trying to create a spreadsheet with a validation table in it tha when I choose one of the items in it, it will run a macro to fin certian info in a seperate table below. I have put the autofilter on the second table but can't work out how t link the macro and the validation table. Someone please help!!!!!!! Regards And -- Andy Whittake ----------------------------------------------------------------------- Andy Whittaker's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2590 View this thread: http://www.excelforum.com/showthread.php?threadid=39286 Tag: Formula for calculating positions Tag: 68491
  - 14
    - Spreadsheet Reference Hello I have 30 different spreadsheets named 01, 02, 03, 04?30 an Excel workbook. All the sheets have references to the previous sheet. Is there any way I can refer the cell values of the previous sheet using a general formula other than individually linking each cell to the values previous sheet? Any information in this regard will be of great help to me as that will cut a ton of my work. My thanks in advance to any one who is trying to help me in this regard. Raja -- Raja ------------------------------------------------------------------------ Raja's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25901 View this thread: http://www.excelforum.com/showthread.php?threadid=392843 Tag: Formula for calculating positions Tag: 68486
  - 15
    - E - Mail triggering from Excel... Hi All, Once per week we enter stock figures into a spreadie that then advises how many units should be manufactured for that week. This is done at the production plant level. If upon entering the weeks starting stock figure we trigger one of the limits (max or min), I'd like the spreadie (via macro) to send an E - Mail to the stock co - ordinator so that he can look into the alarm event. How might this be done and / or where can I find helpful information to get started ? Thanks for your assistance. David Tag: Formula for calculating positions Tag: 68485
  - 16
    - worksheet ? of ?? I know about page numbering in the header or footer, but I need some way to put the worksheet number (if counted from left to right) and the total number of worksheets in my workbook in cells on my worksheets. Does anyone know how to program some funtions that I can put into a cell that I choose so that these number will automatically be generated and changed, even if I remove or add worksheet, or change the order of the worksheets in the workbook (from left to right), without having to run a macro all the time. Chris Hofman Tag: Formula for calculating positions Tag: 68484
  - 17
    - Mystery Constant Causes #Value Error I guess I stumped everyone in excel.worksheetfunctions group 'cause no one answered July 28 post. Hope someone here has the answer. A spreadsheet displays the #Value error in many cells. Each of these cells contains a formula that simply sums 3 non-contiguous cells in the same column e.g. =SUM(B16,B14,B12). The formula toolbar indicates the reason for the error is that a cell or cells in the range contains a constant which cannot be evaluated. However, no data is visible in the referenced cells (or formula bar when they are clicked on). There are no named ranges that I can see. If I select each of the cells one at a time and press delete then undo the deletion, no change in the formula result (#Value) is observed. However, if I select the first two cells referenced by the formula and press delete, the formula result becomes 0. The #Value error is still displayed if I undo those deletions and select cell 2 and 3 and click delete. It seems the constant(s) somehow relates to the combination of cells 2 and 3 (or they each contain a constant). One other strange looking aspect is that the cells seem to have a number format that shows Custom in the list of formats and Custom_ (yes, that's an underscore) for the custom format. However, one cell on the sheet has the same format and it's formula result is 0, so I don't think it's a formatting thing. For the life of me, I cannot figure out the #Value error when nothing shows in the formula bar or referenced cells. Does anyone have any clue how a cell could contain a constant that can't be seen? Thanks, Dave Tag: Formula for calculating positions Tag: 68470
  - 18
    - Response after clicking direction key Hi all, When i click the direction key, the response as below: PC1: moving the active cell according to the direction PC2: Scrolling the screen according to the direction Anybody have idea why? Thank you for answering Tag: Formula for calculating positions Tag: 68469
  - 19
    - Sorting by Color Hello, Is it possible to sort in excel by color for entire row. Thanks, Manish Tag: Formula for calculating positions Tag: 68463
  - 20
    - Microsoft Query could not be started because it isn't installed I have Excel 2000 SP3 & Windows XP Home. When I go to Data | Get External Data | Run Saved Query I can import data from an Access database into an Excel worksheet. When I go to Data | Get External Data | New Database Query after a delay a message appears to tell me that MS Query is not installed. This is the message: "Microsoft Excel "Microsoft Query could not be started because it isn't installed. You can install Query by running the installation program you "used to install Office or Excel. The Query option is in the Office Tools category. "OK" However MSQRY32.EXE is in the Office folder & I have reinstalled it by renaming it to .old & running the Add/Remove facility in the Office installer to no avail. I have also installed the complete set of Office Tools without any change in the above-mentioned behaviour. I have run Detect & Repair again with no change. I have searched the MSKB & found an article at: http://support.microsoft.com/default.aspx?scid=kb;en-us;179686 that refers to this problem but in Microsoft Excel 98 Macintosh Edition. Nevertheless I have tried the resolutions from the article again to no avail. When trying to start MS Query by double-clicking MSQRY32.EXE apart from the display blinking MS Query does not open. Task Manager both in applications & processes does not show that it is running. It seems to me that as a Saved Query can be run that Query is installed & that some other element that Excel, Query & Access need for a New Database Query is either absent or not available. Can anyone help with this difficulty please? Malcolm Tag: Formula for calculating positions Tag: 68455
  - 21
    - File DMEODMA.xla not found... Hi guys, When I try and open a file in Excel, using File -> Open, I get the following message... "The file DMEODMA.xla not found." The open file dialogue does not appear so I can't open a file. Strangely though, if I click the "open" button on the toolbar the open dialogue comes up okay and I can open a file. Also, if I open an Excel file by double clicking on it in Windows Explorer, Excel opens okay and so does the file. This is not really stopping me from working as I can open files by clicking on the "open" button on the toolbar but it is bugging me and I was wondering if someone could help me out. A google search on "DMEODMA" yielded nothing..! Thanks in advance. Lyndon. Tag: Formula for calculating positions Tag: 68451
  - 22
    - How to change Pivot when SQL data source fieled name changed Q: How to change Pivot when SQL data source field name changed I have a Pivot table that accesses a SQL data source. When one of the Fields Names in SQL is changed there is an error when trying to refresh Pivot Table. I cant find a way to edit the Pivot Table MS Query for the new SQL Columns Name like I can when just using MS Query to import data into excel vs into Pivot. Is this possible? Tag: Formula for calculating positions Tag: 68444
  - 23
    - dropdown list question I am using an in-cell dropdown list that is populated from a vba string. I have encountered a 255 character limit for the list of lookup items. Is there any way around this other than using spreadsheet cells to store the list values? Thanks, Keith Tag: Formula for calculating positions Tag: 68439
  - 24
    - Comparing one list of numbers against another Hi , Can someone help me with this problem I want to compare a list of tape media with another , The results should show missing media and new media in separate columns An example First Inventory Second Inventory Missing Media New Media TAPE01 TAPE01 TAPE07 TAPE11 TAPE02 TAPE02 TAPE08 TAPE03 TAPE03 TAPE09 TAPE04 TAPE04 TAPE10 TAPE05 TAPE05 TAPE06 TAPE06 TAPE07 TAPE11 TAPE08 TAPE09 TAPE10 Any help appreciated Regards Mike Tag: Formula for calculating positions Tag: 68438
  - 25
    - Column and Row lookup? Matrics retrieval I have say a 26 by 26 grid of info and the row 1 is filled with values A-Z and column A is numbered from 1-26. How do I write a function that will search Range A-Z(Row 1) and then search range 1-26 (Col A) and when it finds the two values if will return the value in the cell they intersect? ex. given a 3x3 grid. top row of cells are filled with A, B, C and first calumn is filled with 1,2,3. I want a function that will return the value in (B,2). Thanks. -- jt76 ------------------------------------------------------------------------ jt76's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23717 View this thread: http://www.excelforum.com/showthread.php?threadid=392662 Tag: Formula for calculating positions Tag: 68437

Hi.
I have a worksheet like the following:
Player's Name-----Round1(R1)-----R2-----R3...-----Total Score====Position after
R1-----Pos.(R2)-----Pos.(R3)...

Position is calculated based on the score accumalted (the more scores, the
better) so far.
Eg: Postion after R1 depends on score got in R1.
Position after R2 depends on score got in R1 & R2.
Position after R3 depends on score got in R1 & R2 & R3.

Q: What formulas should I use to calculate the positions up to different rounds?
I think I can use RANK functions, but it seems I need to set SUM formulas to
calculate total scores up to different rounds (ie R1, R1+R2, R1+R2+R3 etc.)
first. It seems to be clumsy.
At best, I would need to set one formula only in "position after R1/R2/R3"
columns witohut the need to set SUM formulas separately.
Any suggestion?

--
Additional information:
- I'm using Office XP
- I'm using Windows XP

Top

Re: Formula for calculating positions by Domenic

Domenic
Fri Aug 05 15:36:33 CDT 2005

Assuming that A1:H10 contains your table, and that the first row
contains your headers/labels, try...

F2, copied across and down:

=IF(COUNT($B$2:B$10)=COUNTA($A$2:$A$10)*COLUMNS($F2:F2),SUMPRODUCT(--(SUM
($B2:B2)<SUBTOTAL(9,OFFSET($B$2:B$10,ROW($B$2:B$10)-ROW($B$2),0,1))))+1,"
")

However, it would be more efficient to have separate totals for each
round and then use RANK.

Hope this helps!

In article <OOGofcemFHA.3552@TK2MSFTNGP10.phx.gbl>,
"0-0 Wai Wai ^-^" <x@x.com> wrote:

> Hi.
> I have a worksheet like the following:
> Player's Name-----Round1(R1)-----R2-----R3...-----Total Score====Position
> after
> R1-----Pos.(R2)-----Pos.(R3)...
>
> Position is calculated based on the score accumalted (the more scores, the
> better) so far.
> Eg: Postion after R1 depends on score got in R1.
> Position after R2 depends on score got in R1 & R2.
> Position after R3 depends on score got in R1 & R2 & R3.
>
> Q: What formulas should I use to calculate the positions up to different
> rounds?
> I think I can use RANK functions, but it seems I need to set SUM formulas to
> calculate total scores up to different rounds (ie R1, R1+R2, R1+R2+R3 etc.)
> first. It seems to be clumsy.
> At best, I would need to set one formula only in "position after R1/R2/R3"
> columns witohut the need to set SUM formulas separately.
> Any suggestion?

Top