Formula or VBA to "round" numbers for sales prices + issues with number/text/...

[_SGX_]

Dear Forum,

It's been a while since I had to use the Excel wizards of this forum, but I can't wrap my head around an easy to use formula or VBA to "round" sales prices of a starting company that is obligated by (belgian) law to change the prices to specific decimals/roundings, because 1ct, 2ct,... isn't allowed to have people pay like that anymore, minimum is a 5ct.

Here's the list:
,00 ,01 ,02 --> ,00
,03 ,04 --> increase to ,05
,06 ,07 --> decrease to ,05
,08 ,09 --> increase to ,10

Examples:
3,99 = 4,00
3,32 = 3,30
3 = 3,00 = OK
3,2 = 3,20 = OK

My solution (not yet finished even) has multiple steps, multiple formulas, multiple IFs, trying to extract the last digit to see what needs to be done but I cannot get the decimals to always stay right, I had trouble getting my lookups to use digits instead of numbers as text,... Hopefully I'm missing some awesome formula to get me going in the right direction.

Some of the steps:
1. Make prices TEXT, so other manipulations can be done more easily
2. Editing the numbers that are already without 1 or 2 decimals (pretty much manually or with "if(len(A1)=3;A1&"0"): 3 --> 3,00 3,2 --> 3,20
--> But I think it would work better to try and find the comma, and then work from the number to the right of it (decimal digits)
3. Extracting last "number" using Value() of Right(A1;1)
4. IF formula to check if it's ending in 0,1,2,3 --> Becomes 0
5. IF Formula to check if it's ending in 4,5,6,7--> Becomes 5
6. IF Formula to check if it's ending in 8,9 --> Becomes 10
7. Adding the new ending digits to the starting numbers, seems to function except for no decimal numbers in start... somehow my formula makes 30 out of starting 3.
--> Using multiple IF statements to calculate what needs to be done. Example (but it's in Dutch Excel- =ALS(G3=9;D3+0,01;ALS(G3=8;D3+0,02;ALS(G3=0;D3;LINKS(D3;LENGTE(D3)-1))&G3))

Example Mini-sheet (this is new to me, seems awesome for assistance!)

Prijswijziging_Berdy_01.01.22.xlsx
	A	B	C	D	E	F	G	H
1	Purchase Price	Extra column for calculating prices	Sales Price	Extract last number	,00-,02 --> ,00	,03-,07 --> ,05	,08-,09 --> ,10 + Adding up the numbers to original
2
3	7,10	11,45	11,95	5	5	5	11,95
4	6,35	10,24	10,74	4	4	5	10,75
5	19,52	31,50	32	0	0	0	320
6	8,46	13,65	14,15	5	5	5	14,15
7	30,07	48,51	49,01	1	0	0	49,010
8	10,77	17,38	17,88	8	8	8	17,90
9	6,64	10,71	11,21	1	0	0	11,210
10	0,00	0,00	0,5	0	0	0	0,50
11	8,78	14,17	14,67	7	7	5	14,65
12	24,41	39,37	39,87	7	7	5	39,85
13	96,98	156,45	156,95	5	5	5	156,95
14	6,51	10,50	11	0	0	0	110
15	22,13	35,70	36,20	0	0	0	36,200
16	107,06	172,73	173,23	3	3	5	173,25
17	7,32	11,81	12,31	1	0	0	12,310
18	27,89	44,99	45,49	9	9	9	45,50
19	5,39	8,70	9,20	0	0	0	9,200
20	12,33	19,89	20,39	9	9	9	20,40
21	7,29	11,76	12,26	6	6	5	12,25
22	26,00	41,95	42,45	5	5	5	42,45
23	7,16	11,54	12,04	4	4	5	12,05
24	24,47	39,48	39,98	8	8	8	40,00
25	7,16	11,54	12,04	4	4	5	12,05
26	24,47	39,48	39,98	8	8	8	40,00
27	8,24	13,29	13,79	9	9	9	13,80
28	31,86	51,40	51,90	0	0	0	51,900
29	7,16	11,56	12,06	6	6	5	12,05
30	28,31	45,68	46,18	8	8	8	46,20
31	7,61	12,28	12,78	8	8	8	12,80
32	9,92	16,01	16,51	1	0	0	16,510
33	31,73	51,20	51,70	0	0	0	51,700
34	7,91	12,75	13,25	5	5	5	13,25
35	26,00	41,95	42,45	5	5	5	42,45
36	7,97	12,86	13,36	6	6	5	13,35
37	25,06	40,43	40,93	3	3	5	40,95
38	3,94	6,35	6,85	5	5	5	6,85
39	4,90	7,90	8,40	0	0	0	8,400
40	8,07	13,02	13,52	2	0	0	13,520
41	3,87	6,24	6,74	4	4	5	6,75
42	7,13	11,50	12	0	0	0	120
43	4,85	7,82	8,32	2	0	0	8,320
44	21,80	35,17	35,67	7	7	5	35,65
45	4,43	7,14	7,64	4	4	5	7,65
46	6,81	10,99	11,49	9	9	9	11,50
47	3,91	6,30	6,80	0	0	0	6,800
48	5,96	9,61	10,11	1	0	0	10,110
49	17,90	28,87	29,37	7	7	5	29,35
50	4,40	7,10	7,60	0	0	0	7,600
51	4,49	7,25	7,75	5	5	5	7,75
52	12,37	19,95	20,45	5	5	5	20,45
53	9,73	15,69	16,19	9	9	9	16,20
54	11,69	18,85	19,35	5	5	5	19,35
55	29,94	48,30	48,80	0	0	0	48,800
56	209,57	338,10	338,60	0	0	0	338,600
57	15,20	24,00	24,50	0	0	0	24,500
Blad1

Cell Formulas
Range	Formula
D3:D57	D3	=IF(LEN(C3)<=3,VALUE("0"),VALUE(RIGHT(C3,1)))
E3:E57	E3	=IF(OR(D3=0,D3=1,D3=2),VALUE("0"),D3)
F3:F57	F3	=IF(OR(E3=3,E3=4,E3=5,E3=6,E3=7),VALUE("5"),E3)
G3:G57	G3	=IF(F3=9,C3+0.01,IF(F3=8,C3+0.02,IF(F3=0,C3,LEFT(C3,LEN(C3)-1))&F3))
A3:A56	A3	=I3*0.75

 

[gordsky]

I think you have a bracket in the wrong place n your "G" column formula.
If you remove a bracket after the "-1" and place it at the end of the formula ( so it looks like below) I think it should do what you are after

=IF(F3=9,C3+0.01,IF(F3=8,C3+0.02,IF(F3=0,C3,LEFT(C3,LEN(C3)-1)&F3)))

 

[_SGX_]

Hey Gordsky, thank you for replying! It certainly helped! The only issue seems to remain with the ,01!

Prijswijziging_Berdy_01.01.22.xlsx
	A	B	C	D	E	F	G	H
1	Purchase Price	Extra column for calculating prices	Sales Price	Extract last number	,00-,02 --> ,00	,03-,07 --> ,05	,08-,09 --> ,10 + Adding up the numbers to original
2
3	7,10	11,45	11,95	5	5	5	11,95
4	6,35	10,24	10,74	4	4	5	10,75
5	19,52	31,50	32	0	0	0	32,00
6	8,46	13,65	14,15	5	5	5	14,15
7	30,07	48,51	49,01	1	0	0	49,01
8	10,77	17,38	17,88	8	8	8	17,90
9	6,64	10,71	11,21	1	0	0	11,21
10	0,00	0,00	0,5	0	0	0	0,50
11	8,78	14,17	14,67	7	7	5	14,65
12	24,41	39,37	39,87	7	7	5	39,85
13	96,98	156,45	156,95	5	5	5	156,95
14	6,51	10,50	11	0	0	0	11,00
15	22,13	35,70	36,20	0	0	0	36,20
16	107,06	172,73	173,23	3	3	5	173,25
17	7,32	11,81	12,31	1	0	0	12,31
18	27,89	44,99	45,49	9	9	9	45,50
19	5,39	8,70	9,20	0	0	0	9,20
20	12,33	19,89	20,39	9	9	9	20,40
21	7,29	11,76	12,26	6	6	5	12,25
22	26,00	41,95	42,45	5	5	5	42,45
23	7,16	11,54	12,04	4	4	5	12,05
24	24,47	39,48	39,98	8	8	8	40,00
25	7,16	11,54	12,04	4	4	5	12,05
26	24,47	39,48	39,98	8	8	8	40,00
27	8,24	13,29	13,79	9	9	9	13,80
28	31,86	51,40	51,90	0	0	0	51,90
29	7,16	11,56	12,06	6	6	5	12,05
30	28,31	45,68	46,18	8	8	8	46,20
31	7,61	12,28	12,78	8	8	8	12,80
32	9,92	16,01	16,51	1	0	0	16,51
33	31,73	51,20	51,70	0	0	0	51,70
34	7,91	12,75	13,25	5	5	5	13,25
35	26,00	41,95	42,45	5	5	5	42,45
36	7,97	12,86	13,36	6	6	5	13,35
37	25,06	40,43	40,93	3	3	5	40,95
38	3,94	6,35	6,85	5	5	5	6,85
39	4,90	7,90	8,40	0	0	0	8,40
40	8,07	13,02	13,52	2	0	0	13,52
41	3,87	6,24	6,74	4	4	5	6,75
42	7,13	11,50	12	0	0	0	12,00
43	4,85	7,82	8,32	2	0	0	8,32
44	21,80	35,17	35,67	7	7	5	35,65
45	4,43	7,14	7,64	4	4	5	7,65
46	6,81	10,99	11,49	9	9	9	11,50
47	3,91	6,30	6,80	0	0	0	6,80
48	5,96	9,61	10,11	1	0	0	10,11
49	17,90	28,87	29,37	7	7	5	29,35
50	4,40	7,10	7,60	0	0	0	7,60
51	4,49	7,25	7,75	5	5	5	7,75
52	12,37	19,95	20,45	5	5	5	20,45
53	9,73	15,69	16,19	9	9	9	16,20
54	11,69	18,85	19,35	5	5	5	19,35
55	29,94	48,30	48,80	0	0	0	48,80
56	209,57	338,10	338,60	0	0	0	338,60
57	15,20	24,00	24,50	0	0	0	24,50
Blad1

Cell Formulas
Range	Formula
D3:D57	D3	=IF(LEN(C3)<=3,VALUE("0"),VALUE(RIGHT(C3,1)))
E3:E57	E3	=IF(OR(D3=0,D3=1,D3=2),VALUE("0"),D3)
F3:F57	F3	=IF(OR(E3=3,E3=4,E3=5,E3=6,E3=7),VALUE("5"),E3)
G3:G57	G3	=IF(F3=9,C3+0.01,IF(F3=8,C3+0.02,IF(F3=0,C3,LEFT(C3,LEN(C3)-1)&F3)))
A3:A56	A3	=I3*0.75

 

[gordsky]

can you point me to a row or cell where the error is occuring and state what it should be

 

[_SGX_]

can you point me to a row or cell where the error is occuring and state what it should be

Row 7: 49,01 --> 49,00
Row 9: 11,91 --> 11,90
Row 40: 13,52 --> 13,50

 

[gordsky]

Having studied your code it is going to fail in a number of places.
Col D formula is counting the number of char in the cell (you have said LEN) so is calculating on that. Using the right function will ultimately fail because it wont recognise 32.00 as 32.0 but more as 32.

secondly your formula in D says <=3 but your initial text say is should be for <3 and 3 & 4 do something else

lastly when i copied your book to an excel file it would seem that column c is a mix of text and numbers..

Do you actually need all those helper columns also?? If column c was purely numerical you could do it in a single formula

 

[gordsky]

the below single code works fine on a test sheet. If you do want the "helper" columns then simply break the formula down at each "if" statement and paste it into the relevant column.

=IF(OR(VALUE(RIGHT(ROUND((C3-(TRUNC(C3,1))),2),1))=0,VALUE(RIGHT(ROUND((C3-(TRUNC(C3,1))),2),1))=5),C3,IF(AND(VALUE(RIGHT(ROUND((C3-(TRUNC(C3,1))),2),1))>0,VALUE(RIGHT(ROUND((C3-(TRUNC(C3,1))),2),1))<3),C3--TRUNC(C3,1),IF(AND(VALUE(RIGHT(ROUND((C3-(TRUNC(C3,1))),2),1))>=3,VALUE(RIGHT(ROUND((C3-(TRUNC(C3,1))),2),1))<=7),C3+(0.05-(C3-TRUNC(C3,1))),ROUND(C3,1))))

In summary the above does the following.
If 2nd dp is 0 or 5 then it doesn't change
if 2nd dp is 1 to 3 then it becomes 0
if 2nd dp is 4 to 7 it becomes 5
if 2nd dp is 8 or 9 it will round up to 1st dp

 

[_SGX_]

the below single code works fine on a test sheet. If you do want the "helper" columns then simply break the formula down at each "if" statement and paste it into the relevant column.

=IF(OR(VALUE(RIGHT(ROUND((C3-(TRUNC(C3,1))),2),1))=0,VALUE(RIGHT(ROUND((C3-(TRUNC(C3,1))),2),1))=5),C3,IF(AND(VALUE(RIGHT(ROUND((C3-(TRUNC(C3,1))),2),1))>0,VALUE(RIGHT(ROUND((C3-(TRUNC(C3,1))),2),1))<3),C3--TRUNC(C3,1),IF(AND(VALUE(RIGHT(ROUND((C3-(TRUNC(C3,1))),2),1))>=3,VALUE(RIGHT(ROUND((C3-(TRUNC(C3,1))),2),1))<=7),C3+(0.05-(C3-TRUNC(C3,1))),ROUND(C3,1))))

In summary the above does the following.
If 2nd dp is 0 or 5 then it doesn't change
if 2nd dp is 1 to 3 then it becomes 0
if 2nd dp is 4 to 7 it becomes 5
if 2nd dp is 8 or 9 it will round up to 1st dp

Looks awesome. Need to try and use it in the file... Because it's english formulas :/ Could I use the XL2BB addon for that maybe?

Yes of course I don't need the helper columns, they were for me to figure out how to get it to work hehe. I was assuming that in a final formula it would need to check if there is a comma and then work from there (just thinking out loud)

 

[_SGX_]

the below single code works fine on a test sheet. If you do want the "helper" columns then simply break the formula down at each "if" statement and paste it into the relevant column.

=IF(OR(VALUE(RIGHT(ROUND((C3-(TRUNC(C3,1))),2),1))=0,VALUE(RIGHT(ROUND((C3-(TRUNC(C3,1))),2),1))=5),C3,IF(AND(VALUE(RIGHT(ROUND((C3-(TRUNC(C3,1))),2),1))>0,VALUE(RIGHT(ROUND((C3-(TRUNC(C3,1))),2),1))<3),C3--TRUNC(C3,1),IF(AND(VALUE(RIGHT(ROUND((C3-(TRUNC(C3,1))),2),1))>=3,VALUE(RIGHT(ROUND((C3-(TRUNC(C3,1))),2),1))<=7),C3+(0.05-(C3-TRUNC(C3,1))),ROUND(C3,1))))

In summary the above does the following.
If 2nd dp is 0 or 5 then it doesn't change
if 2nd dp is 1 to 3 then it becomes 0
if 2nd dp is 4 to 7 it becomes 5
if 2nd dp is 8 or 9 it will round up to 1st dp

I used an online translator for the formulas and so far so good, except where there ends in ,01 it seems, somehow the formula DOUBLES the total amount?

Prijswijziging_Berdy_01.01.22.xlsx
	A	B	C	D
1	Purchase Price	Extra column for calculating prices	Sales Price	New Formula
2
3	7,10	11,45	11,95	11,95
4	6,35	10,24	10,74	10,75
5	19,52	31,50	32	32
6	8,46	13,65	14,15	14,15
7	30,07	48,51	49,01	98,01
8	10,77	17,38	17,88	17,9
9	6,64	10,71	11,21	22,41
10	0,00	0,00	0,5	0,5
11	8,78	14,17	14,67	14,65
12	24,41	39,37	39,87	39,85
13	96,98	156,45	156,95	156,95
14	6,51	10,50	11	11
15	22,13	35,70	36,20	36,20
16	107,06	172,73	173,23	173,25
17	7,32	11,81	12,31	24,61
18	27,89	44,99	45,49	45,5
19	5,39	8,70	9,20	9,20
20	12,33	19,89	20,39	20,4
Blad1

Cell Formulas
Range	Formula
A3:A20	A3	=J3*0.75
D3:D20	D3	=IF(OR(VALUE(RIGHT(ROUND((C3-(TRUNC(C3,1))),2),1))=0,VALUE(RIGHT(ROUND((C3-(TRUNC(C3,1))),2),1))=5),C3,IF(AND(VALUE(RIGHT(ROUND((C3-(TRUNC(C3,1))),2),1))>0,VALUE(RIGHT(ROUND((C3-(TRUNC(C3,1))),2),1))<3),C3--TRUNC(C3,1),IF(AND(VALUE(RIGHT(ROUND((C3-(TRUNC(C3,1))),2),1))>=3,VALUE(RIGHT(ROUND((C3-(TRUNC(C3,1))),2),1))<=7),C3+(0.05-(C3-TRUNC(C3,1))),ROUND(C3,1))))

 

[gordsky]

=IF(OR(VALUE(RIGHT(ROUND((C3-(TRUNC(C3,1))),2),1))=0,VALUE(RIGHT(ROUND((C3-(TRUNC(C3,1))),2),1))=5),C3,IF(AND(VALUE(RIGHT(ROUND((C3-(TRUNC(C3,1))),2),1))>0,VALUE(RIGHT(ROUND((C3-(TRUNC(C3,1))),2),1))<3),C3--TRUNC(C3,1),IF(AND(VALUE(RIGHT(ROUND((C3-(TRUNC(C3,1))),2),1))>=3,VALUE(RIGHT(ROUND((C3-(TRUNC(C3,1))),2),1))<=7),C3+(0.05-(C3-TRUNC(C3,1))),ROUND(C3,1))))

the issue is the blue bit above change it to

C3-(c3-trunc(c3,1))