IF function using negative and positive numbers

**Mindyleigh20** · 01-16-2020, 10:14 PM

Is there a way to make this formula also measure the exact same negative numbers?
I know it sounds odd, but at the moment if there is between 5 and 19.99 in cell B2 1 point is assigned.

I need to also assign 1 point if the cell B2 has between negative 5 and negative 19.99.

This is the current formula that works for positive numbers:
=IF(AND(B2>=5,B2<=19.99),1,IF(AND(B2>=20,B2<=39.99),2,0))

**6StringJazzer** · 01-16-2020, 10:16 PM

Use ABS on each occurrence of B2

Formula:

Please Login or Register  to view this content.

**Mindyleigh20** · 01-16-2020, 10:29 PM

That worked perfectly. Thanks!

**6StringJazzer** · 01-17-2020, 10:29 AM

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking on the star icon addreputationiconsmall.jpg below their name.

**green_aardvard** · 01-17-2020, 01:13 PM

Hi MindyLeigh

While "hard coding" the formula is fine, but if the parameters changed you end up having to change the formula across the range.

I have attached a spreadsheet which allows you to have flexible thresholds. If the value is below the threshold it will have the lower value.