Why doesn't this formula work? function works seperately but not as part of a bigger function.

[Waboku]

Hello, thanks for considering helping me with this.

I have this function:

=IF(OR(A2={"-1",-2}),5,IF(AND(A2>10),X2*0.9,IF(AND(10>=A2>=3),A2-1,IF(AND(3>A2),((A2-1)/2*0.1667+0.5)*A2))))

Some of the parts are just there because I don't know how to correctly trim down a function someone helped me with. The part that results in 5 is not needed and I think some of the AND's aren't needed. The problem I'm having is the function for if 3>A2 doesn't return the correct outcomes. If the function ((A2-1)/2*0.1667+0.5)*A2 is used on its own it returns the correct results.

As part of the whole function it does this:

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }</style> [TABLE="width: 130"]
<colgroup><col style="width:65pt" width="65" span="2"> </colgroup><tbody>[TR]
[TD="width: 65, align: right"]Column A
2.5
[/TD]
[TD="width: 65, align: right"]
Returns
1.5
[/TD]
[/TR]
[TR]
[TD="align: right"]1.5
[/TD]
[TD="align: right"]0.5
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD="align: right"]0
[/TD]
[/TR]
</tbody>[/TABLE]

On its own:

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { }</style> [TABLE="width: 136"]
<colgroup><col style="width:65pt" width="65"> <col style="mso-width-source:userset;mso-width-alt:3029;width:71pt" width="71"> </colgroup><tbody>[TR]
[TD="width: 65"]Column A
[/TD]
[TD="width: 71"]Returns[/TD]
[/TR]
[TR]
[TD="align: right"]2.5
[/TD]
[TD="class: xl63, align: right"]1.56[/TD]
[/TR]
[TR]
[TD="align: right"]1.5
[/TD]
[TD="class: xl63, align: right"]0.81[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD="class: xl63, align: right"]0.50[/TD]
[/TR]
</tbody>[/TABLE]

The on its own results are what I'm looking for, if anyone knows why this is and how to fix it please let me know.

 

[Waboku]

Nevermind, sorry. I solved it.

 

[Michael M]

Maybe this

=IF(A2>10,X2*0.9,IF(AND(A2<=10,A2>=3),A2-1,IF(AND(A2<3),((A2-1)/2*0.1667+0.5)*A2)))

 

[Waboku]

Hello Michael M, thanks for your help. Because of your formula I was able to start making my own If(AND( functions.