PDA

View Full Version : How To Find an Average within a particular date range

Hello
2017-02-04, 11:30 AM
Hello!

So, I've spent hours and hours trying to figure this out and googling, etc.

I want to have the average amount of income in column C, provided the date in column B falls into the specified date range, appear in a given cell.

Cafe
23/11/2016
£43.13

Cafe
24/11/2016
£52.26

Cafe
25/11/2016
£40.00

Cafe
26/11/2016
£78.56

Cafe
28/11/2016
£54.87

Cafe
29/11/2016
£51.61

Cafe
30/11/2016
£74.01

Cafe
01/12/2016
£41.43

Cafe
02/12/2016
£59.46

Cafe
03/12/2016
£80.22

Cafe
05/12/2016
£103.71

Cafe
06/12/2016
£43.66

Cafe
07/12/2016
£61.13

Cafe
08/12/2016
£72.49

Cafe
09/12/2016
£145.77

Cafe
10/12/2016
£147.41

So for instance, I'd want the average price of all entries from November to appear in the corresponding cell in the "average" column. Like this:

Total
Average

Nov
1363.68

Dec
2539.6

What formula do I put in the empty cells to make this happen??

Thanks!

Pecoflyer
2017-02-04, 01:06 PM
Hi and welcome

you could use the AVERAGIFS function or eventually a Pivot Table

Hello
2017-02-04, 02:33 PM
Hey!

Thanks for your response. How would you suggest I use it? I've seen it being used and can't figure out how to get it to work for me.

Mike

p45cal
2017-02-04, 02:41 PM
Both (almost) Pecoflyer's suggestions are included in the attached.
The pivot table is by far the safest solution.
I couldn't get averageifs to work (nor sumifs), but I could be missing something.

AliGW
2017-02-04, 03:23 PM
Using P45cal's workbook, using AVERAGEIFS:

=AVERAGEIFS(\$C\$2:\$C\$17,\$B\$2:\$B\$17,">="&"01/"&MONTH(\$F13)&"/"&YEAR(\$F13),\$B\$2:\$B\$17,"<="&DAY(EOMONTH(\$F13,0))&"/"&MONTH(\$F13)&"/"&YEAR(\$F13))

p45cal
2017-02-04, 03:43 PM
Using P45cal's workbook, using AVERAGEIFS:

=AVERAGEIFS(\$C\$2:\$C\$17,\$B\$2:\$B\$17,">="&"01/"&MONTH(\$F13)&"/"&YEAR(\$F13),\$B\$2:\$B\$17,"<="&DAY(EOMONTH(\$F13,0))&"/"&MONTH(\$F13)&"/"&YEAR(\$F13))

Very good! (but you can see why I gave up!).
Prompted by you I found it could be shortened to:
=AVERAGEIFS(\$C\$2:\$C\$17,\$B\$2:\$B\$17,">="& \$F13,\$B\$2:\$B\$17,"<="& EOMONTH(\$F13,0))
(Excel 2010) which is more or less what I was hoping to do in the first place but it escaped me.

AliGW
2017-02-04, 03:47 PM
Yes. that's better! :)

Hello
2017-02-04, 03:55 PM
Hey guys!

Thanks for helping me out. I've copied and pasted both of your formulas and neither worked for me. I may be doing something wrong though??

AliGW
2017-02-04, 04:04 PM
Copy and paste won't be enough if the arrays referenced are different in your sheet. If you are unable to work it out for yourself, attach the workbook and one of us will check it for you.

Hello
2017-02-04, 04:42 PM
Oki doki. It's in the "Income" tab that I need the formula.

Has this worked?

AliGW
2017-02-04, 04:54 PM
The formula needs to be this:

=AVERAGEIFS(\$C\$2:\$C\$102,\$B\$2:\$B\$102,">="&\$E5,\$B\$2:\$B\$102,"<="&EOMONTH(\$E5,0))

This section is not correctly set up:

Excel 2016 (Windows) 32 bit

E

5

Nov

6

Dec

7

Jan

8

Feb

Sheet: Income

You need to refer to P45cal's sample sheet in post #4 to see how to set these cells up. Once done, the formula will work.

p45cal
2017-02-04, 05:02 PM
In attached, cells E5:G8 all updated.
Pivot in vicinity of E17

Your file reports as being damaged.

AliGW
2017-02-04, 05:14 PM
Yes, I got the damaged message, too.

Hello
2017-02-07, 09:07 AM
Thanks so much guys, I think this has worked! :)

AliGW
2017-02-07, 10:45 AM
You're welcome!

Hello
2017-02-07, 12:53 PM
Oh wait haha. It doesn't quite work because if I make two entries on the same day, it divides by the number of entries (rows), not the number of days in the month. I'd just divide by the number of days in the month if it wasn't for the fact that for instance, I need to know the daily average of February despite being only 7 days in.

Sorry, I didn't realise how complicated this was!

AliGW
2017-02-07, 01:42 PM
OK. Please provide a sample data file showing all the various scenario, results entered manually. Then we will be able to offer a solution. Before you post, make sure that you have covered all eventualities.