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.
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.
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" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Try:Formula:Please Login or Register to view this content.
Last edited by TMS; 02-17-2022 at 06:45 AM.
Trevor Shuttleworth - Retired Excel/VBA Consultant
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
You don't need the IF clause, Trevor.
To cap between -40 and +40, this will work:
=MIN(MAX(A2-B2,-40),40)
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.
You are correct … over engineered. Belt and unnecessary braces
LOL! Been there more than I care to remember ...
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.
And that's what Ali's formula and mine do. Did you try them?
@John: your formula caps positive values to +40.
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
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.
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.
Not what you said.Formula:Please Login or Register to view this content.
Thanks @TMS that works.
and sorry I didnt get my point across fully. Ill improve my queries in the future.
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.
Thanks to everyone who helped! Marked as solved now and reputation given.
You're welcome. Thanks for the rep.
I have to say, it's an odd way to state the problem and do the calculation.
This makes more sense to me:Formula:Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks