How to add that percentage in column H.

[Romano_odK]

Good afternoon,
I am running into a problem which I can't solve. In column I until M you can fill in a number or percentage, depends on the field. In column G you will see that result. This works like I wanted it to.
Now you have column G. What this should do is add a percentage (actually I need marge) to the value that given in column H. I first thought another IF value, but I can't realize that, gives me errors. Does anyone know how to do this?

Thank you for your time.

Artikelen beheren 6.06.xlsm
	F	G	H	I	J	K	L	M	N	O
5	Verkoopprijs	Grossier +korting%	Verkoopprijs nieuw	Vkp bedrag	Vkp + opslag%	Kp +factor	Kp +opslag%	Kp +marge%	Eenheid	Kostprijs
6	10,00	30,0%	25,00					60,0%	pak	10,00
7	11,00								pak	20,00
8	23,00								pak	30,00
Items

Cell Formulas
Range	Formula
H6:H8	H6	=IFERROR(IFS(M6>0,O6/(1-M6), L6>0,O6*(1*L6)+O6, K6>0,O6*K6+O6, J6>0,F6*(1*J6)+F6, I6>0,I6),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O6:O7088	Expression	=P6	text	NO
AQ6:AQ7088,F6:F7088	Expression	=H6	text	NO

 

[Romano_odK]

@Romano_odK I think this time it should work.

Try this formula for G8:

= IFERROR(
 IFS(
  M8>0; IF(H8>0; (O8/(1-M8))/(1+H8); O8/(1-M8));
  L8>0; IF(H8>0; (O8*(1*L8)+O8)/(1+H8); O8*(1*L8)+O8);
  K8>0; IF(H8>0; (O8*K8+O8)/(1+H8); O8*K8+O8);
  J8>0; IF(H8>0; (F8*(1*J8)+F8)/(1+H8); F8*(1*J8)+F8);
  I8>0; IF(H8>0; (I8)/(1+H8); I8)
 );
  "")

I had it the other way round (120 being 100%, but now 120 is 130% again)

Please let me know if the formula works.

No sorry, I added an example. When I fill in I6 100 and 20% in H6, I should have gotten 125 in G6. When you subtract that 20% again from 125 you get 100 again. Sorry for the inconvenience.

Artikelen beheren 6.08.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
5	Artikelcode	Zoekcode	Omschrijving	Status	Startdatum	Verkoopprijs	Verkoopprijs nieuw	Grossier +korting%	Vkp bedrag	Vkp + opslag%	Kp +factor	Kp +opslag%	Kp +marge%	Eenheid	Kostprijs
6	100000	8712759037825	Vito Glaserfix 111 6x2 mm wit - 10x25 m	A	24/09/2019	20,66	83,33	20,0%	100,00					pak	6,70
Artikelen

Cell Formulas
Range	Formula
G6	G6	= IFERROR( IFS( M6>0, IF(H6>0, (O6/(1-M6))/(1+H6), O6/(1-M6)), L6>0, IF(H6>0, (O6*(1*L6)+O6)/(1+H6), O6*(1*L6)+O6), K6>0, IF(H6>0, (O6*K6+O6)/(1+H6), O6*K6+O6), J6>0, IF(H6>0, (F6*(1*J6)+F6)/(1+H6), F6*(1*J6)+F6), I6>0, IF(H6>0, (I6)/(1+H6), I6) ), "")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F6:F7093,O6:O7093	Expression	=G6	text	NO
C6:C7093	Expression	=LEN(C6)>60	text	NO
B6:B7093	Cell Value	duplicates	text	NO

 

[PeteWright]

No problem.

When I fill in I6 100 and 20% in H6, I should have gotten 125 in G6. When you subtract that 20% again from 125 you get 100 again.

Shouldn't it be 25% in H6 if you want G6 to be 125?
Or should it rather be 120 in G6?

 

[Romano_odK]

No problem.

Shouldn't it be 25% in H6 if you want G6 to be 12

It really should be 125 in cell G6. Actually the formula I am looking for is already there in another place. When you take the line in the formula which concerns cell M6 you can see it. When you fill in 100 in cell O6 "Kostprijs" you get the same result. G3 turns to 125. The only thing is I do not understand how to implement it. This is "margin" calculating and that different from just adding a percentage to a number.

 

[PeteWright]

@Romano_odK
I think I may need some knowledge of financial calculation (which I don't have - unfortunately)

But if this can be solved by mathematical logic, I may find a solution.

Now a few more questions:

1. How many combinations of values in columns I, J, K, L and M are possible?
2. Which cells in columns can be entered at the same time in one single row? (e.g. {H} and {I or J or K or L or M})
3. Is your formula correct? That means, that the first condition that is true will be calculated (in order of your IFS statement)

Sorry for asking again, I hope we'll figure it out soon.

 

[Romano_odK]

@Romano_odK
I think I may need some knowledge of financial calculation (which I don't have - unfortunately)

But if this can be solved by mathematical logic, I may find a solution.

Now a few more questions:

1. How many combinations of values in columns I, J, K, L and M are possible?
2. Which cells in columns can be entered at the same time in one single row? (e.g. {H} and {I or J or K or L or M})
3. Is your formula correct? That means, that the first condition that is true will be calculated (in order of your IFS statement)

Sorry for asking again, I hope we'll figure it out soon.

1. just 1 value is allowed
2. only 1 value
3. yes it works properly, ok it would be better if you were only able to allow one entry in the columns I until M, but I do not know how to force that, so it works if you remember you can only use one value.

I really appreciate you putting so much time in this. This is part of a huge sheet that is able to generate an XML and get its info from a SQL server, this work well. Sometimes you have to increase prices or calculate a better margin or profit. This is why I need this. Thank you again.

 

[PeteWright]

@Romano_odK

I finally found a formula (for G6) that gets all the results you showed in post #7

= IFERROR(
 IFS(
  M6>0, IF(H6>0, (O6/(1-M6))/(1-H6), O6/(1-M6)),
  L6>0, IF(H6>0, (O6*(1*L6)+O6)/(1-H6), O6*(1*L6)+O6),
  K6>0, IF(H6>0, (O6*K6+O6)/(1-H6), O6*K6+O6),
  J6>0, IF(H6>0, (F6*(1*J6)+F6)/(1-H6), F6*(1*J6)+F6),
  I6>0, IF(H6>0, (I6)/(1-H6), I6)
 ),
  "")

I hope this is what you need

Please let me know.

 

[Romano_odK]

@Romano_odK

I finally found a formula (for G6) that gets all the results you showed in post #7

= IFERROR(
 IFS(
  M6>0, IF(H6>0, (O6/(1-M6))/(1-H6), O6/(1-M6)),
  L6>0, IF(H6>0, (O6*(1*L6)+O6)/(1-H6), O6*(1*L6)+O6),
  K6>0, IF(H6>0, (O6*K6+O6)/(1-H6), O6*K6+O6),
  J6>0, IF(H6>0, (F6*(1*J6)+F6)/(1-H6), F6*(1*J6)+F6),
  I6>0, IF(H6>0, (I6)/(1-H6), I6)
 ),
  "")

I hope this is what you need

Please let me know.

It works, it definitely works. Thank you for all you time you put in to this helping me. Now I can proceed to the next piece of this sheet. Thank you again and I wish you a great weekend.

 

[PeteWright]

It works, it definitely works. Thank you for all you time you put in to this helping me. Now I can proceed to the next piece of this sheet. Thank you again and I wish you a great weekend.

I'm glad I could help. Thanks for the feedback.

If you get any errors or need further assistance with your Worksheet, just let me know.

Have a nice weekend, too.
Best regards
Pete