# Absolute value with IF statement

#### beancounter

##### Board Regular
I have a forumula as below

=IF((M20-21>0+0)*(M20-21<41+0),M20-21,(M20-21)-40)

It seems to work but if the # in M20 is less than 61 I get a negative number. I don't want the forumula to return negative numbers. How do I change the formula to make negative #'s be zero?????

Thanks,
John

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### dave3009

##### Well-known Member
Code:
=IF(M20<61, 0, IF((M20-21>0+0)*(M20-21<41+0),M20-21,(M20-21)-40))

##### MrExcel MVP
=MAX(0,IF(((M20-21) > 0)*(M20-21 < 41), M20-21, (M20-21)-40))

#### barry houdini

##### MrExcel MVP
Try

=IF(M20<=21,0,M20-IF(M20>=62,61,21))

#### beancounter

##### Board Regular
Dave3009,

I'm trying to determine if a number M20 - 21 units is greater than 0 but less than 40.

If so I need M20 - 21, but if M20 - 21 is greater than 40, I need (M20 - 21) -40 and have that # either be positve or zero.

Hope that makes sense.

John

Replies
4
Views
255
Replies
2
Views
101
Replies
4
Views
352
Replies
2
Views
135
Replies
5
Views
40

1,191,717
Messages
5,988,256
Members
440,145
Latest member
arxoon

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back