Adding an OR statement into formula

**mack_76** · 01-21-2015, 08:52 AM

Hi

I have the following formula but I need to amend it with an OR. I would like the formula average totals if P = "T" and V = "T" but they don't both have to be "T". I have tried a couple of variations now but don't seem to be able to get the correct average??

=AVERAGE(IF(ISNUMBER('Before Oct13'!$AE$2:$AE$78),IF('Before Oct13'!$P$2:$P$78="T",IF('Before Oct13'!$V$2:$V$78="T",'Before Oct13'!$AE$2:$AE$78))))

Please help!
Many thanks
Mack_76

**samba_ravi** · 01-21-2015, 08:55 AM

Attach a sample file, and enter expected results manually

**mack_76** · 01-21-2015, 09:01 AM

1 Excel Query.xlsx

File attached

**Glenn Kennedy** · 01-21-2015, 09:26 AM

So if EITHER P= T, OR V = T, take an average.... is that what you want? If so, how do you want the #N/A errors in (e.g.) rows 16, 43, etc, handled?

**mack_76** · 01-21-2015, 11:12 AM

Hi

Yes that is correct. I want the formula to ignore errors which it does in my orignal formula?

**romperstomper** · 01-21-2015, 11:17 AM

Basically:

=AVERAGE(IF(ISNUMBER('Before Oct13'!$AE$2:$AE$78),IF(SIGN(('Before Oct13'!$P$2:$P$78="T")+('Before Oct13'!$V$2:$V$78="T")),'Before Oct13'!$AE$2:$AE$78)))

**mack_76** · 01-21-2015, 11:19 AM

Thank you, thank you, thank you...

**Glenn Kennedy** · 01-21-2015, 11:33 AM

One Q Romperstomper. What does SIGN do in this formula? Excluding it doesn't seem to make any difference...

I was working my way towards this when you posted....

**romperstomper** · 01-21-2015, 11:43 AM

You're correct - I wasn't really paying attention to the formula as written. I tend to multiply the conditions rather than using IF clauses, in which case you do need it to avoid double counting.