Formula to find % discount, giving as input the Full Price and the Net Price, with rules.

[Amoldavskii]

Hello,

I need help please for an automatic/ semi automatic % finder.
I Need to find which % of discount is a Net price, from a Full price.
We have 5 possible groups:
A. Under 49.99%
B. From 50% to 50+5%
C. from 50+5+1% to 50+5+5% (to 50+10%)
D from 50+10% to 50+10+5% (to 50+15%)
E. from 50+15+1% to 50+15+5 and higher. at 50+15 rule break and can higher will be 50+15+18 or 50+15+26 ect. (NO more 50+20+1!)

Considering the upper rules, I want to ask if it's possible to find from the input of full and net price, to exactly which % it's =?
I attach my actual Excel Formula and some examples, please view it to understand better.

So, a % discount finder considering the logic of the discount groups.

Help file.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1
2
3		FULL	NET	50 + …				FULL	NET	50 + …
4		17,16	3,40	60,4%				17,16	8,19	4,5%
5
6
7
8		FULL	NET	0+ / 50 + …				LISTINO	Netto	0+ / 50 + …
9		10,64	5,85	45,0%	<<I need similar to D9 formula (it's italian, SE = IF)		Can find EVEN SIMILAR SITUATIONS with ,%, ?	10,64	4,94	53,6%	= 50+5+2,2%
10		Editable = my X	Editable = my Y	Result (don't touch)
11
12
13
14
15
16
17		FULL	NET	0+ / 50 + …	….+3° sconto
18		10,64	4,94	5,00%	2,2%
19
20
21		FULL	NET	0+ / 50 + …	….+3° sconto
22		14,68	3,67	50%
23
24
25
26		EXAMPLES	Group 1						Group 2					Group 3						Group 4						Group 5
27		Full price	45%	46%	47%	48%	49%	50%	50+1%	50+2%	50+3%	50+4%	50%+5%	50+5+1%	50+5+2%	50+5+3%	50+5+4%	50+5+5%	50% + 10%	50+10+1%	50+10+2%	50+10+3%	50+10+4%	50+10+5%	50% + 15%	50+15+1%	50+15+2%	50+15+3%	50+15+4%	50+15+5%	50+15+7	50+15+28 ect
28	Product A	10,64	5,85	5,75	5,64	5,53	5,43	5,32	5,27	5,21	5,16	5,11	5,05	5,00	4,95	4,90	4,85	4,80	4,79	4,74	4,69	4,64	4,60	4,55	4,52	4,48	4,43	4,39	4,34	4,30
29	Product B	8,48	4,66	4,58	4,49	4,41	4,32	4,24	4,20	4,16	4,11	4,07	4,03	3,99	3,95	3,91	3,87	3,83	3,82	3,78	3,74	3,71	3,67	3,63	3,60	3,57	3,53	3,50	3,46	3,42
Foglio1

Cell Formulas
Range	Formula
C4,I4	C4	=B4*0.5*(1-D4)
D9,J9	D9	=IF((1-(C9/B9/0.5)<1),(1-(C9/B9)),(1-(C9/B9/0.5)))
C18,C22	C18	=B18*0.5*(1-D18)*(1-E18)
C28:C29	C28	=B28*0.55
D28:D29	D28	=B28*0.54
E28:E29	E28	=B28*0.53
F28:F29	F28	=B28*0.52
G28:G29	G28	=B28*0.51
H28:H29	H28	=B28*0.5
I28:I29,N28:N29,T28:T29,Z28:Z29	I28	=H28*0.99
J28:J29,O28:O29,U28:U29,AA28:AA29	J28	=H28*0.98
K28:K29,P28:P29,V28:V29,AB28:AB29	K28	=H28*0.97
L28:L29,Q28:Q29,W28:W29,AC28:AC29	L28	=H28*0.96
M28:M29,R28:R29,X28:X29,AD28:AD29	M28	=H28*0.95
S28	S28	=H28*0.9
Y28:Y29	Y28	=H28*0.85

 

[maabadi]

Check formula at E4 , E9 , K4 & K9 for grouping. this formula is in E4 for Cell D4

=IF(D4<0.5,D4,IF(D4<=0.55,"50 + "&ROUND((D4-0.5)*100,1)&"%",IF(D4<=0.6,"50 + 5 + "&ROUND((D4-0.55)*100,1)&"%",IF(D4<=0.65,"50 + 10 + "&ROUND((D4-0.6)*100,1)&"%","50 + 15 + "&ROUND((D4-0.65)*100,1)&"%"))))

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3		FULL	NET	50 + …				FULL	NET	50 + …
4		17.16	3.39768	60%	50 + 10 + 0.4%			17.16	8.1939	4.5%	4.5%
5
6
7
8		FULL	NET	0+ / 50 + …				LISTINO	Netto	0+ / 50 + …
9		10.64	5.85	45.0%	45.0%		Can find EVEN SIMILAR SITUATIONS with ,%, ?	10.64	4.94	53.57%	50 + 3.6%
10		Editable = my X	Editable = my Y	Result (don't touch)
11
12
13
14
15
16
17		FULL	NET	0+ / 50 + …	….+3° sconto
18		10.64	4.942812	5%	2%
19
20
21		FULL	NET	0+ / 50 + …	….+3° sconto
22		14.68	3.67	50%
23
24
25
26		EXAMPLES	Group 1						Group 2
27		Full price	0.45	0.46	0.47	0.48	0.49	0.5	50+1%	50+2%	50+3%	50+4%	50%+5%
28	Product A	10.64	5.852	5.7456	5.6392	5.5328	5.4264	5.32	5.2668	5.2136	5.1604	5.1072	5.054
29	Product B	8.48	4.664	4.5792	4.4944	4.4096	4.3248	4.24	4.1976	4.1552	4.1128	4.0704	4.028
Sheet1

Cell Formulas
Range	Formula
C4,I4	C4	=B4*0.5*(1-D4)
E4,K9,E9,K4	E4	=IF(D4<0.5,D4,IF(D4<=0.55,"50 + "&ROUND((D4-0.5)*100,1)&"%",IF(D4<=0.6,"50 + 5 + "&ROUND((D4-0.55)*100,1)&"%",IF(D4<=0.65,"50 + 10 + "&ROUND((D4-0.6)*100,1)&"%","50 + 15 + "&ROUND((D4-0.65)*100,1)&"%"))))
D9,J9	D9	=IF((1-(C9/B9/0.5)<1),(1-(C9/B9)),(1-(C9/B9/0.5)))
C18,C22	C18	=B18*0.5*(1-D18)*(1-E18)
C28:C29	C28	=B28*0.55
D28:D29	D28	=B28*0.54
E28:E29	E28	=B28*0.53
F28:F29	F28	=B28*0.52
G28:G29	G28	=B28*0.51
H28:H29	H28	=B28*0.5
I28:I29	I28	=H28*0.99
J28:J29	J28	=H28*0.98
K28:K29	K28	=H28*0.97
L28:L29	L28	=H28*0.96
M28:M29	M28	=H28*0.95

 

[jasonb75]

Unless I'm missing something, try entering =B9 into B28, then

=XLOOKUP(C9,B28:AD28,B27:AD27,"",1)

into D9.

 

[Amoldavskii]

In my example:
The information I always have are the Full Price (B9) and Net Price (C9).
The X I need to find is D9

The data from A26 and lower are examples of possible situations, I tried by putting it, to show "which range it need to fit".
But even in this range, there can be imprecisions in prices, to give as results 50+10+4.6%. (example)

 

[jasonb75]

But even in this range, there can be imprecisions in prices, to give as results 50+10+4.6%. (example)

So are you saying that you want the exact discount calculated rather than the nearest one on the scale shown?

With a simple scale, xx.xx% it would be possible, but with xx.xx%+yy.yy%+zz.zz% you are asking the impossible.

 

[maabadi]

@Amoldavskii what about my solution?

 

[Amoldavskii]

ok let's forget about "But even in this range, there can be imprecisions in prices, to give as results 50+10+4.6%. (example)"

I believed it's possible assuming that 50+10+3% = 56.4%, so something similar to "if D9=value 56.4% = result 50+10+3%
(there are 28 standard discounts %, like in the rows from A28 to AD28)

 

[Amoldavskii]

@Amoldavskii what about my solution?

To be honest I use for first time the Xl2bb addon and having Excel in italian, even when I translate the "if" and "round" in usual italian formulas, it doesn't read the formula correctly.

I thank you very much because I'm going to study your formula as good as I can, and translate or repair it properly.

 

[jasonb75]

Did you try copying the whole XL2BB mini table using the copy icon in the top left corner, then pasting to a blank sheet?

I though that doing so would translate it automatically, unless it can translate to English, but not back again.

General rule of translation, in addition to the function names, you would need to change commas to semicolons and dots to commas.

 

[Amoldavskii]

Did you try copying the whole XL2BB mini table using the copy icon in the top left corner, then pasting to a blank sheet?

I though that doing so would translate it automatically, unless it can translate to English, but not back again.

General rule of translation, in addition to the function names, you would need to change commas to semicolons and dots to commas.

thanks for the advice, I found an utility that translate them directly.

Now it's working, but the result is imperfect because trying some simulations, it doesn't split the second discount, but I'm sure the maabdi formula has something I can inspire.