Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Formula troubles | Excel Worksheet Functions | |||
Formula troubles | Excel Discussion (Misc queries) | |||
Advanced Conditional Formatting Ideas Needed! (ok, maybe not that advanced...) | Excel Discussion (Misc queries) | |||
Advanced Formula... | Excel Discussion (Misc queries) | |||
Formula Troubles........ | Excel Worksheet Functions |