# IF statements with negative values

#### ypatel1990

##### New Member
Hey everyone,

I currently have a column with a list of percentages of year over year change and am trying to create a column next to it to show different groupings that it would fall under for a pivot table. I keep getting the value false when running my if statements. I have an example of what I am trying to do in the Grouping column below. Not sure if the numbers being percentages is causing problems but I tried to copy and paste the column as values but all of the numbers changed.

 YOY Grouping 0.0% 0 -25.7% -49 to -1 800% 100+ -80.3% -100 to -50 52% 50 to 100

<TBODY>
</TBODY>

### Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

#### ypatel1990

##### New Member

This is the formula I have been trying to get to work. It only includes the negative range so far but it isnt working IF(AM2=0,"0",IF(AM2<-50,AND(AM2>-100,"-50 to -100",IF(AM2<-1,AND(AM2>-49,"-1 to -49","out of range")))))

#### Andrew Poulsom

##### MrExcel MVP

=LOOKUP(A2,{-1,-0.5,0,0.1,0.5,1},{"-50 to -100","-1 to -49","0","1 to 50","50 to 100","100+"})

#### ypatel1990

##### New Member

=LOOKUP(A2,{-1,-0.5,0,0.1,0.5,1},{"-50 to -100","-1 to -49","0","1 to 50","50 to 100","100+"})

This actually works great. How exactly does the formula differentiate between the ranges like 0 and -1 to -49 when everything is just seperated with commas?

#### Andrew Poulsom

##### MrExcel MVP
The curly braces contain array constants. LOOKUP finds the largest item in the first array that is less than or equal to the lookup value and returns the item at the same position in the second array.

#### ypatel1990

##### New Member
The curly braces contain array constants. LOOKUP finds the largest item in the first array that is less than or equal to the lookup value and returns the item at the same position in the second array.

Thats makes much more sense now. This is so much simpler in comparison to a bunch of IF AND statements for all ranges.

Replies
0
Views
355
Replies
5
Views
257
Replies
1
Views
361
Replies
3
Views
896
Replies
0
Views
585

1,196,018
Messages
6,012,892
Members
441,738
Latest member
dataexcel

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