Hello guys,
It is a bit complicated, so I will try to explain the problem in more details and I also attached a file:
In column F, K,P I have a list of values
F K O P Q R S T
100 20
0,00 8,50 2
0,00 12,42 2
0,00 16,50 2
0,00 20,67 2
0,00 25,83 2
2,42 30,83 2
8,00 35,75 2
11,33 40,50 2
15,08 45,00 2
18,58 51,50 2
22,58 58,75 2
in T I put a formula which has to calculate the data from F,K,P when some conditions are fulfill:
- The value in T is in the range of [$O$74; $Q$73] if it is not there are two options
o To be > $Q$73 and then has to return $Q$73
o Or to be < $O$74 and in this case has to return $O$74
- If the first value in P, P76 =1 then return the value from $O$74, if in column down P is again =1 it has to return the value from the cell above;
- If P76=3 then do T75+(F76)-K76;
- If P76=2 then do T75-(K76-(F76));
When the calculations starts there will be a case in which the calculated value will be
- Less than $Q$73 then it has to return $Q$73 to the moment in which the value starts to grow again.
- Bigger than $O$74 then it has to return $O$74 to the moment in which the value starts to decrease again.
I try to do it with:
=IF(P76=3;$Q$73+T75+SUM(F76)-K76;IF(AND(T75<=$O$74;T75>=$Q$73);IF(P76=1;T75;IF(P76=3;T75+SUM(F76)-K76;IF(P76=2;T75-(K76-SUM(F76)))))))
But when the value starts to grow it exceeds the limit of $O$74 and the second problem is that when the value is less than $Q$73 it returns false instead of $Q$73
Hope someone can give me a hand,
Bookmarks