one cels value will determine which 1 of 2 formulas to use based on its value

[chrisnak]

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 athletes 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) each round 2 people would move through to the next round.

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

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

If heats of 32
1-8 will be 4
9-16 it will be 2
17-32 it will be 1

If heats of 16
1-8 it will be 3
9-16 it will be 1

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

the formula that I am using for this is
2*($G93<13) + ($J93>12)*($G93<25)+($J93>24)*($G93<49)+($J93>50)


somehow I need to combine those two formulas (or have a totally different one) so I can use it in one cell. it needs to figure out if it is one of the two formats

I was thinking that I would need to add a column that would determine if it was 6 or 4 man heats and that can be a criteria of what formula to use...... so
if cell E1=4 then use 2*($B2<9) + ($C2>8)*($B2<17)+($C2>16)*($B2<33)+($C2>32)*($B2<65)+($C2>64)*($B2<129)+($C2>128)

If Column E1=6 then use 2*($G93<13) + ($J93>12)*($G93<25)+($J93>24)*($G93<49)+($J93>50)

for an example


1 A B C D

2 Name	placing	qualified #	heats ridden
3 joe	2	4
4 joe	2	8
5 joe	2	16
6 joe	2	32
7 joe	2	48
8 joe	2	64
9 joe	2	128
10
11 joe	2	24
12 joe	2	48

<!--StartFragment--> <colgroup><col width="87" span="4" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

Column B is the athletes placing. Column C is the format for qualifying and then Column D is where the formula needs to go base on the value in column C so one of the top two formulas (or a better working one)

hopefully this makes sense

thanks

 

[Special-K99]

This part doesnt make sense

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

25 is less than 48 but youve said you want it to be 1, then you say less than 48 is 0, so which is it, cant be both?

If heats of 16 < ============ Shouldnt this be 24 ???
1-12 it will be 3
13-24 it will be 1
less then 24 will be 0


13 is less than 24 but youve said you want it to be 1, then you say less than 24 is 0, so which is it, cant be both?

Might be better to create a table and do a INDEX(MATCH().MATCH())

 

[chrisnak]

Sorry that is a mistake.... it was a hard one to explain. it should be MORE then 48 and More then 24 (that is from qualifying so those that don't get in the top 24 or 48 wont be in finals) and yes that 16 should be 24

sorry for the confusion.