+ Reply to Thread
Results 1 to 4 of 4

Capping Percentage at 100% within Trend Formula

  1. #1
    Registered User
    Join Date
    01-31-2022
    Location
    Edinburgh, Scotland
    MS-Off Ver
    2016
    Posts
    8

    Capping Percentage at 100% within Trend Formula

    I am currently forecasting percentages for future dates for work going up and down, but when I use the Trend formula the percentages either go over 100% or below 0% into a negative.

    I know that there is the Min function to cap it, but I can't quite get this to work the way that I want and I can't really find any other alternatives.

    Example below

    Formula
    =TREND($A$8:C8,$A$7:C7,D7)

    Data
    31/01/2021 01/02/2021 02/02/2021 03/02/2021 04/02/2021 05/02/2021 06/02/2021 07/02/2021
    10% 5% 2% -2.33% -6.33% -10.33% -14.33% -18.33%

    31/01/2021 01/02/2021 02/02/2021 03/02/2021 04/02/2021 05/02/2021 06/02/2021 07/02/2021
    70% 80% 98% 110.67% 124.67% 138.67% 152.67% 166.67%

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

    Re: Capping Percentage at 100% within Trend Formula

    Hi there.

    A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

    Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.
    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

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,858

    Re: Capping Percentage at 100% within Trend Formula

    Perhaps something like =MEDIAN(0,1,TREND(...)) which will always return the middle of the three values, which should mostly be the TREND() result except when it falls below 0 or goes above 1.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    01-31-2022
    Location
    Edinburgh, Scotland
    MS-Off Ver
    2016
    Posts
    8

    Re: Capping Percentage at 100% within Trend Formula

    Glen: First time posting, thanks for letting me know and I will make changes and remember for any future questions

    MrShorty: Thanks, this was exactly what I was looking for.
    Last edited by Scotsy; 01-31-2022 at 10:16 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. [SOLVED] Capping percentage of one cell based on percentage of another
    By jtinsley in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-21-2021, 05:17 AM
  2. Capping the formula
    By MJLG in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-01-2018, 06:48 AM
  3. Setting up a trend percentage based on date
    By Adash in forum Outlook Formatting & Functions
    Replies: 4
    Last Post: 07-23-2014, 05:06 PM
  4. Calculating a trend percentage on a series of #'s
    By Dooldeniya in forum Excel General
    Replies: 3
    Last Post: 10-05-2010, 02:57 PM
  5. capping amount in formula?
    By jivins in forum Excel General
    Replies: 2
    Last Post: 11-25-2008, 02:01 PM
  6. Capping a formula value?
    By Scot B in forum Excel General
    Replies: 2
    Last Post: 12-27-2005, 07:45 PM
  7. Find percentage Trend
    By Gaurav in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-15-2005, 07:07 PM

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