Greater but less than formula

[GCLIFTON]

Need guidance to how to make this formula read = 0 and Less than 5.00 but greater than -5 so -5><5

Also is there a way to have it label the ones summing to 0 "Zero" and label the ones less than 5 but greater than-5 "Less 5"

=ROUND(SUMIFS([Amount],[Financial Complex],[@[Financial Complex]]),2)=0

 

[sstiebinger]

if(ROUND(SUMIFS([Amount],[Financial Complex],[@[Financial Complex]]),2)=0,"Zero",if(and(ROUND(SUMIFS([Amount],[Financial Complex],[@[Financial Complex]]),2)>-5,ROUND(SUMIFS([Amount],[Financial Complex],[@[Financial Complex]]),2)<0),"Less 5",if(and(ROUND(SUMIFS([Amount],[Financial Complex],[@[Financial Complex]]),2)>0,ROUND(SUMIFS([Amount],[Financial Complex],[@[Financial Complex]]),2)<5),"between zero and 5","Not between -5 and 5")

 

[GCLIFTON]

If is part of example of my data. The formula is throwing off a parenthesis error. I know there is 1 parenthesis missing after "zero") but after that i dont know where the formula is error-ing out. The error is stating that it is missing an parenthesis


[TABLE="width: 775"]
<tbody>[TR]
[TD]Financial Complex[/TD]
[TD]OPERATION/NAME[/TD]
[TD]GL ACCOUNT[/TD]
[TD]OPERATION[/TD]
[TD]TransactionDate[/TD]
[TD]Amount[/TD]
[TD]Column1[/TD]
[/TR]
[TR]
[TD="align: right"]1601[/TD]
[TD]1601-Auto Club of S Cal[/TD]
[TD="align: right"]104126[/TD]
[TD="align: right"]1601[/TD]
[TD="align: right"]9/19/2016[/TD]
[TD="align: right"]-20[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]1715[/TD]
[TD]1715-Raytheon Lemmon[/TD]
[TD="align: right"]102190[/TD]
[TD="align: right"]1715[/TD]
[TD="align: right"]10/20/2016[/TD]
[TD="align: right"]519.04[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]1715[/TD]
[TD]1715-Raytheon Lemmon[/TD]
[TD="align: right"]102190[/TD]
[TD="align: right"]1715[/TD]
[TD="align: right"]10/20/2016[/TD]
[TD="align: right"]-100[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]1741[/TD]
[TD]1741-Armstron World Indst[/TD]
[TD="align: right"]101552[/TD]
[TD="align: right"]1741[/TD]
[TD="align: right"]10/19/2016[/TD]
[TD="align: right"]354.06[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]1741[/TD]
[TD]1741-Armstron World Indst[/TD]
[TD="align: right"]101552[/TD]
[TD="align: right"]1741[/TD]
[TD="align: right"]10/20/2016[/TD]
[TD="align: right"]446.32[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]1763[/TD]
[TD]1763-Ziegler[/TD]
[TD="align: right"]104785[/TD]
[TD="align: right"]1763[/TD]
[TD="align: right"]10/18/2016[/TD]
[TD="align: right"]352.89[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]1763[/TD]
[TD]1763-Ziegler[/TD]
[TD="align: right"]104785[/TD]
[TD="align: right"]1763[/TD]
[TD="align: right"]10/19/2016[/TD]
[TD="align: right"]344.04[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
</tbody>[/TABLE]

 

[sstiebinger]

if(ROUND(SUMIFS([Amount],[Financial Complex],[@[Financial Complex]]) ,2)=0,"Zero",if(and(ROUND(SUMIFS([Amount],[Financial Complex],[@[Financial Complex]]),2)>-5,ROUND(SUMIFS([Amount],[Financial Complex],[@[Financial Complex]]),2)<0),"Less 5",if(and(ROUND(SUMIFS([Amount],[Financial Complex],[@[Financial Complex]]),2)>0,ROUND(SUMIFS([Amount],[Financial Complex],[@[Financial Complex]]),2)<5),"between zero and 5","Not between -5 and 5")

Just missing the final ")" at the end to close the initial IF (purple)

 

[FDibbins]

If you are only looking for 1 of those 2 returns, then perhaps this...

=if(ROUND(SUMIFS([Amount],[Financial Complex],[@[Financial Complex]]),2)=0,"ZERO","Less 5")

Otherwise, I would make a small table with your targets and text, and use vlookup

 

[GCLIFTON]

Also, Why is this only showing me the most 150 lines. My data base is 30,000 lines i am quite sure there are more than 150

 

[FDibbins]

Do any of the suggested formulas work for you?
Did you make sure the formula was copied all the way down?

 

[GCLIFTON]

No for some reason it is skipping data that clearly is zero. Do you have an idea why it may be doing this.

 

[GCLIFTON]

No for some reason it is skipping data that clearly is zero. Do you have an idea why it may be doing this.

The formula needs to have the logic If the Financial Complex are the same Look at the amounts and find all the sum that are equal to Zero and mark them by saying Zero

Then the same logic but saying now give me the sums that are >-5 (Which means -5,-4,-3,-2,-1) correct? and <5. So these values should all equal like -2.65 or 4.26 etc.

So i am aware that i probably need two formulas .
1. =0
2. >-5 and <5

 

[sstiebinger]

My original formula caught all cases.
"Zero", "Less 5", "between zero and 5", "Not between -5 and 5"
First checked for Zero, if not zero check if between -5 and 0, if not then check between 0 and 5, if not return everything else (not between -5 and 5)
My formula also checked greater than and Less than, but not greater than or equal or less than or equal, so if it was exactly 5 or -5 it would return "not".

Did you try that formula?