|
Nesting and Naming help!
I am making an excel file that should convert the distance jumped by a person to a specific rank. For example if the distance was greater or equal to 34, he would get a 10. and 34 distance = 32, he would get a 9 and so on... Here's my dilema; I need a formula that will should nest more than 7 functions, in this case it wont work right? So what I did was name 2 separate fuctions for example 1st fuction is Code: -------------------- =IF(B8=34,10,IF(B8=32,9,IF(B8=30,8,IF(B8=28,7, IF(B8=26,6,IF(B8=24,5,IF(B8=22,4,IF(B8=20,3,FA LSE)))))))) -------------------- and the 2nd fuction is Code: -------------------- =IF(B8=18,2,IF(B8<=16,1,1)) -------------------- I name the 1st function vJump1 and the 2nd fuction vJump2. Both of the functions are below the actual table and will be hidden. As you can see, they all look for the values in B8, and in cell B9, this is what I would put : Code: -------------------- =IF(vJump1,vJump1,vJump2) -------------------- It works and all and even made a few tweaks to make it work with different values and exercises as well (sit ups, pull ups and so on...). All in all, I have 18 names for the 9 exercises. The problem is, when I want to make inputs for the next week, I put it in another column (D8, F8, H8 ...). And thus, I have to copy the fuctions that i made and name them differently. It is very tidious and time wasting to do this. Also, I am taking records for 8 other men(one worksheet for each person), so that means, I'd have to name more than a hundred names. Can anyone help me? Is there any other way to solve my problem? Can anyone point me to a tutorial? I'm just a teenager who has basic excel knowhow. If it helps, I attached the file. Thanks, Peds +-------------------------------------------------------------------+ |Filename: ADMU BCPR Edited 2.zip | |Download: http://www.excelforum.com/attachment.php?postid=4936 | +-------------------------------------------------------------------+ -- t43m4n ------------------------------------------------------------------------ t43m4n's Profile: http://www.excelforum.com/member.php...o&userid=35536 View this thread: http://www.excelforum.com/showthread...hreadid=555346 |
Nesting and Naming help!
=LOOKUP(B8,{0,20,22,24,26,28,30,32,34},{FALSE,3,4, 5,6,7,8,9,10})
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "t43m4n" wrote in message ... I am making an excel file that should convert the distance jumped by a person to a specific rank. For example if the distance was greater or equal to 34, he would get a 10. and 34 distance = 32, he would get a 9 and so on... Here's my dilema; I need a formula that will should nest more than 7 functions, in this case it wont work right? So what I did was name 2 separate fuctions for example 1st fuction is Code: -------------------- =IF(B8=34,10,IF(B8=32,9,IF(B8=30,8,IF(B8=28,7, IF(B8=26,6,IF(B8=24,5,IF (B8=22,4,IF(B8=20,3,FALSE)))))))) -------------------- and the 2nd fuction is Code: -------------------- =IF(B8=18,2,IF(B8<=16,1,1)) -------------------- I name the 1st function vJump1 and the 2nd fuction vJump2. Both of the functions are below the actual table and will be hidden. As you can see, they all look for the values in B8, and in cell B9, this is what I would put : Code: -------------------- =IF(vJump1,vJump1,vJump2) -------------------- It works and all and even made a few tweaks to make it work with different values and exercises as well (sit ups, pull ups and so on...). All in all, I have 18 names for the 9 exercises. The problem is, when I want to make inputs for the next week, I put it in another column (D8, F8, H8 ...). And thus, I have to copy the fuctions that i made and name them differently. It is very tidious and time wasting to do this. Also, I am taking records for 8 other men(one worksheet for each person), so that means, I'd have to name more than a hundred names. Can anyone help me? Is there any other way to solve my problem? Can anyone point me to a tutorial? I'm just a teenager who has basic excel knowhow. If it helps, I attached the file. Thanks, Peds +-------------------------------------------------------------------+ |Filename: ADMU BCPR Edited 2.zip | |Download: http://www.excelforum.com/attachment.php?postid=4936 | +-------------------------------------------------------------------+ -- t43m4n ------------------------------------------------------------------------ t43m4n's Profile: http://www.excelforum.com/member.php...o&userid=35536 View this thread: http://www.excelforum.com/showthread...hreadid=555346 |
Nesting and Naming help!
"Bob Phillips" wrote in message
... "t43m4n" wrote in message ... I am making an excel file that should convert the distance jumped by a person to a specific rank. For example if the distance was greater or equal to 34, he would get a 10. and 34 distance = 32, he would get a 9 and so on... Here's my dilema; I need a formula that will should nest more than 7 functions, in this case it wont work right? So what I did was name 2 separate fuctions for example 1st fuction is Code: -------------------- =IF(B8=34,10,IF(B8=32,9,IF(B8=30,8,IF(B8=28,7, IF(B8=26,6,IF(B8=24,5,IF (B8=22,4,IF(B8=20,3,FALSE)))))))) -------------------- and the 2nd fuction is Code: -------------------- =IF(B8=18,2,IF(B8<=16,1,1)) -------------------- I name the 1st function vJump1 and the 2nd fuction vJump2. Both of the functions are below the actual table and will be hidden. As you can see, they all look for the values in B8, and in cell B9, this is what I would put : Code: -------------------- =IF(vJump1,vJump1,vJump2) -------------------- It works and all and even made a few tweaks to make it work with different values and exercises as well (sit ups, pull ups and so on...). All in all, I have 18 names for the 9 exercises. The problem is, when I want to make inputs for the next week, I put it in another column (D8, F8, H8 ...). And thus, I have to copy the fuctions that i made and name them differently. It is very tidious and time wasting to do this. Also, I am taking records for 8 other men(one worksheet for each person), so that means, I'd have to name more than a hundred names. Can anyone help me? Is there any other way to solve my problem? Can anyone point me to a tutorial? I'm just a teenager who has basic excel knowhow. =LOOKUP(B8,{0,20,22,24,26,28,30,32,34},{FALSE,3,4, 5,6,7,8,9,10}) Or =MIN(10,MAX(1,INT((B8-14)/2))) -- David Biddulph |
Nesting and Naming help!
=IF(B8=34,10,IF(B8<=16,1,LOOKUP(B8,{18,2;19,2;20, 3;21,3;22,4;23,4;24,5;25,5;26,6;27,6;28,7;29,7;30, 8;31,8;32,9;33,9}))) This should work, however are you aware that you haven't assigned a value to 17? If you need to make an array like this in the future simply fill a column with numbers, and then to there right fill the value you wish assigned to them. In a cell type =(range that you made) i.e. =K2:L20 Now highlight just the K2:L20 in your formula bar and press F9. It will be converted to an array{}. Copy the array and place it inside your formula. Saves a lot of typing, and you get all the commas and semicolons in just the right places. -- babycody ------------------------------------------------------------------------ babycody's Profile: http://www.excelforum.com/member.php...o&userid=13120 View this thread: http://www.excelforum.com/showthread...hreadid=555346 |
Nesting and Naming help!
Thanks for all your tips. I will try it tomorrow when I have time. -- t43m4n ------------------------------------------------------------------------ t43m4n's Profile: http://www.excelforum.com/member.php...o&userid=35536 View this thread: http://www.excelforum.com/showthread...hreadid=555346 |
Nesting and Naming help!
Lateral thinking at it's best Don!
Just needs a little tweak like =ROUNDDOWN((B8/2),0)-7 to sort out the odd numbers. Regards Martin |
Nesting and Naming help!
Thanks for the catch. It probably also needs the min
=ROUNDDOWN(MIN((B8/2)-7,10),0) -- Don Guillett SalesAid Software "MartinW" wrote in message ... Lateral thinking at it's best Don! Just needs a little tweak like =ROUNDDOWN((B8/2),0)-7 to sort out the odd numbers. Regards Martin |
Nesting and Naming help!
"Don Guillett" wrote in message
... Thanks for the catch. It probably also needs the min =ROUNDDOWN(MIN((B8/2)-7,10),0) And the max. -- David Biddulph |
Nesting and Naming help!
problem with this is, I also need conditions that allow non-whole numbers. For example, if i input 32.2, it should return 9 and so on... Also I want the code to be flexible because some exercises require a different value. For example the sprint exercise. Less time means more points (10) and more time means less points ( 1 ). Thanks -- t43m4n ------------------------------------------------------------------------ t43m4n's Profile: http://www.excelforum.com/member.php...o&userid=35536 View this thread: http://www.excelforum.com/showthread...hreadid=555346 |
Nesting and Naming help!
problem with this is, I also need conditions that allow non-whole
numbers. For example, if i input 32.2, it should return 9 and so on... It does! And 31.9 =8!! Also I want the code to be flexible because some exercises require a different value. For example the sprint exercise. Less time means more points (10) and more time means less points ( 1 ). What are the different criteria? The range of formulae that have been posted here should be more than enough to supply the flexibility you require. They just need to be adapted to suit your particular needs. HTH Martin |
Nesting and Naming help!
i did babycody's tip and it worked for most of the exercises. It did not work for the 300yard shuttle The input and output is shown below Code: -------------------- 300-YARD SHUTTLE ≤ 45.9 10 46-47.9 9 48-49.9 8 50-51.9 7 52-53.9 6 54-55.9 5 56-57.9 4 58-59.9 3 60-61.9 2 ≥ 62.0 1 -------------------- I tried the F9 thing and placed all the number from 46, 46.1,46.2 ..... up to 61.9 on one column and the correspoding output on the other and did the f9 thing it said that the formula was too long. Is there a way for it to look up for a certain range? like in this case 46-47.9 then it would output 9 instead of typing 46,8;46.1;8 etc... Thanks. You guys are a lot of help. -- t43m4n ------------------------------------------------------------------------ t43m4n's Profile: http://www.excelforum.com/member.php...o&userid=35536 View this thread: http://www.excelforum.com/showthread...hreadid=555346 |
| All times are GMT +1. The time now is 04:44 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com