+ Reply to Thread
Results 1 to 18 of 18

Capping difference of two figures at -40 but not effecting others

  1. #1
    Registered User
    Join Date
    02-10-2022
    Location
    Ireland
    MS-Off Ver
    MS 365 Subscription on Windows
    Posts
    22

    Capping difference of two figures at -40 but not effecting others

    Hi there, i'm confused at how to cap the difference between two numbers at -40 while not effecting any figures that are positive or between -39 and 0. Any help here would be appreciated, see example attached. Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,388

    Re: Capping difference of two figures at -40 but not effecting others

    Try this:

    =MAX(A2-B2,-40)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,131

    Re: Capping difference of two figures at -40 but not effecting others

    Try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by TMS; 02-17-2022 at 06:45 AM.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,388

    Re: Capping difference of two figures at -40 but not effecting others

    You don't need the IF clause, Trevor.

    To cap between -40 and +40, this will work:

    =MIN(MAX(A2-B2,-40),40)

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    24,295

    Re: Capping difference of two figures at -40 but not effecting others

    Try

    =MAX(MIN(ABS($C2),40),,0)*SIGN(C2)
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,131

    Re: Capping difference of two figures at -40 but not effecting others

    You are correct over engineered. Belt and unnecessary braces

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,388

    Re: Capping difference of two figures at -40 but not effecting others

    LOL! Been there more than I care to remember ...

  8. #8
    Registered User
    Join Date
    02-10-2022
    Location
    Ireland
    MS-Off Ver
    MS 365 Subscription on Windows
    Posts
    22

    Re: Capping difference of two figures at -40 but not effecting others

    Thanks for the reply's, what i meant was a formula that would change the new price to a figure that is max -40 difference and not effect any figures that are positive or between -39 and 0.

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,131

    Re: Capping difference of two figures at -40 but not effecting others

    And that's what Ali's formula and mine do. Did you try them?

    @John: your formula caps positive values to +40.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,388

    Re: Capping difference of two figures at -40 but not effecting others

    That is precisely what this does:

    =MAX(A2-B2,-40)

    Did you try it???

    AliGW on MS365 Insider (Windows) 64 bit

    A
    B
    C
    1
    Old price
    New Price
    Difference
    2
    100
    160
    -40
    3
    110
    150
    -40
    4
    120
    200
    -40
    5
    140
    320
    -40
    6
    150
    400
    -40
    7
    160
    100
    60
    8
    170
    150
    20
    9
    180
    200
    -20
    10
    190
    250
    -40
    11
    200
    200
    0
    Sheet: Sheet1

  11. #11
    Registered User
    Join Date
    02-10-2022
    Location
    Ireland
    MS-Off Ver
    MS 365 Subscription on Windows
    Posts
    22

    Re: Capping difference of two figures at -40 but not effecting others

    Hi @AliGW and @TMS I did try both formulas. They don't give a new price figure. 100-160 is still -60, the formula i'm looking for will give back a new price figure capped at -40 from the old price in this case 140. Sorry about the confusion and thanks for the help.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,388

    Re: Capping difference of two figures at -40 but not effecting others

    You didn't ask for a new price figure!!!

    How are you expecting us to calculate the new figure? How do we know whether the price is to go up or down?

    Utterly confused - your sample workbook does not seem to show what you want at all.

  13. #13
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,131

    Re: Capping difference of two figures at -40 but not effecting others

    Not what you said.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    02-10-2022
    Location
    Ireland
    MS-Off Ver
    MS 365 Subscription on Windows
    Posts
    22

    Re: Capping difference of two figures at -40 but not effecting others

    Thanks @TMS that works.

    and sorry I didnt get my point across fully. Ill improve my queries in the future.

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,388

    Re: Capping difference of two figures at -40 but not effecting others

    Yes, you need to!!! You did not show us the results you wanted.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

  16. #16
    Registered User
    Join Date
    02-10-2022
    Location
    Ireland
    MS-Off Ver
    MS 365 Subscription on Windows
    Posts
    22

    Re: Capping difference of two figures at -40 but not effecting others

    Thanks to everyone who helped! Marked as solved now and reputation given.

  17. #17
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,131

    Re: Capping difference of two figures at -40 but not effecting others

    You're welcome. Thanks for the rep.

  18. #18
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,131

    Re: Capping difference of two figures at -40 but not effecting others

    I have to say, it's an odd way to state the problem and do the calculation.

    This makes more sense to me:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ 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] Calculating difference in values when figures are in the same field
    By Blinky99 in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 05-12-2019, 04:47 AM
  2. Difference between two exact same figures
    By leemarkin in forum Excel General
    Replies: 2
    Last Post: 10-09-2018, 10:33 AM
  3. [SOLVED] Capping a [hh]:mm
    By Beany.uk in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 05-19-2016, 06:12 PM
  4. Difference Between 2 Highest Figures when Filtering
    By jenna_max in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-24-2014, 01:50 AM
  5. [SOLVED] Conditional formatting based on the difference value between figures in two cells
    By Karczoch in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-10-2014, 07:50 AM
  6. Calculating the difference between monthly figures
    By cazz in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-24-2013, 01:59 PM
  7. Conversion of an array of figures inputted into cells as word figures to Excel figures
    By Allan Simpson730 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-08-2013, 07:25 AM

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