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

 

[_SGX_]

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

Superb! This is EXACTLY what I needed. Any good resource that helped you become such an excel wizard? Thanks again Gordsky!!!

 

[gordsky]

Superb! This is EXACTLY what I needed. Any good resource that helped you become such an excel wizard? Thanks again Gordsky!!!

your welcome, I am by no means a wizz and most on here will have far better skills than me. Formulas are about thinking logically which is something I am not bad at. VBA code syntax is where i struggle

 

[RoryA]

Aren't you just rounding to the nearest 0.05 or did I miss something?

=MROUND(C3,0.05)

would seem to work for the examples you quoted in your initial post?

 

[gordsky]

Aren't you just rounding to the nearest 0.05 or did I miss something?

=MROUND(C3,0.05)

would seem to work for the examples you quoted in your initial post?

wouldn't mround fail if c3 were a negative number?? (doesnt the multiplier then have to be negative too?)

 

[RoryA]

I hadn't expected negative sales prices, to be honest, but you could cater for that with:

=MROUND(C3,0.05*SIGN(C3))