Use If, And, Or in a formula multiple times

[HariniBetha]

Hello,

I need to come up with a formula that provides a result as below - can you please help?

Look for the average of B, C and D, if the average is less than $1000 and if C+D+E = 0, provide a result of -B else 0 AND if the average of B, C and D is greater than $1000 and if E = 0, provide a result of -D, else 0.

I basically have to combine both the formulas below - I am able to generate results separately, but I need the formula to look for both conditions. Please help!!!!!

=IF(AND((AVERAGE(C5:E5)<1000),(C5+D5+E5=0)),-B5, 0)
=IF(AND((AVERAGE(B4:D4)>1000),(E4=0)),-D4,0)

[jjhayes]

Maybe:

Please Login or Register  to view this content.

in f1 and copy down

[HariniBetha]

Thank you for your reply. The first part of the formula is working just fine, giving the correct result, but when the average is over 1000, I am getting a result of False. Any suggestions?

[jjhayes]

May have missed a parentheses...

Try this

Please Login or Register  to view this content.

[HariniBetha]

You are a genius!!! :-) This worked for me! I appreciate you taking time to look into this.

=IF(AND(AVERAGE(AC3:AE3)<1000,SUM(AD3:AF3)=0),-AC3,IF(AND(AVERAGE(AC3:AE3)>1000,AF3=0),-AE3,0))