Results 1 to 11 of 11

Average using Cells Reference

Threaded View

  1. #1
    Registered User
    Join Date
    09-22-2009
    Location
    Yep
    MS-Off Ver
    Excel 365 Pro Plus
    Posts
    34

    Red face Average using Cells Reference

    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
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Taking the average of multiple groups of cells with ability to extend range of average
    By 2597Gar in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-29-2019, 11:07 AM
  2. Replies: 5
    Last Post: 08-11-2015, 04:45 PM
  3. [SOLVED] average if, reference to other cells
    By mkeys4 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-24-2014, 04:38 PM
  4. Replies: 4
    Last Post: 02-27-2014, 04:58 PM
  5. Replies: 14
    Last Post: 06-14-2013, 09:00 PM
  6. Replies: 2
    Last Post: 06-10-2011, 03:33 PM
  7. Average function with #VALUE! error in reference cells
    By Larry4500 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-16-2006, 05:50 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1