+ Reply to Thread
Results 1 to 11 of 11

Average using Cells Reference

  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

  2. #2
    Registered User
    Join Date
    01-05-2022
    Location
    Indonesia
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    51

    Re: Average using Cells Reference

    I tried and found no answer, so I did in native way, not so helpful btw (especially if there's addition in months, the formula should be updated too)

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I'm not an expert, but I'll try

  3. #3
    Forum Expert CheeseSandwich's Avatar
    Join Date
    12-22-2021
    Location
    Kent, England
    MS-Off Ver
    365 - 2501
    Posts
    1,431

    Re: Average using Cells Reference

    Maybe this in H12:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    No need to copy down
    Last edited by CheeseSandwich; 03-30-2023 at 03:32 AM.
    If things don't change they stay the same

  4. #4
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2019
    Posts
    4,542

    Re: Average using Cells Reference

    Try, =IF(G12=MAX(B12:G12),AVERAGE(F12:G12),AVERAGE(OFFSET(B12,0,0,1,MATCH(MAX(B12:G12),B12:G12,0))))

  5. #5
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Average using Cells Reference

    Please try

    =IF(MATCH(MAX(B12:G12),B12:G12,0)=COLUMNS(B12:G12),AVERAGE(MAX(B12:G12),INDEX(B12:G12,1,MATCH(MAX(B12:G12),B12:G12,0)-1)),
    AVERAGE(INDEX(B12:G12,1,MATCH(MAX(B12:G12),B12:G12,0)):G12))

    Regards.
    Last edited by menem; 03-30-2023 at 02:50 AM.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Average using Cells Reference

    or simply...

    =AVERAGE(INDEX(B12:G12,,MIN(5,MATCH(MAX(B12:G12),B12:G12,0))):G12)

    copied down...
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU

  7. #7
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2019
    Posts
    4,542

    Re: Average using Cells Reference

    Oh, I read the requirements wrongly. Good formula, Glenn!

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

    Re: Average using Cells Reference

    Hi Guys
    Thanks so much for all your support. I tested all the proposals and they worked perfectly.
    I'm going to use Glenn's suggestion which is FANTASTIC!!! Simple, direct and precise.
    It amazes me how you guys make tasks seem simple.
    Thanks again and have a wonderful rest of the week.

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

    Re: Average using Cells Reference

    Glenn,
    You ROCK!!!

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Average using Cells Reference

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

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

    Re: Average using Cells Reference

    Already done Glenn. Thanks again.

+ Reply to Thread

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