Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
More formula problems
Ok, so the conditions are this:
Males 26 and under have a run time of 18 minutes or less for a perfect 100 points. For every 10 seconds after that you subtract 1 point (so that a run time of 18:01 - 18:10 = 99 points and a run time of 23:00 = 70 points) Anything 28:01 and over is failing. Males 27 to 39 have same perfect score but 29:01 and over is failing. Males 40 - 45 have until 30:01 to pass. Males 46 and over have until 33:01 to pass. That's one. The other conditions a Females 26 and under run a 21:00 or faster for 100 points. Same subtraction rate for points. 31:01 is failing point. Females 27 - 39 have till 32:01 and females 40 - 45 have until 33:01 to pass. Females 46 and over have until 36:01. I have the beginning I think. Here is what I have so far that I could figure out: L8 is my Age block, M8 is my Gender block, and Y8 is my run time block. AA is the run score block. =(--(L8<27)*(M8="M"))*IF(Y8<=1800,100 I hope the beginning I have can help someone to help me lol. Thanks in advance to anyone who can help this jumble out. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
More formula problems
Please read the entire thread - I did some bouncing around.
One - create a lookup table Ending Age time 0 28 19 29 40 30 46 33 The age 18 is in my cell b3 and the 33 is in cell c6 Clients age is keyed in cell b10 Clients time is keyed in cell c10 Cell d10 has the max time =VLOOKUP(B10,B3:C6,2) Points in cells e10 =IF(C10<=18,100,IF(C10D10,0,100-100*ROUNDUP((D10-C10)/(D10-18),1))) Does this make sense? Make sure you get the males (or females) to work first and then add the other gender One way to add the other gender Note since the ages are the same in cell c3 =if(a10="m",28,31) cell c4 =if(a10="m",29,32) cell c5 =if(a10="m",30,33) cell c6 =if(a10="m",33,36) Understand? Then the last thing you have to do is to make the "18" is cell e10 variable Lets make cell c7 =if(a10="m",18,21) then equation e10 then would be =IF(C10<=C7,100,IF(C10D10,0,100-100*ROUNDUP((D10-C10)/(D10-C7),1))) There are slightly shorter ways - but this works This also assumes that you are doing one case at a time and the lookup table is okay to change. If you need to keep both tables - we can fix that as well..... -- Wag more, bark less "LoveExcelButFrustrated" wrote: Ok, so the conditions are this: Males 26 and under have a run time of 18 minutes or less for a perfect 100 points. For every 10 seconds after that you subtract 1 point (so that a run time of 18:01 - 18:10 = 99 points and a run time of 23:00 = 70 points) Anything 28:01 and over is failing. Males 27 to 39 have same perfect score but 29:01 and over is failing. Males 40 - 45 have until 30:01 to pass. Males 46 and over have until 33:01 to pass. That's one. The other conditions a Females 26 and under run a 21:00 or faster for 100 points. Same subtraction rate for points. 31:01 is failing point. Females 27 - 39 have till 32:01 and females 40 - 45 have until 33:01 to pass. Females 46 and over have until 36:01. I have the beginning I think. Here is what I have so far that I could figure out: L8 is my Age block, M8 is my Gender block, and Y8 is my run time block. AA is the run score block. =(--(L8<27)*(M8="M"))*IF(Y8<=1800,100 I hope the beginning I have can help someone to help me lol. Thanks in advance to anyone who can help this jumble out. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
More formula problems
How are you entering your times... without colons? You do realize you can't
do simple subtraction on numbers entered that way and get correct time calculations, right? Anyway, let me know what your time entries actually are so I can either use them as entered (with colons) or construct the appropriate corrections for them (with no colons and assuming the digits represent MMSS). Rick "LoveExcelButFrustrated" m wrote in message ... Ok, so the conditions are this: Males 26 and under have a run time of 18 minutes or less for a perfect 100 points. For every 10 seconds after that you subtract 1 point (so that a run time of 18:01 - 18:10 = 99 points and a run time of 23:00 = 70 points) Anything 28:01 and over is failing. Males 27 to 39 have same perfect score but 29:01 and over is failing. Males 40 - 45 have until 30:01 to pass. Males 46 and over have until 33:01 to pass. That's one. The other conditions a Females 26 and under run a 21:00 or faster for 100 points. Same subtraction rate for points. 31:01 is failing point. Females 27 - 39 have till 32:01 and females 40 - 45 have until 33:01 to pass. Females 46 and over have until 36:01. I have the beginning I think. Here is what I have so far that I could figure out: L8 is my Age block, M8 is my Gender block, and Y8 is my run time block. AA is the run score block. =(--(L8<27)*(M8="M"))*IF(Y8<=1800,100 I hope the beginning I have can help someone to help me lol. Thanks in advance to anyone who can help this jumble out. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
More formula problems
One correction to my formula
=IF(C10<=C7,100,IF(C10D10,0,100*ROUNDUP((D10-C10)/(D10-C7),1))) I haven't worked with time much and will defer to Rick - but with a few examples that I did it seemed to work - but maybe I'll learn something as well. -- Wag more, bark less "Brad" wrote: Please read the entire thread - I did some bouncing around. One - create a lookup table Ending Age time 0 28 19 29 40 30 46 33 The age 18 is in my cell b3 and the 33 is in cell c6 Clients age is keyed in cell b10 Clients time is keyed in cell c10 Cell d10 has the max time =VLOOKUP(B10,B3:C6,2) Points in cells e10 =IF(C10<=18,100,IF(C10D10,0,100-100*ROUNDUP((D10-C10)/(D10-18),1))) Does this make sense? Make sure you get the males (or females) to work first and then add the other gender One way to add the other gender Note since the ages are the same in cell c3 =if(a10="m",28,31) cell c4 =if(a10="m",29,32) cell c5 =if(a10="m",30,33) cell c6 =if(a10="m",33,36) Understand? Then the last thing you have to do is to make the "18" is cell e10 variable Lets make cell c7 =if(a10="m",18,21) then equation e10 then would be =IF(C10<=C7,100,IF(C10D10,0,100-100*ROUNDUP((D10-C10)/(D10-C7),1))) There are slightly shorter ways - but this works This also assumes that you are doing one case at a time and the lookup table is okay to change. If you need to keep both tables - we can fix that as well..... -- Wag more, bark less "LoveExcelButFrustrated" wrote: Ok, so the conditions are this: Males 26 and under have a run time of 18 minutes or less for a perfect 100 points. For every 10 seconds after that you subtract 1 point (so that a run time of 18:01 - 18:10 = 99 points and a run time of 23:00 = 70 points) Anything 28:01 and over is failing. Males 27 to 39 have same perfect score but 29:01 and over is failing. Males 40 - 45 have until 30:01 to pass. Males 46 and over have until 33:01 to pass. That's one. The other conditions a Females 26 and under run a 21:00 or faster for 100 points. Same subtraction rate for points. 31:01 is failing point. Females 27 - 39 have till 32:01 and females 40 - 45 have until 33:01 to pass. Females 46 and over have until 36:01. I have the beginning I think. Here is what I have so far that I could figure out: L8 is my Age block, M8 is my Gender block, and Y8 is my run time block. AA is the run score block. =(--(L8<27)*(M8="M"))*IF(Y8<=1800,100 I hope the beginning I have can help someone to help me lol. Thanks in advance to anyone who can help this jumble out. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
More formula problems
Well, I have been holding on to this solution waiting for you to come back
to me with an answer to my question... but since you haven't, I'll give you the "generalized" answer and you will have to substitute actual function calls to replace the indicated values shown in angle brackets. This formula should calculate the "run score" (assuming you use "F" for female in M8)... =100-MAX(0,INT((<minutes+<seconds/60-17.834-3*(M8="F"))*6)) Rick "Rick Rothstein (MVP - VB)" wrote in message ... How are you entering your times... without colons? You do realize you can't do simple subtraction on numbers entered that way and get correct time calculations, right? Anyway, let me know what your time entries actually are so I can either use them as entered (with colons) or construct the appropriate corrections for them (with no colons and assuming the digits represent MMSS). Rick "LoveExcelButFrustrated" m wrote in message ... Ok, so the conditions are this: Males 26 and under have a run time of 18 minutes or less for a perfect 100 points. For every 10 seconds after that you subtract 1 point (so that a run time of 18:01 - 18:10 = 99 points and a run time of 23:00 = 70 points) Anything 28:01 and over is failing. Males 27 to 39 have same perfect score but 29:01 and over is failing. Males 40 - 45 have until 30:01 to pass. Males 46 and over have until 33:01 to pass. That's one. The other conditions a Females 26 and under run a 21:00 or faster for 100 points. Same subtraction rate for points. 31:01 is failing point. Females 27 - 39 have till 32:01 and females 40 - 45 have until 33:01 to pass. Females 46 and over have until 36:01. I have the beginning I think. Here is what I have so far that I could figure out: L8 is my Age block, M8 is my Gender block, and Y8 is my run time block. AA is the run score block. =(--(L8<27)*(M8="M"))*IF(Y8<=1800,100 I hope the beginning I have can help someone to help me lol. Thanks in advance to anyone who can help this jumble out. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
More formula problems
Rick, what is 17.834 for?
-- Wag more, bark less "Rick Rothstein (MVP - VB)" wrote: Well, I have been holding on to this solution waiting for you to come back to me with an answer to my question... but since you haven't, I'll give you the "generalized" answer and you will have to substitute actual function calls to replace the indicated values shown in angle brackets. This formula should calculate the "run score" (assuming you use "F" for female in M8)... =100-MAX(0,INT((<minutes+<seconds/60-17.834-3*(M8="F"))*6)) Rick "Rick Rothstein (MVP - VB)" wrote in message ... How are you entering your times... without colons? You do realize you can't do simple subtraction on numbers entered that way and get correct time calculations, right? Anyway, let me know what your time entries actually are so I can either use them as entered (with colons) or construct the appropriate corrections for them (with no colons and assuming the digits represent MMSS). Rick "LoveExcelButFrustrated" m wrote in message ... Ok, so the conditions are this: Males 26 and under have a run time of 18 minutes or less for a perfect 100 points. For every 10 seconds after that you subtract 1 point (so that a run time of 18:01 - 18:10 = 99 points and a run time of 23:00 = 70 points) Anything 28:01 and over is failing. Males 27 to 39 have same perfect score but 29:01 and over is failing. Males 40 - 45 have until 30:01 to pass. Males 46 and over have until 33:01 to pass. That's one. The other conditions a Females 26 and under run a 21:00 or faster for 100 points. Same subtraction rate for points. 31:01 is failing point. Females 27 - 39 have till 32:01 and females 40 - 45 have until 33:01 to pass. Females 46 and over have until 36:01. I have the beginning I think. Here is what I have so far that I could figure out: L8 is my Age block, M8 is my Gender block, and Y8 is my run time block. AA is the run score block. =(--(L8<27)*(M8="M"))*IF(Y8<=1800,100 I hope the beginning I have can help someone to help me lol. Thanks in advance to anyone who can help this jumble out. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
More formula problems
It is approximately 10 seconds less than 18 minutes and is subtracted from
the runners time so that the interval calculations can be made. Rick "Brad" wrote in message ... Rick, what is 17.834 for? -- Wag more, bark less "Rick Rothstein (MVP - VB)" wrote: Well, I have been holding on to this solution waiting for you to come back to me with an answer to my question... but since you haven't, I'll give you the "generalized" answer and you will have to substitute actual function calls to replace the indicated values shown in angle brackets. This formula should calculate the "run score" (assuming you use "F" for female in M8)... =100-MAX(0,INT((<minutes+<seconds/60-17.834-3*(M8="F"))*6)) Rick "Rick Rothstein (MVP - VB)" wrote in message ... How are you entering your times... without colons? You do realize you can't do simple subtraction on numbers entered that way and get correct time calculations, right? Anyway, let me know what your time entries actually are so I can either use them as entered (with colons) or construct the appropriate corrections for them (with no colons and assuming the digits represent MMSS). Rick "LoveExcelButFrustrated" m wrote in message ... Ok, so the conditions are this: Males 26 and under have a run time of 18 minutes or less for a perfect 100 points. For every 10 seconds after that you subtract 1 point (so that a run time of 18:01 - 18:10 = 99 points and a run time of 23:00 = 70 points) Anything 28:01 and over is failing. Males 27 to 39 have same perfect score but 29:01 and over is failing. Males 40 - 45 have until 30:01 to pass. Males 46 and over have until 33:01 to pass. That's one. The other conditions a Females 26 and under run a 21:00 or faster for 100 points. Same subtraction rate for points. 31:01 is failing point. Females 27 - 39 have till 32:01 and females 40 - 45 have until 33:01 to pass. Females 46 and over have until 36:01. I have the beginning I think. Here is what I have so far that I could figure out: L8 is my Age block, M8 is my Gender block, and Y8 is my run time block. AA is the run score block. =(--(L8<27)*(M8="M"))*IF(Y8<=1800,100 I hope the beginning I have can help someone to help me lol. Thanks in advance to anyone who can help this jumble out. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
More formula problems
Duh - I was doing one second not 10.
What (if anything)is wrong with my solution? -- Wag more, bark less "Rick Rothstein (MVP - VB)" wrote: It is approximately 10 seconds less than 18 minutes and is subtracted from the runners time so that the interval calculations can be made. Rick "Brad" wrote in message ... Rick, what is 17.834 for? -- Wag more, bark less "Rick Rothstein (MVP - VB)" wrote: Well, I have been holding on to this solution waiting for you to come back to me with an answer to my question... but since you haven't, I'll give you the "generalized" answer and you will have to substitute actual function calls to replace the indicated values shown in angle brackets. This formula should calculate the "run score" (assuming you use "F" for female in M8)... =100-MAX(0,INT((<minutes+<seconds/60-17.834-3*(M8="F"))*6)) Rick "Rick Rothstein (MVP - VB)" wrote in message ... How are you entering your times... without colons? You do realize you can't do simple subtraction on numbers entered that way and get correct time calculations, right? Anyway, let me know what your time entries actually are so I can either use them as entered (with colons) or construct the appropriate corrections for them (with no colons and assuming the digits represent MMSS). Rick "LoveExcelButFrustrated" m wrote in message ... Ok, so the conditions are this: Males 26 and under have a run time of 18 minutes or less for a perfect 100 points. For every 10 seconds after that you subtract 1 point (so that a run time of 18:01 - 18:10 = 99 points and a run time of 23:00 = 70 points) Anything 28:01 and over is failing. Males 27 to 39 have same perfect score but 29:01 and over is failing. Males 40 - 45 have until 30:01 to pass. Males 46 and over have until 33:01 to pass. That's one. The other conditions a Females 26 and under run a 21:00 or faster for 100 points. Same subtraction rate for points. 31:01 is failing point. Females 27 - 39 have till 32:01 and females 40 - 45 have until 33:01 to pass. Females 46 and over have until 36:01. I have the beginning I think. Here is what I have so far that I could figure out: L8 is my Age block, M8 is my Gender block, and Y8 is my run time block. AA is the run score block. =(--(L8<27)*(M8="M"))*IF(Y8<=1800,100 I hope the beginning I have can help someone to help me lol. Thanks in advance to anyone who can help this jumble out. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula problems | Excel Discussion (Misc queries) | |||
Problems with a formula | Excel Worksheet Functions | |||
formula problems | Excel Discussion (Misc queries) | |||
Formula problems | Excel Worksheet Functions | |||
Formula Problems | Excel Worksheet Functions |