I am looking to adjust the following formula or find a better way to get the data i want.
What I am trying to do is find a way to calculate the amount of heats that athlete are doing based on their final result and the amount the competition is taking into finals. Normally they run heats with 4 people and the options are 4, 8, 16, 32, 48, 64, 128
so the Number of heats would be for each (placing is on the left and # of heats is on the right)
If heats of 128
1-8 will be 6
9-16 it will be 4
17-32 it will be 3
33-64 it will be 2
65-128 it will be 1
less then 16 will be 0
If heats of 64
1-8 will be 5
9-16 it will be 3
17-32 it will be 2
33-64 it will be 1
less then 64 will be 0
If heats of 32
1-8 will be 4
9-16 it will be 2
17-32 it will be 1
less then 16 will be 0
If heats of 16
1-8 it will be 3
9-16 it will be 1
less then 16 will be 0
If heats of 8
1-8 it will be 2
If heats of 4
1-8 it will be 2
the formula that I am using is this
2*($B2<9) + ($C2>8)*($B2<17)+($C2>16)*($B2<33)+($C2>32)*($B2<65)+($C2>64)*($B2<129)+($C2>128)
which works for those ones.
where I am running into an issue is there is another format that will have heats that run as 6 man heats which is a totally separate format and the amount they would take to finals is 24, or 48 so similar to above but based on 6 man heats
If heats of 48
1-12 will be 4
13-24 it will be 2
25-48 it will be 1
less then 48 will be 0
If heats of 16
1-12 it will be 3
13-24 it will be 1
less then 24 will be 0
I need to have a formula that would be able to be in the same cell and if it were 4 man heats (4, 8, 16, 32, 48, 64, 128) then it would use one calculation but if it were to be (24, or 48) it would use another calculation.
I have included a file that hopefully shows it. Column B is the athletes placing. Column C is the format for qualifying and then Column D is where the formula needs to go D2-8 is filled in with my formula but I need the addition to it for D10,11 to work properly. so if cell C says 24 or 48 it calculates on the different criteria.
hopefully this makes sense
thanks
Bookmarks