Nesting and Naming help!

[t43m4n]

I am making an excel file that should convert the distance jumped by
person to a specific rank. For example if the distance was greater o
equal to 34, he would get a 10. and 34> distance >= 32, he would get
9 and so on...

Here's my dilema;

I need a formula that will should nest more than 7 functions, in thi
case it wont work right? So what I did was name 2 separate fuctions fo
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 th
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 wit
different values and exercises as well (sit ups, pull ups and s
on...).

All in all, I have 18 names for the 9 exercises. The problem is, when
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 nam
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), s
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? Ca
anyone point me to a tutorial? I'm just a teenager who has basic exce
knowhow.

If it helps, I attached the file.


Thanks,
Ped

+-------------------------------------------------------------------
|Filename: ADMU BCPR Edited 2.zip
|Download: http://www.excelforum.com/attachment.php?postid=4936
+-------------------------------------------------------------------

 

[Bob Phillips]

=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)

 

[David Biddulph]

=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)))

 

[Don Guillett]

try
=(B8/2)-7

 

[babycody]

=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.

 

[t43m4n]

Thanks for all your tips. I will try it tomorrow when I have time.

 

[MartinW]

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

 

[Don Guillett]

Thanks for the catch. It probably also needs the min

=ROUNDDOWN(MIN((B8/2)-7,10),0)

 

[David Biddulph]

Thanks for the catch. It probably also needs the min

=ROUNDDOWN(MIN((B8/2)-7,10),0)

And the max.

 

[t43m4n]

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

 

[MartinW]

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

 

[t43m4n]

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 an
did the f9 thing it said that the formula was too long. Is there a wa
for it to look up for a certain range? like in this case 46-47.9 the
it would output 9 instead of typing 46,8;46.1;8 etc...


Thanks. You guys are a lot of help