# Nested Calculation Based on Value Ranges

1. ## Nested Calculation Based on Value Ranges

I have some data I want to sort between different values, I want to have values -5.5 between -5.0 t then to display -4, and lets say 1.5 to 2.0 to display 3, I have 23 different ranges I want to work with, I have tried this where I am linking the data and the cell ref to the display numbers, I just get a bunch of ones and zeros display when I write this.

=IF(AND(D22<\$T\$49),\$U\$49,IF(AND(D22>\$T\$49, D22<=\$T\$48),\$U\$48, IF(AND(D22>\$T\$48,D22<=\$T\$47),U\$47, IF(AND(D22>\$T\$47,D22<=\$T\$46),\$U\$46, IF(AND(D22>\$T\$46,D22<=\$T\$45),\$U\$45,IF(AND(D22>\$T\$45,D22<=\$T\$44),\$U\$44,IF(AND(D22>\$T\$44,D22<=\$T\$43),\$U\$43,IF(AND(D22>\$T\$43,D22<=\$T\$42),\$U\$42,IF(AND(D22>\$T\$42,D22<=\$T\$41),\$U\$41,IF(AND(D22>\$T\$41,D22<=\$T\$40),\$U\$40,IF(AND(D22>\$T\$40,D22<=\$T\$39),\$U\$39,IF(AND(D22>\$T\$39,D22<=\$T\$38),\$U\$38,IF(AND(D22>\$T\$38,D22<=\$T\$37),\$U\$37,IF(AND(D22>\$T\$37,D22<=\$T\$36),\$U\$36,IF(AND(D22>\$T\$36,D22<=\$T\$35),\$U\$35,IF(AND(D22>\$T\$35,D22<=\$T\$34),\$U\$34,IF(AND(D22>\$T\$34,D22<=\$T\$33),\$U\$33,IF(AND(D22>\$T\$33,D22<=\$T\$32),\$U\$32,IF(AND(D22>\$T\$32,D22<=\$T\$31),\$U\$31,IF(AND(D22>\$T\$31,D22<=\$T\$30),\$U\$30,IF(AND(D22>\$T\$30,D22<=\$T\$29),\$U\$29,IF(AND(D22>\$T\$29,D22<=\$T\$28),\$U\$28,IF(AND(D22>\$T\$28,D22<=T47),\$U\$28,IF(AND(D22>\$T\$27),\$U\$27,””))))))))))))))))))))))))

2. ## Re: Neeeeeeeeeeed Help with strings

Without sample I am not sure it match or not, but try:
``Please Login or Register  to view this content.``

3. ## Re: Neeeeeeeeeeed Help with strings

Depending on the relative values, the last value may need to be 1 instead of -1.

4. ## Re: Need help with strings sorting numbers, to produce a value if they are between 2 value

Untitled.jpgattached image should show you what I am tring to do, please if you could help would be very greatful

5. ## Re: Nested Calculation Based on Value Ranges

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As you are new here, I have done it for you this time.)

6. ## Re: Nested Calculation Based on Value Ranges

From my solution in #2, try to slight adjust:

``Please Login or Register  to view this content.``
With D22 is 3.77%

7. ## Re: Nested Calculation Based on Value Ranges

have edited tiltle, sorry I just have no clue about how to explain this, any help would be massively thanked

8. ## Re: Nested Calculation Based on Value Ranges

thank you for your reply, what I want to do as the image above, is rate the values if they are between the values on the right of the table by the values on the left. so if the far right column are lets say 3.77 in will take the rating value of the (display these numbers) so it will return a value of 9 in the Column which says (display numbers go here). Do I need to use java script for this, or can it be done in a long string?

9. ## Re: Nested Calculation Based on Value Ranges

Originally Posted by ARNIEBUN
have edited tiltle, sorry I just have no clue about how to explain this, any help would be massively thanked
I did it for you, actually. When you create a thread title, all you need to ask yourself is, "What am I trying to achieve?" The answer will be your thread title.

10. ## Re: Nested Calculation Based on Value Ranges

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this change is not optional. As you are still new, I will do it for you this time: https://www.excelguru.ca/forums/show...?10676&p=44112)

11. ## Re: Nested Calculation Based on Value Ranges

Try converting all the numbers to percentages. Right now the list looks to be integers vs. percentages. The #2 Solution does work otherwise. Cheers

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