Nested If, and & OR statements

[louisepr]

Hi

I am hoping someone can help me with a formula that will address the following conditions. I can get each one to work individually but I cannot get the order right when I combine them- i either end up with a false or an error message telling me to add parentheses etc
For each unique staff member (column G) If the sum of amount (column T) = 0
if the rate (column S) - Standard Hourly Rate (Column AA)* Time Code Factor (column AB) (rounded to 2 decimal places) is greater than or equal to the value in rndmin and less than or equal to the value in rndmax
if the Default Rate (Column AC) or the Default Amount (Column AD) = the Rate (column S)

if these conditions are met, leave a blank, if these conditions are not met, return "Check"

examples of where I could get bits to work are below - but I just cant combine them

Thanks in advance

IF(AND((S13-ROUND(AA13*AB13,2))>=rndmin,(S13-ROUND(AA13*AB13,2))<=rndmax)," ","Check") IF(OR(S13=ROUND(AA13*AB13,2),S13=AC13,S13=AD13)," ","Check")

 

[KRice]

Based on the post from @Alex Blakenburg, I think that should work...and the other you posted needs a right parentheses between M13 and =0, as in
=IF(OR(SUMIFS(T:T,G:G,G13,M:M,M13)=0,S13=AC13,S13=AD13),"",IF(ABS(S13-ROUND(AA13*AB13,2))<0.05,"","Check"))

 

[louisepr]

You have already done the work for the modification you just need to replace the T13 = 0 with your sumifs.
See below.

=IF(OR(
AND((S13-ROUND(AA13*AB13,2))>=rndmin,(S13-ROUND(AA13*AB13,2))<=rndmax),
S13=ROUND(AA13*AB13,2),
S13=AC13,
S13=AD13,
SUMIFS(T:T,G:G,G13,M:M,M13)=0),
" ","Check")

 

[louisepr]

You have already done the work for the modification you just need to replace the T13 = 0 with your sumifs.
See below.

=IF(OR(
AND((S13-ROUND(AA13*AB13,2))>=rndmin,(S13-ROUND(AA13*AB13,2))<=rndmax),
S13=ROUND(AA13*AB13,2),
S13=AC13,
S13=AD13,
SUMIFS(T:T,G:G,G13,M:M,M13)=0),
" ","Check")

That worked perfectly thank you Now i will spend some time understanding it

 

[Alex Blakenburg]

That worked perfectly thank you Now i will spend some time understanding it

Let me know if you need anything explained.
OR means if "any 1" of the conditions is true then do the true part of the if statement.
Each of the following 5 lines is a condition that can be true or false.

The first line has AND which means both conditions on that line have to be true for that line to evaluate as true. By using a >= & <= you are effectively saying between

The last is the SUMIFS line and its format is Sumifs(Column to total, 1st Col to evaluate, 1st column criteria, 2nd Col to evaluate, 2nd column criteria)

 

[KRice]

And if you want to eliminate the high/low tolerances and replace them with a single tolerance, which also eliminates the AND...

=IF(OR(SUMIFS(T:T,G:G,G13,M:M,M13)=0,S13=AC13,S13=AD13,ABS(S13-ROUND(AA13*AB13,2))<0.05),"","Check")

 

[louisepr]

=IF(OR(SUMIFS(T:T,G:G,G13,M:M,M13=0,S13=AC13,S13=AD13),"",IF(ABS(S13-ROUND(AA13*AB13,2))<0.05,"","Check"))

The SUMIFS function function is a problem...those conditions won't work, but I don't understand what you really want. Check the SUMIFS for a right closing parentheses.

Thank you - yes, you are correct - it didn't function the way I expected - I still have a lot to learn about multiple ifs and how they all work. Alex's formula got me where I needed to be, but thank you so much for taking the time to respond, and giving me more to think about and learn - I will definitely look at the abs function in the future

 

[louisepr]

Let me know if you need anything explained.
OR means if "any 1" of the conditions is true then do the true part of the if statement.
Each of the following 5 lines is a condition that can be true or false.

The first line has AND which means both conditions on that line have to be true for that line to evaluate as true. By using a >= & <= you are effectively saying between

The last is the SUMIFS line and its format is Sumifs(Column to total, 1st Col to evaluate, 1st column criteria, 2nd Col to evaluate, 2nd column criteria)

Thank you. I had in my head that the sumifs needed to be at the beginning of the formula, so its now about understanding the order

And if you want to eliminate the high/low tolerances and replace them with a single tolerance, which also eliminates the AND...

=IF(OR(SUMIFS(T:T,G:G,G13,M:M,M13)=0,S13=AC13,S13=AD13,ABS(S13-ROUND(AA13*AB13,2))<0.05),"","Check")

That also worked perfectly Thank you