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]

The other possibility is rounding errors.

 

[GCLIFTON]

When i attempted to run that formula it continuous gave me You formula is missing a parenthesis even after i add the parenthesis. I copied and paste your formula and it stil populated that messages.

I am not sure how to attached a file in Mr. Exel I know i can attached one in Excel Forum. It is under the title Round and Sumif Question under Excel Formulas and functions.

 

[sstiebinger]

Sorry, was actually missing 2 )) at the end.

This should work:

=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]

I tried but kept getting errors. If you would like to see it and try it An attached file and all is located here

Round and SUMIF Question

The topic is Round and Sumif Question

 

[sstiebinger]

If I add this to a new column as a sanity check

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

Then this to a new column

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

It shows "Zero" for zero and "Not between -5 and 5" for everything else (which it should, because nothing else is between those numbers...)

The formula works for the criteria you have defined... are you defining the right criteria?

 

[GCLIFTON]

Ok I see what you are saying. I think i have the wrong criteria. I have been meaning for it to say, per the common [Complex CC], Per each amount in that common [complex cc], sum with the other amounts in that common [complex cc] to find a sum of 0 and or =>-5 or =<5. And label those. Which is slightly different than the other criteria.

For instance row 359 to 376. Event though it is 18 lines totaling to 28.45 there are some values in the amount that sum up to Zero if the criteria where to search each amount by the Common [Complex CC] and then sum and label the zero or +\- <> of 5's than this would be the correct criteria to use.

How would that change the previous formula? I totally understand what you are saying now

 

[sstiebinger]

so.. in the range; find the 2 values that, when summed, equal 0?