AVERAGEIFS with date range condition
Hi all,
Would appreciate help with making this formula work:
=AVERAGEIFS([AVG range];[criteria 1 range - numbers];[criteria 1: all empty cells];[criteria 2 range - dates]; [dates before <22.01.2014 and after 04.01.2015])
right now written as =AVERAGEIFS($D$3:$CQ$3;$D$5:$CQ$5;"";$D$2:$CQ$2; "OR(<22.01.2014; >04.01.2015)")
but it does not work this way... must be some stupid mistake. Please, help!
Thanks much,
D
It seems like the problem with the syntax
Try it like this
=AVERAGEIFS($D$3:$CQ$3;$D$5:$CQ$5;"=""";$D$2:$CQ$2;"<22.01.2014";$D$2:$CQ$2;">04.01.2015")
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Thanks a lot @AlKey,
also gives #DIV/0 error
If your date ranges are using true dates, you may wish to use SUMPRODUCT instead to capture a real OR expression.
Please Login or Register to view this content.
Make Mom proud: Add to my reputation if I helped out!
Make the Moderators happy: Mark the Thread as Solved if your question was answered!
another try
=AVERAGEIFS($D$3:$CQ$3;$D$5:$CQ$5;"=""";$D$2:$CQ$2;">"&DATE(2014;1;22);$D$2:$CQ$2;"<"&DATE(2015;1;4))
Last edited by AlKey; 02-09-2015 at 11:52 AM.
I'm not sure there's an intersection between the low and high range.
I think you are right. they need to be reversed.Originally Posted by daffodil11
nope does not work either
sucks that excel gives no clue where the mistake may lie
Did you try the SUMPRODUCT method?
AverageIF, SumIf, and CountIf are ready-made varieties of SUMPRODUCT to encourage the use of array enabled formulas, but as you've seen they lack in providing useful feedback for why they aren't working.
On the other hand, SUMPRODUCT does produce useful outputs that can be readily debugged for points of failure. The most likely culprit is that your base formula is asking for the intersection between numbers that are both greater than X and less than X, and there are none.
AverageIf logic follows the sum of values that meet criteria, divided by their count. Since there is no natural number that is simultaneously >x and <x, you'll have to use an expression that effectively applies OR logic, or you'll always be dividing by zero.
I've attached an example illustrating the comparison.
Last edited by daffodil11; 02-09-2015 at 12:13 PM.
i will try, thank you!
quick feedback:
it worked as =SUMPRODUCT(--($D$5:$CQ$5="");(($D$2:$CQ$2<DATE1)+($D$2:$CQ$2>DATE2));($D$3:$CQ$3))/SUMPRODUCT(--($D$5:$CQ$5="");(($D$2:$CQ$2<DATE1)+($D$2:$CQ$2>DATE2)))
thanks for help!
There we go! Well done!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks