Condition calculation

[Cobber2008]

Im trying to do some calculations with some conditions.

For every row for the same Date and Time -

For each Rank from 1 -3 if

Rank 3 price is < 9 and

the difference between Rank4 price and Rank3 price is > 3

then the formula on this data is for rank 1 = 1/Rank1price/(sum(1/rank1price + 1/rank2price + 1/rank3price)). For rank 2 it is 1/Rank2price/(sum(1/rank1price + 1/rank2price + 1/rank3price)) for rank 3 then 1/Rank3price/(sum(1/rank1price + 1/rank2price + 1/rank3price)).

If the Rank 3 price is >=9 then only calculate for rank 1 and 2 so for rank 1 1/Rank1price/(sum(1/rank1price + 1/rank2price))

if the difference between rank 3 and rank 4 price is less than 3 then no calculation.s

It seems a bit complex but this spreadsheet will make it clearer. Thanks.

Date	Time	Price	Rank	Calculation
26/12/2023​	11:55:00 AM​	2.73​	1​	1/c2/(1/c2+1/c3+1/c4)
26/12/2023​	11:55:00 AM​	5.24​	2​	1/c3/(1/c2+1/c3+1/c4)
26/12/2023​	11:55:00 AM​	8.4​	3​	1/c4/(1/c2+1/c3+1/c4)	Rank 3 <9	Price diff b/w 3 and 4 > 3
26/12/2023​	11:55:00 AM​	10.13​	4​
26/12/2023​	11:55:00 AM​	13​	5​
26/12/2023​	11:55:00 AM​	15.06​	6​
26/12/2023​	11:55:00 AM​	21.18​	7​
26/12/2023​	11:55:00 AM​	38​	8​
26/12/2023​	11:55:00 AM​	59.33​	9​
26/12/2023​	12:30:00 PM​	2.47​	1​	1/E11/(1/E11+1/E12)
26/12/2023​	12:30:00 PM​	3.87​	2​	1/E12/(1/E11+1/E12)
26/12/2023​	12:30:00 PM​	9.5​	3​	price > 9 so exclude rank 3 and calc for 1/2 only
26/12/2023​	12:30:00 PM​	10​	4​
26/12/2023​	12:30:00 PM​	12​	5​
26/12/2023​	12:30:00 PM​	15​	6​
26/12/2023​	11:30:00 PM​	3​	1​
26/12/2023​	11:30:00 PM​	5​	2​
26/12/2023​	11:30:00 PM​	6​	3​	Diff between 3 and 4 is 1 so < 3 so no calculation for 1/2/3
26/12/2023​	11:30:00 PM​	7​	4​
26/12/2023​	11:30:00 PM​	12​	5​
26/12/2023​	11:30:00 PM​	15​	6​

 

[shknbk2]

I think I've come up with a formula that works. This would go in C2; then, copy down.

=IF(D2<=3,IF(OFFSET(D2,4-D2,-1)-OFFSET(D2,3-D2,-1)>3,IF(OFFSET(D2,3-D2,-1)<9,1/C2/(1/INDEX(OFFSET(C2:C4,1-D2,0),1)+1/INDEX(OFFSET(C2:C4,1-D2,0),2)+1/INDEX(OFFSET(C2:C4,1-D2,0),3)),IF(D2<3,1/C2/(1/INDEX(OFFSET(C2:C3,1-D2,0),1)+1/INDEX(OFFSET(C2:C3,1-D2,0),2)),"")),""),"")

However, there is a problem. Nothing shows up with the data you provide. Why? Because for each of the days, the Rank4 - Rank3 data is less than 3. You say that the price difference between 3 and 4 in your first example is > 3, but it isn't: 10.13-8.4 < 3.

So is 10-9.5 for day 2.

If you play with the numbers so R4 - R3 is greater than 3, then the data shows up.