Multiple Formulas #3

Im creating a database for my school house and before i convert over to 
Access..Im trying to figure out some ways to calculate some numbers. For 
example, my hardest thing yet, if someone was to run an 18min:00sec 3mile 
run...he'll get a max score of 100. But if he ran it in 20min:34sec he'll get 
a score of 84.  Being the longest run of 28min:00sec (failing).  How do i 
create a formula to calculate the mins and sec to result in the point score.
0
LCplShaw (1)
10/4/2005 8:50:04 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
277 Views

Similar Articles

[PageSpeed] 41

Set up your timing ranges on another sheet and then use a VLOOKUP with the 
last parameter set to TRUE. for example, if you table is a two column one 
with times in one column (A) and the associated score in B then in your 
current table (imagining the entered time is A) enter

=VLOOKUP(A1,SheetTable!$A$1:$B$100,2,TRUE)

this should return the nearest time and the corresponding score

-- 
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


"LCpl Shaw" <LCplShaw@discussions.microsoft.com> wrote in message 
news:15D1E2BD-285B-4ECC-B4ED-C2689826860C@microsoft.com...
> Im creating a database for my school house and before i convert over to
> Access..Im trying to figure out some ways to calculate some numbers. For
> example, my hardest thing yet, if someone was to run an 18min:00sec 3mile
> run...he'll get a max score of 100. But if he ran it in 20min:34sec he'll 
> get
> a score of 84.  Being the longest run of 28min:00sec (failing).  How do i
> create a formula to calculate the mins and sec to result in the point 
> score. 


0
10/4/2005 9:36:07 PM
Reply:

Similar Artilces:

