Custom Rounding And IF Statement In One Formula....

[HobbesIsReal]

So here is what happens in the first half of the equation....I round numbers to the nearest half based on a specific range of decimals in the original number. For example if the number in A1 is:


28.0 to 28.399 then rounds DOWN to 28

28.4 to 28.799 then rounds to 28.5

28.8 to 28.99 then rounds UP to 29


for this I am using shg's equation:


=int(a1) + lookup(round(mod(a1,1), 6), {0,4,8}/10, {0,5,10}/10)


This is all I needed for most of my numbers.....but one part goes one step further.....

.

.

.

.

NOW THE SECOND HALF......Once the first half of the equation rounds the number above, I need the second half of the equation to then do the following. If the resulting number from the first part of equation above is:


<*> 1 through 3 = its own size (1 is 1, 1.5 is 1.5, 2 is 2, 2.5 is 2.5, 3 is 3)

<*> 3.5 to 7 = 3

<*> 7.5 and above = 4

<*> Resulting number from last part of the formula multiply by 2


So this will end up being only 1, 1.5, 2, 2.5, 3, or 4 multipled by 2.


I am pretty sure this second part would be an IF statement (or maybe there is a more efficient way), but I am still learning the ins and outs of that....but I don't know how to use the first formula AND add the needed second part into all one forumula.

 

[HobbesIsReal]

shg solved this in another forum. Here is the answer.....


=CHOOSE(MATCH(INT(A1) + LOOKUP(ROUND(MOD(A1,1), 6), {0,4,8}/10, {0,5,10}/10), {0,3.5,7.5}), 2 * (INT(A1) + LOOKUP(ROUND(MOD(A1,1), 6), {0,4,8}/10, {0,5,10}/10)), 6, 8)

 

[Smallman]

Hi HobbesIsReal


Thanks for posting your solution. That is rare!! Well done. In future if you do post on another forum provide the link in your original post. It assists the helpers when assessing weather to take the challenge on as it may already be solved. Not everyone is as kind as you to put the solution on the other forum(s).


Take care


Smallman

 

[NARAYANK991]

Hi ,


I have my doubts on what the OP meant by the statement :



So this will end up being only 1, 1.5, 2, 2.5, 3, or 4 multiplied by 2.




Are the possible values 2 , 3 , 4 , 5 , 6 and 8 ?


If this is so , a simple IF statement can do the same job , since any result of the initial rounding beyond 7.5 will result in 8. A rounding up to 7.5 and beyond is possible only if the input number is greater than or equal to 7.4


Similarly , between 2.8 and 7.39999 , the result will always be 6.


Any value below 2.79999 results in the rounded result being multiplied by 2.


Narayan