Advanced Formula troubles

[Eves]

I need to do the following calculation:

((1-((1-AE5)*10))*V14)

but only when:

((1-((1-AE5)*10))*V14)>=0 or <=V14*1.5

If greater than or = to V14*1.5 then =V14*1.5
And if less than or = 0 then =0

 

[Jacob Skaria]

Replace CALC with your calculation

=IF(AND(CALC>=0,CALC<=v14*1.5),CALC,IF(CALC<0,0,CALC))

Your last statement is confusing. "And if less than or = 0 then =0"...equal
to zero is mentioned in the 1st condition..So this should be 'less than' .

 

[Joe User]

((1-((1-AE5)*10))*V14)
but only when:
((1-((1-AE5)*10))*V14)>=0 or <=V14*1.5
If greater than or = to V14*1.5 then =V14*1.5
And if less than or = 0 then =0

I think the following does what you want:

=max(0, min(V14*1.5,(1-(1-AE5)*10)*V14))

Note that it can be simplified to:

=max(0, V14*min(1.5,10*AE5-9))

I discovered that the two formulas are infinitesimally different in some
cases, due to floating-point computation anomalies. But in fact, I think the
second formula is closer to the expected behavior.

Based on your constraints, we expect 1.5*VE14 when AE5>=1.05 (and VE14>0),
and we expect zero when AE5<=0.9 (or VE14<=0).


----- original message -----

 

[Joe User]

Errata (typo)....

Based on your constraints, we expect 1.5*VE14 when AE5>=1.05
(and VE14>0), and we expect zero when AE5<=0.9 (or VE14<=0).

Obviously, I meant: we expect 1.5*V14 when AE5>=1.05 (and V14>0), and we
expect zero when AE5<=0.9 (or V14<=0).


----- original message -----