problem with index formula
The following fomula is contained in cell C16 and returns the correct values: =VLOOKUP(B6,'Active Clients'!$B$2:$K$954,10,FALSE) The following formula is contained in cell L18 and uses the result in C16 as a lookup value in the first "match": =INDEX(L21:M44,MATCH(C16,L21:L44,1),MATCH(M16,L21:M21,-1)) My promlem is that when I try to match on cell C16 my index formula returns an error. However, when I change the index formula to reference another cell (let's say cell D16) and I type in the value shown in cell C16, the index formula works fine. I think my problem has ...

Send email to custom entities in CRM 3.0
How do I send email to entities other than account, contact, leads and users? Not possible. "Andy" wrote: > How do I send email to entities other than account, contact, leads and > users? > I found out the following: It turns out every CRM 3.0 email is made out of three types of records, all of which are tied together through a common ActivityId: activitypointer - this is the main record and it holds the fields common to all activities email - this holds fields uniquely related to emails such as the mime type, tracking token, etc. activityparty - this holds sender and...

IF FORMULAS #2
Looking for help...... In column A1:A16, I have names. In B1:B16 I will assign random numbers to those names from 1-16. In another part of the sheet I have 16 columns lets say D:S, (D) being column 1 and (S) being column 16. when I assign a number in column B, I want the name to the left to appear above the appropiate column. EX, If B1 is 16 then the name in A1 will appear above column S. I am using Excel 2007 Windows Vista. I find this site to be the place for answers,everyone is great. Thanks in advance. D1: =INDEX($A$1:$A$16,MATCH(COLUMN()-3,$B$1:$B$16,0)) Fill to S1. "JOHN...

How do I display a formula calculation in plot area of a chart?
I want to add a text box in the chart that returns the value from a formula that was entered on the data page. The formula value is not related to any series value. WiseGuyOne: You need to build your text expression in a cell, then insert that expression to the chart. First, to build your text expression: 1. Select your cell 2. Enter = first to let Excel =know you are adding a formula 3. You can now enter text within quotes and cell values .. here's an example =" Yesterdays average temperature was " & sheet1!$a$3 Note that the & is used to add ...

Personal.Xls #3
Hi There, I changed some code in a macro inside my Personal.xls file and saved it. Now when I try to run the macro I get an error message saying that a file called Personal.xls is already open and I can not open another. What have I done wrong? Thanks for your help, Steve Wilson. It's OK I have sorted this out. I saved Personal.xls to another drive and when I opened Excel it loaded Personal.xls from the XLStart Dir as usual but when I clicked on my macro button in the toolbar it was pointing to the macro in personal.xls on the other drive. All the best, Steve Wilson. "Alway...

multiple copies of mail
Hi! After reinstalling XP and outlook 2002, I have started to get 2-5 copies of mail (not every mail). This wasn�t a problem before (when I used outlook 2000). Any ideas where to start looking? Tommy "tomppa" <tofors99@hotmail.com> a �crit dans le message de news:%235CQZ6mwDHA.1512@TK2MSFTNGP10.phx.gbl... > Hi! > > After reinstalling XP and outlook 2002, I have started to get 2-5 copies of > mail (not every mail). This wasn�t a problem before (when I used outlook > 2000). > > Any ideas where to start looking? > > Tommy > > If you get as ...

multiple nested gridviews
I have a parent gridview that has a child gridview and both work fine. It is when I add a second nested gridview (grandchild) that seems to break the expand collapse. So I thought I would seek advice from the group. All the information for all the gridviews comes from the same table, CR. The parent gridview shows all open change requests (parent, child, grandchild, ..) from the CR table. Child gridview (level2) comes from a view of the CR table with an alias for the ID. Grandchild gridview (level3) comes from another view with a different alias for the ID. All the gridview...

Need to separate multiple numbers in one cell
I have multiple number in one cell and i need to somehow put each number into one separate cell for each number: I have : column A row1 2 5 8 10 I want: column B column C column D column E row1 2 5 8 10 How do I do it on Excel 2007? thanks EggHeadCafe - Software Developer Portal of Choice Map Stored Procedure Output To Class Properties http://www.eggheadcafe.com/tutorials/aspnet/2853a1aa-5db7-40d0-9cde-46847fa770ef/map-stored-procedure-outp.aspx Hi, Use Data > Text to columns and specify the delimiter as space ...

Need Formula to Average Monthly Increase
I have a spreadsheet where there are columns for each month of the year Each cell contains a number of loan submissions that graduall increases per month as we gain more business from each mortgage vendor I need a formula that can look at multiple cells in a row (i.e Jan-Dec) per vendor and then give me a result that shows the averag monthly percentage increase factor in submissions... Example: MONTH Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec A SUBS 1 5 18 29 30 40 57 85 150 275 ......... ..I will be using this data fo forecasting future submissions ...

Going Back in History formula?
How can I express a formula that would back 300 rows and look at every other row for the 3 cells with random single digits from 0 to 9 and calculate, the how many rows back since the oldest 0-9 digit occurred in cell 1 answer, cell 2 answer and 3 answer, then calculate which 0-9 digit has not occurred in cell 1, cell 2 and cell 3 the longest and displays how many row back since the last occurrence. So that would be 4 separate formulas ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.Exc...

How do I enter a time formula into a worksheet that determines if.
How do I, set about entering a formula within a worksheet that determines if the Time is greater or less that the time in a certian cell; eg =if(now()>K17,"then display this","") K17 holding the Value 08:30 AM formated to time =IF(MOD(NOW(),1)>K17,"then display this","") HTH Jason Atlanta, GA >-----Original Message----- >How do I, set about entering a formula within a worksheet that determines if >the Time is greater or less that the time in a certian cell; >eg =if(now()>K17,"then display this","") ...

Existing formula
I'm working with a spreadsheet that was built by someone else and cam across a formula in some of the cells that I'm puzzled by. The formul begins with "=", but the next character is "+", then a reference t another worksheet and cell. The "+" is what I'm wondering about, haven't seen it used in this context before, can someone help me ou here? Some examples of what I'm looking at are: =+Inputs!G9 =+H17-R20 Thanks, wwhit -- Message posted from http://www.ExcelForum.com It's an unnecessary character that is there because the person ...

formulas changing when emailed
At work, I have created a spreadsheet that has links to workbooks in a user drive..called the "U: drive." When I transfer this excel project to others via email, I make sure that the other workbooks also have the same name in their U: drive as well. However, whenever I email it... I have to change many of the formulas at their computers bc the references in my formulas change to a really long string. I have tried using "CNTL+H" to replace the "=" to a "+" but it doesn't work for me bc I have "+" "=" elsewhere in my formulas....

Adding data from multiple cell and linking to a new worksheet
I have data in a sheet with names and amounts due. Like A 500 B 300 A 600 B 300 C 400 C 200 There is a second sheet named summary. I want the totals of each person to appear in the summary sheet. These should be linked cells and any change in data in sheet 1 should be updtaed automatically Create a PivotTable http://www.ozgrid.com/Excel/excel-pivot-tables.htm -- Regards Dave Hawley www.ozgrid.com "Rashmi" <Rashmi@discussions.microsoft.com> wrote in message news:56DC0F3A-8D60-4FE2-B778-3A07790EFE8A@microsoft.com... >I have data in a sheet wi...

XSD One Multiple element question
Hi, I am trying to define a Key element for a vehicle schema. I have three ways to uniquely identify a vehicle such as URN, UnitID and Vin. I'd like to make a rule that ensures that at least one of the three is present, but 2 or all 3 can be present. I tried something like below, but it does not seem valid. Thanks in advance for any assistance anyone may be able to provide. Portion of schema is posted below. Hopefully it gives some idea as to what I am trying to accomplish. Best regards, Victor <xs:complexType name="VehKeyType"> <xs:choice> <xs:seq...

Reply Address #3
Using Exchange 2003 and outlook 2003 is there a way to force the "reply to address" to be something besides the Sending address. The example is listed below. These are the sending addresses PHostetter@domainname.com JRoetman@domainname.com DKrischel@domainname.com This is the address I want all replies to be returned to. dp@domainname.com This is the address I want all replies to be returned to. On Sat, 25 Jun 2005 07:30:19 -0500, "Roger Crawford" <rcrawford@nospam.scci.com> wrote: >Using Exchange 2003 and outlook 2003 is there a ...

Excel 2003 - VBA
Hi Guys: I'm drawing a blank and need some help. I have a table with row 3 containing sheet names (currently pointing at 106 sheets). In rows 4-250 I want to put formulas that will look for something (located in Column B) in each of the sheets named in row 3. The first cell in the target sheet contains the last row with valid data. Example: Cell "D3" = P02-05 Cell "B5" = 123456789IBM In cell "D5" I want to generate a formula that will look in sheet P02-05, search for the contents of "B5" throughout the sheet and return the Value in colum...

Open File Dialog
In a database I use an Open File Dialog to open multiple files using an Array and then I use the Array to populate a list and process it. This works great, until I select over 7 files, at which point the array remains empty without the error handler triggering. Is there a logical reason and/or solution for this? Further research indicates that when the number exceeds 7 the following line starts giving a False instead of a True: aht_apiGetOpenFileName(OFN) ...

Excel Cell Formatting #3
How do I centeer a picture in an Excel cell? Hi you can't as such objects 'float' above the cells in Excel -- Regards Frank Kabel Frankfurt, Germany "Tom Coffey" <Tom Coffey@discussions.microsoft.com> schrieb im Newsbeitrag news:8E7356E1-4318-42F3-B9C6-3D7EDFBBEEA1@microsoft.com... > How do I centeer a picture in an Excel cell? ...

Help with referencing multiple sheets in formulas
Greetings Xcellers I have a workbook with 32 sheets, The first sheet is a month summary and the other 31 sheets are for each day of the month. The sheets are names month,1,2,3..31 I need to copy information from the daily sheets to create a summary on the month sheet like follows A B 1 Day Turnover ..... 2 1 ='1'!E7 3 2 ='2'!E7 4 3 ... 5 5 ='5'!E7 Is there a way to substitute the sheet name from the column Day instead of manually having to enter each formula. There will be a number of columns and I have a number of similar workbooks to make. IE for B2 a formula l...

Multiple Redo
The dropdown for Undo still exists, so I can undo dozens of actions at a time. However, the dropdown for Redo has been eliminated . Is there some way to restore this feature (which seems a rather obvious complement to multiple Undo)? Try "Ctrl+Shift+Backspace" "joeb" wrote: > The dropdown for Undo still exists, so I can undo dozens of actions at a > time. However, the dropdown for Redo has been eliminated . Is there some > way to restore this feature (which seems a rather obvious complement to > multiple Undo)? Sorry! Should have s...

Multiple currencies in budget
I use Money 2002 Deluxe and Business. I have two accounts that are in Japanese yen. In all other areas of Money they are converting to my BASE currency (Canadian $) no problem. When I view my budget planner these accounts and reoccuring bills are still being shown as Japanese yen, but being used like CAD$. When I edit the budget these numbers are being shown converted to CAD$ and look fine. The budget planner is showing that I have budgeted 100s of thousands of dollars each month (I wish!) Can anyone help with figuring out why the numbers are being shown converted in one part of...

formula numeric input
Is there any way to have Excel assume, as it should, that a number input in a formula means a value? For example, is there a way to change the default so that instead of inputting "+7+8" to calculate 15, that I could just input "7+8". Lotus used to work this way, and I have to believe that Excel would allow the same capability. -- seamaml ------------------------------------------------------------------------ seamaml's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30296 View this thread: http://www.excelforum.com/showthread.php?threadid=...

Excel 2007 formula, multiple reference range
Help. I am collating data from 6 facilities. I have one worksheet with 7 pages. The first is a summary of all data. The other 6 have individual data for each facility. On the summary page, I need a total number, pulled from all 6 facilities. On the facility detail pages, I have summed the occurrences in that facility. Now, I need a formula that will let me get a grand total. For example: pg 1 B5 needs to have the total from: pg2 K5:K100 if J5:J100 = pg1A5 plus pg3 K5:K100 if J5:J100 = pg1A5 plus pg4 K5:K100 if J5:J100 = pg1A5 plus pg5 SAA plus pg 6 SAA plus pg 7 SAA The reason w...

Multiple Problems--Possible Corruption of Local Live Program
Starting about 2 days ago, my onsite program to access Windows Live Hotmail (which I've used successfully for 9 months) has acted unstably. Here are the symptoms: (1) About half the time I start the program, my address book is missing--i.e. "contacts" show zero. The other half of the time, it does appear. (2) In responding to some emails, the enter key doesn't function. (3) I can't send a message in which I copy-and-pasted some Excel lines. The send function just hangs up. (4) When I do have a malfunction, I am sometimes unable to end the "wlmail" ...