Round down last digit to a 3 or 7 whichever comes first

Hi All,

I'm having a round down issue and I keep getting errors.
The following are just examples, the L17 is the cell with a value which needs to be rounded down.

If L17 = 5.94 Then M17 needs to round down to 5.93
If L17 = 5.91 Then M17 needs to round down to 5.87
If L17 = 5.67 Then M17 needs to remain the same as it ends in a 7
If L17 = 5.63 Then M17 needs to remain the same as it ends in a 3

Any help would be greatly appreciated.

Cheers

Or:

=ROUNDDOWN(A1,1)+LOOKUP(INT(MOD(A1*100,10)),{0,3,7},{-0.03,0.03,0.07})

@Phuocam
check it again

@Sandy,
I agree with Phuocam: your values for 5.92 and 5.98 (I think!) should be 5.87 and 5.97.

Why? these values are not defined so I assumed they are the same as source

No ..... because the OP requested data to end in 3 or 7!!!

what about post #3 ?

I think it reasonable to assume the 3/7 rule applies to all, given the values ending in 4 and 6 have been adjusted, so "logic" suggests 2 and 8 should be treated in the same way.

here is: =A1---CHOOSE(IFNA(MATCH(--RIGHT(TEXT(A1,".00"),1),{1,2,3,4,5,6,7,8,9,0},0),10),0.04,0.05,0,0.01,0.02,0.03,0,0.01,0.02,0.03)
but i'd like to know from OP
i am not thinking in en so few errors can be

Thanks for all the responses guys.

The value to be rounded down from a formula perspective is the cell, eg: L17, L18, L19 etc etc

With the formula and end result being in the cell M17, M18, M19 etc etc when copied down.

I put amounts in there to show as stated an example of different numbers and how they would adjust down or if they end with 3 or 7 they remain unchanged.

I hope this clears it up a little.

So do you have a solution?

Originally Posted by JohnTopley

So do you have a solution?

I wish I did

What is wrong with those provided?

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Originally Posted by JohnTopley

What is wrong with those provided?

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Thanks John, I will mock something up if I still can't get this damn thing to work. I have to recheck a couple.

Originally Posted by Phuocam

Or:

=ROUNDDOWN(A1,1)+LOOKUP(INT(MOD(A1*100,10)),{0,3,7},{-0.03,0.03,0.07})

Thanks Phuocam, this is certainly the cleanest way to implement and is getting the desired result except on a couple of cells where the number ending in 7 which should stay on 7 rounds down to a 3. Otherwise the others are working well.

I tried Phoucam's formula and ending 7 works OK.

I suspect the issue is the last digit is less than 7 but formatted with 2 digit appears as 7.

For example: 5.969 (which will appear as 5.97 in cells formatted as number with 2 decimal places) results in value of 5.93 whereas 5.970 results in value of 5.97

Ahhhaaa, John, I suspect you could be onto something here mate.

I will double check all the formatting to make sure, that's something I didn't consider.

Cheers for that.

you can try =A1---CHOOSE(IFNA(MATCH(--RIGHT(TEXT(ROUND(A1,2),".00"),1),{1,2,3,4,5,6,7,8,9,0},0),10),0.04,0.05,0,0.01,0.02,0.03,0,0.01,0.02,0.03)
or adapt ROUND to Phuocam solution

Originally Posted by daddylonglegs

This formula should round the actual value as required


=MAX(FLOOR(ROUND(A1,2)+{0.03,0.07},0.1)-{0.03,0.07})

Outstanding daddylonglegs, you rock, that was the final bomb right there, John was spot on earlier as the total of the column which was used to then do the rounding down was created from a formula which multiplied another total by 0.995, as a result even though I could not see it straight away there would have been 3 decimal places in the result hence creating the formula anomaly.

Now daddylonglegs has come along and made it incredibly lean and restricts it to 2 decimal digits, this is a feature I will use for other things now I reckon as it's so easy to mess up.

Serious thanks to sandy666, your effort didn't go unnoticed I was just looking for something really lean and clean and optimised, again thank you to Phuocam & John for all your help and advice, really awesome!

I wish I had a big SOLVED sticker I could plaster across this thread for others to see like that do with real estate, that would be cool for future peeps searching ..

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Thanks John, that 'solution' menu is exactly what I needed, appreciate it.