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

[HobbesIsReal]

Thanks to shg I got the first half of my challenge resolved in Round to Nearest Half Based On Defined Range of Decimal thread.

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:

Please Login or Register  to view this content.

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.

[Harribone]

There is probably a tidier appoach to this but try:

=IF(A<=3,(A)*2,IF(A<=7,6,8))

Replace A with the formula from shg.If i;ve understood correctly you will only need to multiply by 2 when shg's bit results in the 1 to 3 range. The next two situations will always be 6 and 8.

[shg]

=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)

[HobbesIsReal]

There is probably a tidier appoach to this but try:

=IF(A<=3,(A)*2,IF(A<=7,6,8))

Replace A with the formula from shg.If i;ve understood correctly you will only need to multiply by 2 when shg's bit results in the 1 to 3 range. The next two situations will always be 6 and 8.

Thank you! I think you are REALLY close. but.... the result from shg's rounding formula could result with any number from 1 and above at .5 increments. So the number might be 1.5 or 37.

So we need to get the number from shg's formula and then have it run the following IF statement

• 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

This will result in either 1, 1.5, 2, 2.5, 3, or 4. We would THEN multiply that number by 2.

I think the first part of your IF statement is correct, but the last part is a little off with the 3.5 to 7 = 3 and the 7.5 and above = 4 parts....

[HobbesIsReal]

=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)

SOOOOO Close. These two ranges work PERFECTLY!

• 3.5 to 7 = 3
• 7.5 and above = 4

But the first one "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)" adds an extra .5 to the number if it is whole number and gives the wrong number when if the original number ends in .5 . And I cannot see why...

[Harribone]

My approach does what you require:

If less than or equal to 3 then use shg formula and multiply by 2, else:
if less than or equal to 7 return 3 and multiply by 2 (which will always be 6)
if neither of these is correct the number must be gretaer than 7 therefore return 4 multiplied by 2 (which is always 8)

[shg]

Missing a pair of parens:

=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)

[HobbesIsReal]

Missing a pair of parens:

=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)

THAT WORKED!

Again, you saved my day as I need this spreadsheet for work by Monday morning and I got everything else working perfectly except this part was a little beyond me.

THANK You both for your help!

[shg]

I would do it in two steps rather than put all of that in a single cell.