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

 

[jasonb75]

Did yo try the one that I suggested, or are you looking to do it without the rows at the bottom?

 

[Amoldavskii]

Did yo try the one that I suggested, or are you looking to do it without the rows at the bottom?

Exactly, trying to do it without the rows at bottom. (Because the amount of that products can be long (like 500+) and updating all the year).

 

[Amoldavskii]

Trying plan B, simplified, can someone help me with this idea, example:

Considering that 50+10+3% = 56.4% and so on each possible discount (50+5+1% = 52.9%), there are 28 standard discounts % like in the rows from C28 to AD28.

Can you help me please with the formula, something similar to "if D9=value 56.4%", to check it from C28 to AD28 (with that 28 standard discounts), and if true, to show to which it's equal= show cell V27 text "50+10+3%"

 

[jasonb75]

That would be a much better way, I'm kicking myself for not thinking of it earlier.

The formula from post 3 should do just that if you change C9 to D9.

You may also need to change 1 at the end to -1 if anything that is not an exact match is rounding the wrong way.