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

 

[PeteWright]

I'm not sure what you mean.
Can give me another example please?

 

[Romano_odK]

I'm not sure what you mean.
Can give me another example please?

I can understand that, I changed the layout a bit. Now the last line I filled in 3 in cell K8 K and that results in G8 being 120. In cell H8 I filled in 30%. I take the value in cell G8 to be 100%, now I need to change that to 130%, so value 120 which is a 100% + the 30% from cell H8 to be added. The formula in cell G8 should do that. I hope I made it a bit clearer now, but can this be done?

Artikelen beheren 6.06.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Importdatum	23/01/2024	C:\TEMP\ART053_2024-01-23.XML			VERKOOP									KOSTPRIJS
2	Resource	1003
3	Valuta	EUR
4	Teller	53
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	10,00								pak	10,00
7	100001	8712759037832	Vito Glaserfix 111 6x2 mm zwart - 10x25 m	A	24/09/2019	11,00								pak	20,00
8	100002	8712759037849	Vito Glaserfix 111 6x3 mm wit - 10x25 m	A	24/09/2019	23,00	120,00	30,0%			3,0			pak	30,00
Items

Cell Formulas
Range	Formula
B1	B1	=TODAY()
C1	C1	=TEXT("C:\TEMP\ART",0)&TEXT($B$4,"000")&"_"&TEXT($B$1,"JJJJ")&"-"&TEXT($B$1,"MM")&"-"&TEXT($B$1,"DD")&".XML"
G6:G8	G6	=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
F6:F7088,O6:O7088	Expression	=G6	text	NO
C6:C7088	Expression	=LEN(C6)>60	text	NO
B6:B7088	Cell Value	duplicates	text	NO

 

[PeteWright]

@Romano_odK I may have a solution, but only if I got you right.

Let me show you the math:
You wrote that the result, 120 in this case, is 130% and you need to find the value (let us name it "V") being 100%, so
120,00 = 1.00 x V + 0.30 x V = (1.00 + 0.30) x V => V = 120,00 / (1.00 + 0.30) = 92.3077

Now let's check that
92.3077 + 0.30 x 92.3077 = 120.00001

Then the formula should be as follows (showing only the part where K8 > 0 is being evaluated):
...

K8>0,(O8*K8+O8)/(1+H8)

...
With this formula G8 should equal 92.31


Until here, do you agree with me? Or was that not what you ment?
Sorry to say, but I am still unsure if I understood your problem.

If this is not exactly what you need, please provide me any further details that are needed to calculate G8.

Regards
Pete

 

[Romano_odK]

@Romano_odK I may have a solution, but only if I got you right.

Let me show you the math:
You wrote that the result, 120 in this case, is 130% and you need to find the value (let us name it "V") being 100%, so
120,00 = 1.00 x V + 0.30 x V = (1.00 + 0.30) x V => V = 120,00 / (1.00 + 0.30) = 92.3077

Now let's check that
92.3077 + 0.30 x 92.3077 = 120.00001

Then the formula should be as follows (showing only the part where K8 > 0 is being evaluated):
...

K8>0,(O8*K8+O8)/(1+H8)

...
With this formula G8 should equal 92.31


Until here, do you agree with me? Or was that not what you ment?
Sorry to say, but I am still unsure if I understood your problem.

If this is not exactly what you need, please provide me any further details that are needed to calculate G8.

Regards
Pete

Good afternoon Pete,
Thank you for your efforts. I understand what you are writing. The bottom-line is that when I add a value in either H8 I8 J8 K8 L8 or M8 its results in a value in G8 because of the formula this is there. Now what I need is that when I also add a value in H3 it should take the value that is already there in G8 and add the % I would like to add in G8. So that makes the initial value 100% and that should be added with the value I add in H8, making it 130%. The added formula for this to should be added to the one in G8.

 

[PeteWright]

Okay, I'm a little bit confused now... Is that a typo there?

... add a value in H3 ...

In cell H3 there is nothing, I guess you meant H8, right?

Now, which values are depending on which input?


It would make things a lot easier for me if you could provide a mini table (or mini sheet) with all possible combinations and their values but that's maybe too much I'm asking for.

 

[Romano_odK]

Okay, I'm a little bit confused now... Is that a typo there?

In cell H3 there is nothing, I guess you meant H8, right?

Now, which values are depending on which input?


It would make things a lot easier for me if you could provide a mini table (or mini sheet) with all possible combinations and their values but that's maybe too much I'm asking for.

You are right that should have been H8 there. I haven't put a value in column H, but that could be anything. In column I until M I can fill in whatever I want, its a percentage and it should add to the value you get in column H. If first thought that I should use another IF statement to determent if there is a value in column H or not and if there were one make a formula to calculate it, but I already have an IFS statement in column G.

Artikelen beheren 6.07.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	10,00	5,56						10,0%	pak	5,00
7	100001	8712759037832	Vito Glaserfix 111 6x2 mm zwart - 10x25 m	A	24/09/2019	20,00	6,30					5,0%		pak	6,00
8	100002	8712759037849	Vito Glaserfix 111 6x3 mm wit - 10x25 m	A	24/09/2019	30,00	42,00				5,0			pak	7,00
9	100003	8712759037856	Vito Glaserfix 111 6x3 mm zwart - 10x25 m	A	24/09/2019	40,00	42,00			5,00%				pak	8,00
10	100004	8712759037863	Vito Glaserfix 111 6x4 mm wit - 10x25 m	A	24/09/2019	60,00	10,00		10,00					pak	9,00
Items

Cell Formulas
Range	Formula
G6:G10	G6	=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
F6:F9861,O6:O9861	Expression	=G6	text	NO
C6:C9861	Expression	=LEN(C6)>60	text	NO
B6:B9861	Cell Value	duplicates	text	NO

 

[PeteWright]

@Romano_odK I'm still unsure if I got you right, but I figured out a formula to what I think you meant:

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)
 );
  "")

Basically it's the same calculation but added to each combination of the IFS function.

So what it does:
First it checks if the value entered in column H is greater than 0 and then takes the values that were calculated in G8 and treats the result as 100%. Then the percentage is added e.g. 30%
If there is no number > 0 in column H the original formula is used.

So, now I'm waiting for your feedback.

Don't worry if it still is not the right formula, I'll keep on trying until we get it done

Regards
Pete

 

[Romano_odK]

@Romano_odK I'm still unsure if I got you right, but I figured out a formula to what I think you meant:

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)
 );
  "")

Basically it's the same calculation but added to each combination of the IFS function.

So what it does:
First it checks if the value entered in column H is greater than 0 and then takes the values that were calculated in G8 and treats the result as 100%. Then the percentage is added e.g. 30%
If there is no number > 0 in column H the original formula is used.

So, now I'm waiting for your feedback.

Don't worry if it still is not the right formula, I'll keep on trying until we get it done

Regards
Pete

Good afternoon Pete,
This is very very close, but not quite. I indeed does work, but the percentage should do is make it 130%, so when you subtract 30% again you get the 100% again. This is the only thing I am missing right now and I am already very happy. Thank you for your efforts.

 

[PeteWright]

@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.