Averageifs

[DWB82]

Hi All

I am looking for help building an averageifs formula.

The formula needs to average between 2 dates which will be across the top of the data, and take in to account a third variable which will be to the left of the formula.

This is the formula that I currently have: "=ROUNDUP(AVERAGEIFS('Indirect Overview'!$C3:$XFD3,'Indirect Overview'!$C$1:$XFD$1,">="&Sheet6!H$5,'Indirect Overview'!$C$2:$XFD$2,"<="&Sheet6!H$6),0)"

Any help that can be given would be great!

Thanks

D

 

[baitmaster]

unclear on what you're after

The formula seems to be averaging between two dates OK, so the only thing left is this "third variable", but you don't say how the variable should affect your answer? Is it a multiplier, another criteria that impacts which values to be included..?

 

[DWB82]

I will explain what I am trying to do hopefully that will help.

I have broke down the formula with below with explainations of what it should be doing:


=AVERAGEIFS(

This is the range of cells that contains all of the total values-'Indirect Overview'!$C3:$XFD25,

These are the cells that contain all of the dates - 'Indirect Overview'!$C$1:$XFD$1,">="&Sheet6!H$5,'Indirect Overview'!$C$2:$XFD$2,"<="&Sheet6!H$6,

This is the third criteria that is horizontal - 'Indirect Overview'!$B$3:$B$25,Sheet6!$E7)


The part that I am struggling with is the horizontal data...

Thanks again for any help you can

 

[Aladin Akyurek]

Does Sheet6!$E7 occur once in 'Indirect Overview'!$B$3:$B$25 just once or can that occur more than once?

 

[DWB82]

Hi

Yes the value in Cell E7 would only occur in the lookup range once.

Thanks

Dominic

 

[Aladin Akyurek]

Hi

Yes the value in Cell E7 would only occur in the lookup range once.

Thanks

Dominic

Let's try:

=ROUNDUP(AVERAGEIFS(
    INDEX('Indirect Overview'!$C3:$XFD3,MATCH(Sheet6!$E7,'Indirect Overview'!$B$3:$B$25,0),0),
    'Indirect Overview'!$C$1:$XFD$1,">="&Sheet6!H$5,'Indirect Overview'!$C$2:$XFD$2,"<="&Sheet6!H$6),0)

 

[DWB82]

Let's try:

=ROUNDUP(AVERAGEIFS(
    INDEX('Indirect Overview'!$C3:$XFD3,MATCH(Sheet6!$E7,'Indirect Overview'!$B$3:$B$25,0),0),
    'Indirect Overview'!$C$1:$XFD$1,">="&Sheet6!H$5,'Indirect Overview'!$C$2:$XFD$2,"<="&Sheet6!H$6),0)

I have used the code above when the value in E7 changes I get a "#REF!" error...

 

[Aladin Akyurek]

I have used the code above when the value in E7 changes I get a "#REF!" error...

Edited fot the range to sum, see bold...

=ROUNDUP(AVERAGEIFS(
INDEX('Indirect Overview'!$C$3:$XFD$25,MATCH(Sheet6!$E7,'Indirect Overview'!$B$3:$B$25,0),0),
'Indirect Overview'!$C$1:$XFD$1,">="&Sheet6!H$5,'Indirect Overview'!$C$2:$XFD$2,"<="&Sheet6!H$6),0)