• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
  • Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

Any excel experts?

Collapse
X
  •  
  • Filter
  • Time
  • Show
Clear All
new posts

    Any excel experts?

    I have a spreadsheet that I am trying to tweak, I'm not a coder so stay away from VB and so far can do most things I want to excel logic.

    In the range P2:AB35 I have a series of numbers, each representing a year from 2013 onwards, what I want is the average of the last five years, to do this I have the following

    (Cell P1) =YEAR(TODAY())
    (Q1) P1-1
    (R1) Q1-1
    (S1) R1-1
    (T1) S1-1

    This gives me the current year and the last 4 that I can reference.

    I then use HLOOKUP to search a range for the values that refer to those years

    Code:
    =AVERAGE((HLOOKUP((P1),$P2:$AB35,5)),(HLOOKUP((Q1),$P2:$AB35,5)),(HLOOKUP((R1),$P2:$AB35,5)),(HLOOKUP((S1),$P2:$AB35,5)),(HLOOKUP((T1),$P2:$AB35,5)))
    (the 5 refers to the 5th row down under that year, the next time is 6 so is the sixth row down etc)

    This works perfectly, for those conditions with 5 years of entries, if you only have 1 year it will include the blanks and give you the average of year 1 + 4 years of zero, which works kinda but not ideal as it gives me a 5th of one year rather than the average of one year which is the same as one year.

    I want to try and ignore any years with blanks, this works perfectly,

    Code:
    =AVERAGEIF(P2:AB2,"<>")
    But won't limit myself to the last 5 years when it has enough data point.

    I can't seen to work out how to nest the two, is it even possible?
    Originally posted by Stevie Wonder Boy
    I can't see any way to do it can you please advise?

    I want my account deleted and all of my information removed, I want to invoke my right to be forgotten.

    #2
    If you're happy to send me the file, I'll sort it for you. It's easier to look at it than work it out third hand.

    Comment


      #3
      Rather than using Average, could you just do a Sum and then divide by CountIf > 0
      …Maybe we ain’t that young anymore

      Comment


        #4
        Originally posted by WTFH View Post
        Rather than using Average, could you just do a Sum and then divide by CountIf > 0
        Originally posted by ladymuck View Post
        If you're happy to send me the file, I'll sort it for you. It's easier to look at it than work it out third hand.
        Was easier than I thought, just needed an IF between the two options rather than trying to combine into one

        Code:
        =IF(COUNTA(P5:AB5)>5,AVERAGE((HLOOKUP((P1),$P2:$AB35,4)),(HLOOKUP((Q1),$P2:$AB35,4)),(HLOOKUP((R1),$P2:$AB35,4)),(HLOOKUP((S1),$P2:$AB35,4)),(HLOOKUP((T1),$P2:$AB35,4))),AVERAGE(P5:AB5))
        Originally posted by Stevie Wonder Boy
        I can't see any way to do it can you please advise?

        I want my account deleted and all of my information removed, I want to invoke my right to be forgotten.

        Comment


          #5
          Originally posted by SimonMac View Post
          I have a spreadsheet that I am trying to tweak, I'm not a coder so stay away from VB and so far can do most things I want to excel logic.

          In the range P2:AB35 I have a series of numbers, each representing a year from 2013 onwards, what I want is the average of the last five years, to do this I have the following

          (Cell P1) =YEAR(TODAY())
          (Q1) P1-1
          (R1) Q1-1
          (S1) R1-1
          (T1) S1-1

          This gives me the current year and the last 4 that I can reference.

          I then use HLOOKUP to search a range for the values that refer to those years

          Code:
          =AVERAGE((HLOOKUP((P1),$P2:$AB35,5)),(HLOOKUP((Q1),$P2:$AB35,5)),(HLOOKUP((R1),$P2:$AB35,5)),(HLOOKUP((S1),$P2:$AB35,5)),(HLOOKUP((T1),$P2:$AB35,5)))


          (the 5 refers to the 5th row down under that year, the next time is 6 so is the sixth row down etc)

          This works perfectly, for those conditions with 5 years of entries, if you only have 1 year it will include the blanks and give you the average of year 1 + 4 years of zero, which works kinda but not ideal as it gives me a 5th of one year rather than the average of one year which is the same as one year.

          I want to try and ignore any years with blanks, this works perfectly,

          Code:
          =AVERAGEIF(P2:AB2,"<>")


          But won't limit myself to the last 5 years when it has enough data point.

          I can't seen to work out how to nest the two, is it even possible?
          Without looking at the whole thing, can you not make the first expression the subject of the last one, i.e. replace the range P2:AB2 with that expression and count back all the brackets?

          I had loads of fun making such complex interlaced calculations work. Sometimes you have to go one step further and use an array formula so it can keep track of itself. That may well be the case here.
          Blog? What blog...?

          Comment

          Working...
          X