• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Return a value from a range of values

D

dnessim

Member
This question seems to come up quite a bit on the forums, but I cant seem to get my specicic scenario addressed. Col M contains the values I want to group, Col N is the group they fall into , see example here:


value (col M) Range (col N)

0.439 0.7

0.619 0.7

0.619 0.7

1.929 2.1

1.929 2.1

2.482 2.5

1.001 1.1

0.560 0.7

0.818 0.9

0.818 0.9

0.818 0.9

1.162 1.3


my ranges are as follows,

0 to .7 = .7

>.7 to <=.9 = .9

>.9 to <=1.1 = 1.1

>1.1 to <=1.3 = 1.3

so on and so on increasing by .2 each time.


Now is there a better way to do this then a big ugly repeating IF statement?

Like this:

=IF(M2<0.7,0.7,IF(M2<=0.9,0.9,IF(M2<=1.1,1.1,IF(M2<=1.3,1.3,IF(M2<=1.5,1.5,IF(M2<=1.7,1.7,IF(M2<=1.9,1.9,IF(M2<=2.1,2.1,IF(M2<=2.3,2.3,IF(M2<=2.5,2.5,">2.5"))))))))))


I would also like to keep the range in a list on my spreadsheet so I can change it if required.

Thanks

Dave
 
Hi Dnessim!


Welcome to the Forum..


In Right-Top of the forum, in Google Box, you can search any topic, and it will search for you..


About your question,


You can set a Area for your data, and then you can put the below formula in B2,

Code: 
=LOOKUP(A1,$D$2:$F$5)


For detail, please have a look the below picture..

https://dl.dropbox.com/u/78831150/Excel/Return%20a%20value%20from%20a%20range%20of%20values%20%28dnessim%29.jpg


Regards,

Deb
 
Hi

Excellent Deb!


Or for this simple exemple

Code: 
=SI(M2<=0.7;0.7;SI(M2>2.5;2.5;0.9+ENT(5*(M2-0.7))/5))


Sorry, wrote in french

=IF(M2<=0.7,0.7,IF(M2>2.5,2.5,0.9+INT(5*(M2-0.7))/5))
 
Hi Mercatog,


Great.. impressive...


@ dnessim & other


Mercatog's Formula in English

Code: 
=IF(M2<=0.7,0.7,IF(M2>2.5,2.5,0.9+INT(5*(M2-0.7))/5))


Regards,

Deb.


PS:

As you can see, most of the reader of this blog, are like to read in ENGLISH, but that doesn't stop someone to share his knowledge. One of our Guide (we called them NINJA.. ) has make an awesome sheet to translate any Excel Formula from any language to Any (in our case English) will help us to receive your knowledge..

Little bit hard for you.. but the interest of sharing.. will give you and us a lots of Peace..

Please download the sheet from http://bit.ly/OyEQnU and GoTo > 4th Sheet / Hoja..

Type your formula and convert to English..


For detail.. go to below link.

http://chandoo.org/forums/topic/excel-multilanguage-formula-translator-and-function-reference
 
Hi Roy

Thanks for the file, very intersting and sorry again for missing translate formula to English (I have edited my post and correct it)

I work in french version of excel and when I want to write formulas here, I use in vba Editor (after selecting the cell with fomrula)
Code: 
?ActveCell.Formula


Sorry again
 
Hi, dnessim!


Another approach:

=MAX(REDOND.MULT(M2;0,2)+0,1;0,7) -----> in english: =MAX(MROUND(M2,0.2)+0.1,0.7)


Regards!
 
Hi SirJB7

Congratulations about your formula

If will maximum at 2.5, your formula will
Code: 
=MIN(MAX(MROUND(M2,0.2)+0.1,0.7),2.5)


Note that formula fails if negative numbers, so to solve this problem [code]=MAX(MROUND(M2,0.2*SIGN(M2))+0.1,0.7)


In general if the minima is So (here 0.7) and the maxima Sf (here 2.5) and the step Pas (here 0.2), Another formula: =MIN(MAX(Pas+So+INT((M2-So)/Pas)*Pas,So),Sf)[/code]


Thanks SirJB7 for MAX and Min in place of IF


Regards
 
@mercatog


Hi!


Thank you for your kind comments, and you're right about the correction for an upper limit of 2.5, ... but I didn't see it in the original question except in the nested formula, that I assumed that it was just cropped for writing a hundred of chars less :)


Besides I didn't care about negative numbers as for the ranges described which started at 0 (zero), ending at 1.3 and making me assume that there was no top.


Regards!


PS: Au fait, chapeau, monsieur!... et pas seulement pour votre formule généralisée mais aussi pour les autre commentaires que j'ai lu.

PS2: (in English for the whole community) BTW, hats off, sir!... and not only because or the general formula but for the other comments I've read too.

PS3: Sorry about failing in Russian, ... for the time being :)
 
Hi SirJB7

I'll begin from my PS3: Спасибо за поощрение

PS2: Thanks for encouragement


Right,ranges described at 0


Regards
 
Everyone - thanks so much for posting all these solutions, I have alot of new functions to learn. I have only implemented a few of these.


@Deb - thank you, at first look, i didn't understand how this could possibly work, but now I think i understand . The only change I made was I increased the "from" values by .001, for example .7001 so that if the input value M2 was .7 the result range should be .7, is there anyway to make the grid "from value" "greater than or equal to .7" using .7001 will work fine, I just wanted to know for learning purposes.


@mercatog - your last formula works great too, I just dont understand why, so i will study.


@SirJB7 - my values are never negative and the max is higher than 2.5 but i know I didnt specify, this was just an example. thanks for your input!!
 
@mercatog,


I get 1.1 which is correct result. So I'd like to understand what results did you get?

The formula evaluation shows following steps:


1. IF Condition evaluates to FALSE,


2. CEILING(1.03,0.1) = 1.1


3. MOD(FLOOR(M2,0.1),0.2)) evaluates as follows:

=MOD(1,0.2) and it then evaluates to 0.


So if you are getting different results please post back.
 
Hi shrivallabha

Here my file where I did tests http://speedy.sh/Z75A8/Shrivallabha.xlsx


And the image of the sheet http://speedy.sh/7ChXP/Shriva.jpg


MOD(FLOOR(1.03,0.1),0.2)=0.2 in my excel :)

FLOOR(1.03,0.1)=1

MOD(1,0.2)=0.2


Regards
 
You are using :

=MIN(MAX(CEILING(M3,0.1)+MOD(FLOOR(M3,0.1),0.2),0.7),2.5)

against my name which isn't the formula I have posted. And it works for 1.03 test no problem.


My posted formula still is:

=IF(M2<=0.7,0.7,CEILING(M2,0.1)+MOD(FLOOR(M2,0.1),0.2))

However, my formula fails miserably at multiple of 0.1 intervals like 0.8, 0.9, 1.0, 1.1, 1.2 etc.


Thanks a lot for pointing that out.
 
Hi, dnessim!

Glad you solved it. Credit to all who posted their suggestions.

Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top