+ Reply to Thread
Results 1 to 7 of 7

Trend Calculation - Returns symbol

  1. #1
    Registered User
    Join Date
    09-02-2015
    Location
    UK
    MS-Off Ver
    7 Professional
    Posts
    14

    Trend Calculation - Returns symbol

    Hi All
    I have attached example

    What I'm looking to achieve is in Cell C5 I want to see a Green arrow pointing up if the trend for the last 12 months is showing improvement, Yellow sideways for Static performance and Red down arrow is performance is getting worse

    Is there a way to achieve this by only using a formula, as I don't wish to use VBA or Macro's

    If needed I can added additional data to the sheet

    Thanks In advanceTrend Test.JPG

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,704

    Re: Trend Calculation - Returns symbol

    It would help if you attached a sample Excel workbook, as it is difficult to read that picture.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

    Pete

  3. #3
    Registered User
    Join Date
    09-02-2015
    Location
    UK
    MS-Off Ver
    7 Professional
    Posts
    14

    Re: Trend Calculation - Returns symbol

    Sorry couldnt work out how to do that - See attached
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    02-14-2017
    Location
    India
    MS-Off Ver
    2013
    Posts
    128

    Re: Trend Calculation - Returns symbol

    Quite Confusing. How u determine it's improving or getting worse? U mean based on the average?
    Last edited by Manikandan Arumugam; 03-21-2017 at 07:12 AM. Reason: Question
    Manikandan Arumugam
    Excel Learner

  5. #5
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Trend Calculation - Returns symbol

    Put a formula in each cell that will give result
    0 = no change
    positive = improvement
    negative = getting worse

    Then with conditional format

    ConditFormat120.jpg
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  6. #6
    Registered User
    Join Date
    09-02-2015
    Location
    UK
    MS-Off Ver
    7 Professional
    Posts
    14

    Re: Trend Calculation - Returns symbol

    Could you please advise what the calculation should be to go in each cell

    In the attached I've included a graph which shows the trend over the 12 month period I:e Feb 2017 back to March 2016
    The graph represents what I'm trying to achieve however Instead of having a graph I want to just use the arrow in cell C5 to show the trend Up, Down , Sideways

    Hope this helps
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Trend Calculation - Returns symbol

    Open attached workbook and see if this works for you

    I have added an extra row which calculates the difference between the cumulative sales for 12 months to now with 12 months to previous month
    I used your Hlookup formula (modified) to drive the value in cell C6

    In cell P5 and copied across:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Is this the correct measure of improvement?


    EDIT
    I do not know why I added a row - I could have used row 5!!
    Last edited by kev_; 03-21-2017 at 08:19 AM.

+ 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. Make a symbol for the calculation result
    By mandy.hk in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 09-15-2015, 01:49 AM
  2. [SOLVED] Formula to compare last 2 numbers in row for upward, downward trend calculation
    By Zyphon in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-26-2015, 10:10 AM
  3. [SOLVED] Trend Calculation using VBA
    By a_driga in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-24-2014, 09:54 PM
  4. [SOLVED] Calculation // trend Analysis
    By nicci113 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-23-2013, 05:19 PM
  5. Replies: 1
    Last Post: 03-28-2012, 10:33 AM
  6. Need help with trend calculation
    By excelify in forum Excel General
    Replies: 2
    Last Post: 04-15-2010, 08:44 AM
  7. Program for calculation a power trend line.
    By Houston banker in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-26-2005, 05:05 PM

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