Advanced Formula troubles
 

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

Advanced Formula troubles
 

Replace CALC with your calculation

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

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' .


--
Jacob (MVP - Excel)


"Eves" wrote:

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

Advanced Formula troubles
 

"Eves" wrote:
((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 VE140),
and we expect zero when AE5<=0.9 (or VE14<=0).


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

"Eves" wrote:
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

Advanced Formula troubles
 

Errata (typo)....

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

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


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

"Joe User" <joeu2004 wrote in message
...
"Eves" wrote:
((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 VE140),
and we expect zero when AE5<=0.9 (or VE14<=0).


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

"Eves" wrote:
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