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

1. ## 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.

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

Try this:

=MAX(A2-B2,-40)

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

Try:
Formula:
`Please Login or Register  to view this content.`

4. ## 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. ## Re: Capping difference of two figures at -40 but not effecting others

Try

=MAX(MIN(ABS(\$C2),40),,0)*SIGN(C2)

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

You are correct … over engineered. Belt and unnecessary braces

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

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

8. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: Capping difference of two figures at -40 but not effecting others

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

14. ## 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. ## 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. ## 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. ## Re: Capping difference of two figures at -40 but not effecting others

You're welcome. Thanks for the rep.

18. ## 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:
`Please Login or Register  to view this content.`

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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