Hey guys,
I am dealing with 10'000 plus lines and need to get a rolling 12 month average for items on another sheet, however I cannot interact with sheet2 due to the data overwrighting previous data daily. I am trying to make another sheet so it looks pretty.
With the averages I am trying to use the month, item and company to lookup using the pre made CAT that comes with the report sheet, however I cannot get the averages with AVERAGEIF since there are some items that are the same or similar and don't seem to work with what I am trying to do.
I have tried =AVERAGE(IF(ISNUMBER(MATCH(C:C,IF('Monthly Sale Averages cat'!E:E=C4,'Monthly Sale Averages cat'!G:G),0)),'Monthly Sale Averages cat'!G:G))
and
=AVERAGEIFS('Monthly Sale Averages cat'!G:G,'Monthly Sale Averages cat'!E:E,(CONCATENATE(D5,A7,B7))),0)
and
=AVERAGEIFS(INDEX('Monthly Sale Averages cat'!E:G,0,MATCH(CONCATENATE(C$2,A3256,B3256),'Monthly Sale Averages cat'!E3254:G3254,0)),'Monthly Sale Averages cat'!E:E=CONCATENATE($C$2,A3259,B3259))
but it doesn't appear to work.
any help appreciated!
I am dealing with 10'000 plus lines and need to get a rolling 12 month average for items on another sheet, however I cannot interact with sheet2 due to the data overwrighting previous data daily. I am trying to make another sheet so it looks pretty.
With the averages I am trying to use the month, item and company to lookup using the pre made CAT that comes with the report sheet, however I cannot get the averages with AVERAGEIF since there are some items that are the same or similar and don't seem to work with what I am trying to do.
I have tried =AVERAGE(IF(ISNUMBER(MATCH(C:C,IF('Monthly Sale Averages cat'!E:E=C4,'Monthly Sale Averages cat'!G:G),0)),'Monthly Sale Averages cat'!G:G))
and
=AVERAGEIFS('Monthly Sale Averages cat'!G:G,'Monthly Sale Averages cat'!E:E,(CONCATENATE(D5,A7,B7))),0)
and
=AVERAGEIFS(INDEX('Monthly Sale Averages cat'!E:G,0,MATCH(CONCATENATE(C$2,A3256,B3256),'Monthly Sale Averages cat'!E3254:G3254,0)),'Monthly Sale Averages cat'!E:E=CONCATENATE($C$2,A3259,B3259))
but it doesn't appear to work.
any help appreciated!