How do you get a specific value for a numbers between two values (0-99 = 0)

**Gotrockseh** · 07-11-2017, 10:00 PM

I cannot seem to figure out how to get excel to perform this task.

If an set value falls between 0 to 99 it must = 0 in the adjacent cell, example 88 = 0 and so forth.

0 to 99 0
100 to 199 -1
200 to 299 -1
300 to 399 -1
400 to 499 -1
500 to 599 -2
600 to 699 -2
700 to 799 -2
800 to 899 -3
900 to 999 -3

Any suggestions. Thanks.

**leelnich** · 07-11-2017, 10:23 PM

See Post #4
Here's 1 way (each piece yields 1 if TRUE, 0 if FALSE):
=(A2>99)+(A2>499)+(A2>799)

Here's another (MATCH finds the position of the largest element <=A2 in a list; INDEX returns a corresponding element from a 2nd list):
=INDEX({0,1,2,3},MATCH(A2,{0,100,500,700}))

Or just:
=MATCH(A4,{0,100,500,700})-1

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

**Gotrockseh** · 07-11-2017, 10:33 PM

That will give me a False or True correct?

I am trying to get a number associated to the range between to values.
For example.
0-99 = 0 in adjacent cell. So if I was to enter 88 in A1, A2 would give a number of 0
100 - 499 must = -1 in the adjacent cell. So if I was to enter 255 in A1 it would give a -1 in A2

thanks.

**leelnich** · 07-11-2017, 10:42 PM

Sorry, thought it was [1,2,3], not [-1,-2,-3]

Here's 1 way (each piece yields 1 if TRUE, 0 if FALSE):
=-(A2>99)-(A2>499)-(A2>799)

Here's another (MATCH finds the position of the largest element <=A2 in a sorted list; INDEX returns the corresponding element from a 2nd list):
=INDEX({0,-1,-2,-3},MATCH(A2,{0,100,500,700}))

Or just:
=1-MATCH(A4,{0,100,500,700})

**Gotrockseh** · 07-11-2017, 11:13 PM

Sweet. That works thanks.

**leelnich** · 07-12-2017, 01:54 AM

You're welcome, happy to help! If complete, please go to Thread Tools up top and mark your thread as SOLVED. Thanks!-Lee

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

How do you get a specific value for a numbers between two values (0-99 = 0)

LinkBack

Thread Tools

Rate This Thread

Display

How do you get a specific value for a numbers between two values (0-99 = 0)

Re: How do you get a specific value for a numbers between two values (0-99 = 0)

Re: How do you get a specific value for a numbers between two values (0-99 = 0)

Re: How do you get a specific value for a numbers between two values (0-99 = 0)

Re: How do you get a specific value for a numbers between two values (0-99 = 0)

Re: How do you get a specific value for a numbers between two values (0-99 = 0)

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Function possible for Using specific number to count other specific numbers total ?

[SOLVED] How to delete a row with specific values in a that fall between certain numbers

Generating 40 rows of 17 Random numbers (1 to 5) that will sum to specific values (60,..)

Getting an average between a range of two numbers as indicated by specific cell values

Assigning specific values to strings and/or numbers

Specific cells populate with specific numbers when a value within a range is entered.

Summing Numbers With Specific Values To The Left Of Them

Bookmarks

Bookmarks

Posting Permissions