Any excel experts? Any excel experts?
Posts 1 to 5 of 5
  1. #1

    TykeLike

    SimonMac's Avatar
    Join Date
    Aug 2010
    Location
    God's Own Republic Of Yorkshire
    Posts
    23,996

    Default 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?
    “Live a good life. If there are gods and they are just, then they will not care how devout you have been, but will welcome you based on the virtues you have lived by. If there are gods, but unjust, then you should not want to worship them. If there are no gods, then you will be gone, but will have lived a noble life that will live on in the memories of your loved ones.”

    ― Marcus Aurelius

  2. #2

    Godlike

    ladymuck's Avatar
    Join Date
    Sep 2005
    Location
    London
    Posts
    9,544

    Default

    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.

  3. #3

    Prof Cunning @ Oxford Uni

    WTFH's Avatar
    Join Date
    Nov 2012
    Posts
    20,712

    Default

    Rather than using Average, could you just do a Sum and then divide by CountIf > 0
    Practice social posting
    Stay Alert
    - Moderate the Posts -Save Face
    Together we can beat the CV offenders

  4. #4

    TykeLike

    SimonMac's Avatar
    Join Date
    Aug 2010
    Location
    God's Own Republic Of Yorkshire
    Posts
    23,996

    Default

    Quote Originally Posted by WTFH View Post
    Rather than using Average, could you just do a Sum and then divide by CountIf > 0
    Quote 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))
    “Live a good life. If there are gods and they are just, then they will not care how devout you have been, but will welcome you based on the virtues you have lived by. If there are gods, but unjust, then you should not want to worship them. If there are no gods, then you will be gone, but will have lived a noble life that will live on in the memories of your loved ones.”

    ― Marcus Aurelius

  5. #5

    Double Godlike!

    malvolio's Avatar
    Join Date
    Jul 2005
    Location
    Walking in the garden, dreaming of Olivia...
    Posts
    11,890

    Default

    Quote 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...?

Posting Permissions

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