• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Average If Question

M

Mike Langlois

Member
Hey all,

I am practicing with using averageif to calculate averages, and have come across a bit of a problem.

In one of my cells, I have a date, and in my second cell I have another date, and the last column is the difference between these days. (I know the dates are the first of the month, however this is meant to be like this)

What I am trying to accomplish is average column C (Difference Between Days) based on two dates.

For example, what is the average number of days where the first date is 01/02/2014 & 01/01/2014 and the second date is 01/01/2014?

I have managed to get it to count if it only needs to find one date in column A, but can't seem to get it to work to take into account 2+ (possibly up to 11) different dates in column A.

Is this possible? Did that make any sense?

Many thanks,
Mike
 

Attachments

Hi Mike ,

Can you give an explanation with an example ? I have not understood ; hopefully in case others have , they will respond.

Narayan
 
NARAYANK991 said:
Hi Mike ,

Can you give an explanation with an example ? I have not understood ; hopefully in case others have , they will respond.

Narayan
Click to expand...

Hi Narayan,

Apologises, I wasn't very clear.

On the attached example, I would like to know what the average difference between days (column c)is where the Second Month column (B) is equal to 01/07/2014, and the first month column (A) is 01/10/2014 and 01/01/2015.

I am able to calculate if I am only looking for 1 date in column A, but can't seem to work it out of I am looking for more than 1 date in column A?
 
Nebu said:
Hi:

Why can't you use a pivot table and filter the dates as per your requirement.

Thanks
Click to expand...

Because the formule I will be trying to create will be dynamic in the sense it will be looking for cells that are changed depending on other formulas in cells.

Basically I want to try and make it automated, to remove the manual process of filtering etc.
 
Hi Mike ,

Is this acceptable ?

=SUMPRODUCT((TEXT($A$2:$A$99,"mmm-yy")={"Oct-14","Jan-15"})*(TEXT($B$2:$B$99,"mmm-yy")="Jul-14")*$C$2:$C$99)/SUMPRODUCT((TEXT($A$2:$A$99,"mmm-yy")={"Oct-14","Jan-15"})*(TEXT($B$2:$B$99,"mmm-yy")="Jul-14"))

Narayan
 
NARAYANK991 said:
Hi Mike ,

Is this acceptable ?

=SUMPRODUCT((TEXT($A$2:$A$99,"mmm-yy")={"Oct-14","Jan-15"})*(TEXT($B$2:$B$99,"mmm-yy")="Jul-14")*$C$2:$C$99)/SUMPRODUCT((TEXT($A$2:$A$99,"mmm-yy")={"Oct-14","Jan-15"})*(TEXT($B$2:$B$99,"mmm-yy")="Jul-14"))

Narayan
Click to expand...

Hi Narayan,

That appears to work perfectly thank you. A question I have regarding it is, is it possible to (instead of saying Oct-14, Jan-15) to make them cell references. So for it to look at a cell and pick up the date (which will be in "mmm-yy" format) and look for that?

Many thanks again for all your help
 
NARAYANK991 said:
Hi Mike ,

Can you give an example ? How many cells will there be , 1 , 2 , 3 ,... ?

Narayan
Click to expand...

Hi Narayan,

I wish I could give you my original file, however as its for work unfortunately I can't share it. In the list of dates to check against (in the example, column A and B) there can be up to 60,000 rows. Because of the way the spreadsheet is currently created, the months I want the averages to check against could change, however the cell the month I want to check against will always be the same.

So I was wondering if its possible to have something like:

=SUMPRODUCT((TEXT($A$2:$A$99,"mmm-yy")={A1,B1})*(TEXT($B$2:$B$99,"mmm-yy")=C1)*$C$2:$C$99)/SUMPRODUCT((TEXT($A$2:$A$99,"mmm-yy")={A1,B1})*(TEXT($B$2:$B$99,"mmm-yy")=C1))

So instead of looking for "May-14" it looks at the cell A1 and looks for that?
 
Hi Mike ,

That is what I wanted to know ; will it be just 2 cells A1 and B1 , or can it be any more cells ? If so , what is the maximum number of cells ?

Will it be cells in a row , as A1 and B1 , or will it be cells down a column , such as A1 , A2 ,... ?

Narayan
 
Hi Narayan,

I have attached an example of the table I am trying to create and what it looks like. The top row will be 11 across at all times, and the length of possible data in the second sheet can go up to 60,000.

Does the descriptions I have written make sense in what I am trying to achieve with the formulae?

Many thanks,
Mike
 

Attachments

Hi Mike ,

I am having second thoughts about using formulae , since you mention that your file has 60,000 rows of data. I think the formulae will slow down your worksheet quite a lot.

Is it OK if we use helper columns ? What about VBA ?

Narayan
 
Hi Narayank,

Thanks very much for your help. We can use helper columns if that means it is possible with formulae, as I wouldn't be able to use VBA?
 
Hi Mike ,

Can you go through the attached file and confirm whether the SUMs are correct ?

If yes , then how is the average of these to be calculated ? Can you take an example of one SUM and indicate how the average in that cell will be calculated ?

As an example , indicate what will be the value in cell F6.

Narayan
 

Attachments

You must log in or register to reply here.
Back
Top