Calculate average of values greater than zero for ranges of dates

=averageifs($b$11:$b$270,$a$11:$a$270, ">="&a11, $a$11:$a$270, "<"&d12,$b$11:$b$270,">"&0)

did you try the formula with that range

=AVERAGEIFS(B1:B5,A1:A5, ">="&E2, A1:A5, "<="&F2,B1:B5,">"&0)
Note the > or >= , < or <= - if included the date
that maybe the difference

I get 0.89750
same as
(87+91+89+92)/4
same as average B2:B5

images dont help here
Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet.

A sample sheet would help here, and possibly will enable a quicker and more accurate solution for you.


The forum does allow for spreadsheets to be uploaded direct to the forum -

Please see the Yellow Banner at the top of the page explaining how to attach a sample spreadsheet

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Here are the instructions, found at the top of the page again

Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

Try:

=AVERAGEIFS(B:B,A:A,">="&A3,A:A,"<"&A3+7,B:B,">0")

did you see my post - with your example and the result - post 4 - all in a sample spreadsheet

you are only looking at full integers
BUT you data has decimals

70-79%, 80-89%, 90-99%
for example
will not include 79.001 to 79.9999

=COUNTIFS($E$11:$E$62,">=90%", $E$11:$E$62,"<=99%") but it is not accurate. I am getting 9 counts but should be 9

9 =9 ????

can you put the formulas into the example
not following the formula with the data
data starts at row 3- formula start at row 11

i rearrange the formula to
=COUNTIFS($E$3:$E$54,">=90%", $E$3:$E$54,"<=99%")
and got 2

That was because I am a complete idiot.

=AVERAGEIFS(B:B,A:A,">="&D3,A:A,"<"&D3+7,B:B,">0")

If you're only wanting to count % as integers, change the formula in E to:

=ROUND(AVERAGEIFS(B:B,A:A,">="&D3,A:A,"<"&D3+7,B:B,">0"),2)

then your countifs should work as planned.

You're welcome. Thanks for letting us know that you got an answer.

---

Please take a moment and consider clicking the "Add Reputation" button at the foot of any of the posts of anyone who helped you reach a solution here today.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.