Hello All
I was looking for a similar problem from the forums, but I didn't find it. It might be a dummy question, but I didn't figure this out yet. Here is the problem:
There is a range with data (columns B:G) with a very inconsistent consumption by month. Need to find a way to calculate a better average due that distribution.
1st Condition: If Max value is located in the last column of the range (in this case column "G"), then the average will be between columns(F:G).
2nd condition: If Max Value is out of the last column, then I need to find in which column the Max Value is located and use that column to build my average formula. Average will be the Max Value Column to the last column ("G").
Here is my tentative:
=IF(MAX(B12:G12)=G12,AVERAGE(F12:G12),AVERAGE(INDIRECT(CELL("address",INDEX(B12:G12,MATCH(MAX(B12:G12),B12:G12,0)))),G12))
When I used the INDIRECT function I got the Value of the MAX value, not the expected ADDRESS to build the AVERAGE formula. When I try to use ADDRESS function, I get a #VALUE! error. Attached a sample file for reference.
Thanks
Bookmarks