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")

 

[Alex Blakenburg]

Will get back to you shortly

 

[KRice]

The two expressions inside your AND statement are the same except for the <= and >=, but what happens if the difference is very close to 0 but slightly positive or slightly negative. Your expression will flag that as an issue on one side of 0 or the other, depending on the specific condition, even though S13 and ROUND(AA13*AB13,2) are nearly the same. You might consider replacing rndmin and rndmax with a single positive rndtol value and using:
=IF(ABS(S13-ROUND(AA13*AB13,2))>=rndtol,"Check","")

And combining this with the other expression would be better done in this order...I think:
=IF(OR(S13=ROUND(AA13*AB13,2),S13=AC13,S13=AD13),"",IF(ABS(S13-ROUND(AA13*AB13,2))<rndtol,"","Check"))

 

[maabadi]

if your first row is 2 & Last row is 100, Then Try This at AE2:

=IF(AND(COUNTIF($G$2:G2,G2)=1,SUMIFS($T$2:$T$100,$G$2:$G$100,G2)=0,ROUND(AA2*AB2,2)>=$AI$2,ROUND(AA2*AB2,2)<=$AH$2,OR(AC2=S2,AD2=S2)),"","Check")

 

[Alex Blakenburg]

If you expand the formula bar to allow you to see multiple lines, and at logical break points enter alt+enter to give you a new line, the formula will be much easier to put together and review.
I have combined your 2 formulas and added T13 = 0 which was another of your conditions.

Review the logic on the below and if it looks correct copy it into the appropriate column on Row 13.
(as mentioned you will need to expand the formula box to see it all - effectively 5 conditions - if you can the AND / between condition as 1)

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

 

[louisepr]

The two expressions inside your AND statement are the same except for the <= and >=, but what happens if the difference is very close to 0 but slightly positive or slightly negative. Your expression will flag that as an issue on one side of 0 or the other, depending on the specific condition, even though S13 and ROUND(AA13*AB13,2) are nearly the same. You might consider replacing rndmin and rndmax with a single positive rndtol value and using:
=IF(ABS(S13-ROUND(AA13*AB13,2))>=rndtol,"Check","")

And combining this with the other expression would be better done in this order...I think:
=IF(OR(S13=ROUND(AA13*AB13,2),S13=AC13,S13=AD13),"",IF(ABS(S13-ROUND(AA13*AB13,2))<rndtol,"","Check"))

Thank you I think I almost have it - I wasn't very clear above - I didn't need the formula twice - I could only get one or the other to work

This formula works as I want to other than the rounding tolerance (the bold italicized bit is where I would like the tolerance to be)

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

I really like the idea of ABS function, that seems much simpler - thank you. Using your example and what I have already I have tried this

=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"))

but it doesn't work - I get the "there's a problem with this formula. Not trying to type a formula?" dialogue box

 

[louisepr]

if your first row is 2 & Last row is 100, Then Try This at AE2:

=IF(AND(COUNTIF($G$2:G2,G2)=1,SUMIFS($T$2:$T$100,$G$2:$G$100,G2)=0,ROUND(AA2*AB2,2)>=$AI$2,ROUND(AA2*AB2,2)<=$AH$2,OR(AC2=S2,AD2=S2)),"","Check")

Thank you, but the data will always be variable so I cant put a defined range in (I have made that mistake before )

 

[louisepr]

If you expand the formula bar to allow you to see multiple lines, and at logical break points enter alt+enter to give you a new line, the formula will be much easier to put together and review.
I have combined your 2 formulas and added T13 = 0 which was another of your conditions.

Review the logic on the below and if it looks correct copy it into the appropriate column on Row 13.
(as mentioned you will need to expand the formula box to see it all - effectively 5 conditions - if you can the AND / between condition as 1)

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

Thank you - I certainly like your suggestion of breaking the formula into lines - that makes much more logical sense in my brain. I like your formula, however I need the sum of the amount in T or each unique staff member (column G) to =0, not just the value in T to = 0

 

[KRice]

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

 

[Alex Blakenburg]

Thank you - I certainly like your suggestion of breaking the formula into lines - that makes much more logical sense in my brain. I like your formula, however I need the sum of the amount in T or each unique staff member (column G) to =0, not just the value in T to = 0

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")