Results 1 to 4 of 4

Thread: Probably simple, but getting a "#VALUE!" error please help

  1. #1

    Probably simple, but getting a "#VALUE!" error please help



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am getting a #VALUE! when trying to compute this formula

    =IF(B10="1",AVERAGE(C204:C207)),IF(B10="2",AVERAGE(C208:C211)),IF(B10="3",AVERAGE(C212:C215)),IF(B10="4",AVERAGE(C216:C219)),IF(B10="5",AVERAGE(C220:C223)),IF(B10="6",AVERAGE(C224:C227)),IF(B10="7",AVERAGE(C228:C231)),IF(B10="8",AVERAGE(C232:C235)),IF(B10="9",AVERAGE(C236:C239)),IF(B10="10",AVERAGE(C240:C243)),IF(B10="11",AVERAGE(C244:C247)),IF(B10="12",AVERAGE(C248:C251))

    What am I doing wrong? I am trying to make it so when I change the number in cell B10 it changes the cells that it is averaging. Maybe I am trying to do the wrong formula?? Please help me.

    Thank you everyone in advance!

    --Chace

  2. #2
    Conjurer sambit's Avatar
    Join Date
    Sep 2014
    Posts
    143
    Articles
    0
    Excel Version
    Excel2007
    Try:
    =IF(B10=1,AVERAGE(C204:C207),IF(B10=2,AVERAGE(C208:C211),IF(B10=3,AVERAGE(C212:C215),IF(B10=4,AVERAGE(C216:C219),IF(B10=5,AVERAGE(C220:C223),IF(B10=6,AVERAGE(C224:C227),IF(B10=7,AVERAGE(C228:C231),IF(B10=8,AVERAGE(C232:C235),IF(B10=9,AVERAGE(C236:C239),IF(B10=10,AVERAGE(C240:C243),IF(B10=11,AVERAGE(C244:C247),IF(B10=12,AVERAGE(C248:C251)))))))))))))

  3. #3
    Wizard Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,615
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Perhaps we could make this much shorter
    Code:
     =(B10=1)*AVERAGE(C204:C207)+(B10>1)*average(index($C$204:$C$251,,4*(B10-1)):index($C$204:$C$251,,4*B10)

  4. #4
    That worked Sambit, thank you so much you solved all of my life problems!!
    Last edited by Chace; 2015-03-25 at 11:16 PM. Reason: recognition

Posting Permissions

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