LinkBack URL
About LinkBacks
Hi,
Can anyone explain why these two approaches has not return the same result. AVERAGE(IF()) has returned the wrong result.
=AVERAGEIF(F13:F63,"Officer",SZ13:SZ63)
{=AVERAGE(IF(F13:F63="Officer",SZ13:SZ63))}
Hi,
Can anyone explain why these two approaches has not return the same result. AVERAGE(IF()) has returned the wrong result.
=AVERAGEIF(F13:F63,"Officer",SZ13:SZ63)
{=AVERAGE(IF(F13:F63="Officer",SZ13:SZ63))}
In my simple testing, they report the same result. Can you post a sample workbook, your real one desensitized or a fake book with the same layout and data style, that demonstrates these two formulas showing different results?
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Hi JBeaucaire,
Attached herewith sample of my workbook. What went wrong with my data layout?
The AVERAGE(IF treats the blank cell as a zero; AVERAGEIF ignores it.
Bob A1: Blank E1: {=AVERAGE(IF($A$1:$A$2="Bob", $B$1:$B$2))} Bob A2: Input E2: =AVERAGEIF($A$1:$A$2, "Bob", $B$1:$B$2)
Last edited by shg; 09-01-2014 at 01:56 PM.
Entia non sunt multiplicanda sine necessitate
.....and if you want to get the same result use this version of the array formula
=AVERAGE(IF(F13:F63="Officer",IF(SZ13:SZ63<>"",SZ13:SZ63)))
that filters out the blanks to get the same result as AVERAGEIF
Audere est facere
Hi shg,
Thanks a lot. I have tried these but it has returned zero result
{=AVERAGE(IF(AND(F13:F63="Officer",SZ13:SZ63=">0"),SZ13:SZ63))]}
{=AVERAGE(IF(AND(F13:F63="Officer",SZ13:SZ63>0),SZ13:SZ63))}
Hi daddylonglegs,
Thanks a lot. it works fine
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks