When the value is lower then an error message.

[Romano_odK]

Good morning,
My first step was to ask for the solution, which I got here but it does not work as I want to so I added the sheet and now I can explain what I need.
O5-N5 should not be lower then (J5+L5)+K5. If it does then it should say TE HOOG.

Can this be done?
Thank you for you time.

Romano

Prijscalculatie v2.7.06.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Prijscalculatie		Berekening Inkoopprijs						Berekening Kostprijs		Berekening Bruto Verkoopprijs
2											Marge		Factor	Grossier korting
3									3,0%
4	Artikelnummer	Omschrijving	Bruto Inkoopprijs	Korting	Netto Inkoopprijs	Opslag op Bruto Ikp %	Opslag op Bruto Ikp regel	Berekende Inkoopprijs	Kostprijs bedrag	Kostprijs	Marge regel	Marge bedrag	Factor regel	Grossier regel	Bruto Verkoopprijs
5	618000	Product 1	2717,00	30,0%	1901,90			1901,90	57,06	1958,96	60,0%	1175,37		90,0%	31343,31
Manual

Cell Formulas
Range	Formula
O3	O3	=IFERROR(AVERAGE(#REF!),"")
E5	E5	=C5-(C5*D5)
F5	F5	=C5*$F$3
H5	H5	=IFS(AND([@[Opslag op Bruto Ikp %]]=0,[@[Opslag op Bruto Ikp regel]]=0),E5,G5>0,E5*G5+E5,F5>0,((E5)-((E5)*D5)+F5))
I5	I5	=(H5*$I$3)
J5	J5	=[@[Berekende Inkoopprijs]]+[@[Kostprijs bedrag]]
L5	L5	=IFERROR(IFS($K$3>0,(J5*$K$3),K5>0,(J5*K5)),"")
O5	O5	=IFERROR(IFS(AND($M$3>0,$N$3>0),(J5*$M$3)/(100%-$N$3), AND($K$3>0,$N$3>0),(J5+L5)/(100%-$N$3), AND($M$3>0,N5>0),((J5*$M$3)/(100%-N5)), AND(K5>0,$N$3>0),((J5+L5)/(100%-$N$3)), AND($K$3>0,$M$3>0),"FOUT", AND(K5>0,M5>0),"FOUT", $K$3>0,(J5+L5)/(100%-N5), K5>0,(J5+L5)/(100%-N5), $M$3>0,(J5*$M$3)/(100%-$N$3), M5>0,(J5*M5)/(100%-N5), $M$3>0,(J5*$M$3)/(100%-N5)), "")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O:O	Cell Value	="FOUT"	text	NO
L4:N63	Cell	contains an error	text	NO

 

[James006]

Hi,
Not sure to fully understand your requirement ...
What about adding in cell P5 =IF((O5-N5)<(J5+L5+K5),"TE HOOG","")

 

[Romano_odK]

Good morning,
My first step was to ask for the solution, which I got here but it does not work as I want to so I added the sheet and now I can explain what I need.
O5-N5 should not be lower then (J5+L5)+K5. If it does then it should say TE HOOG.

Can this be done?
Thank you for you time.

Romano

Prijscalculatie v2.7.06.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Prijscalculatie		Berekening Inkoopprijs						Berekening Kostprijs		Berekening Bruto Verkoopprijs
2											Marge		Factor	Grossier korting
3									3,0%
4	Artikelnummer	Omschrijving	Bruto Inkoopprijs	Korting	Netto Inkoopprijs	Opslag op Bruto Ikp %	Opslag op Bruto Ikp regel	Berekende Inkoopprijs	Kostprijs bedrag	Kostprijs	Marge regel	Marge bedrag	Factor regel	Grossier regel	Bruto Verkoopprijs
5	618000	Product 1	2717,00	30,0%	1901,90			1901,90	57,06	1958,96	60,0%	1175,37		90,0%	31343,31
Manual

Cell Formulas
Range	Formula
O3	O3	=IFERROR(AVERAGE(#REF!),"")
E5	E5	=C5-(C5*D5)
F5	F5	=C5*$F$3
H5	H5	=IFS(AND([@[Opslag op Bruto Ikp %]]=0,[@[Opslag op Bruto Ikp regel]]=0),E5,G5>0,E5*G5+E5,F5>0,((E5)-((E5)*D5)+F5))
I5	I5	=(H5*$I$3)
J5	J5	=[@[Berekende Inkoopprijs]]+[@[Kostprijs bedrag]]
L5	L5	=IFERROR(IFS($K$3>0,(J5*$K$3),K5>0,(J5*K5)),"")
O5	O5	=IFERROR(IFS(AND($M$3>0,$N$3>0),(J5*$M$3)/(100%-$N$3), AND($K$3>0,$N$3>0),(J5+L5)/(100%-$N$3), AND($M$3>0,N5>0),((J5*$M$3)/(100%-N5)), AND(K5>0,$N$3>0),((J5+L5)/(100%-$N$3)), AND($K$3>0,$M$3>0),"FOUT", AND(K5>0,M5>0),"FOUT", $K$3>0,(J5+L5)/(100%-N5), K5>0,(J5+L5)/(100%-N5), $M$3>0,(J5*$M$3)/(100%-$N$3), M5>0,(J5*M5)/(100%-N5), $M$3>0,(J5*$M$3)/(100%-N5)), "")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O:O	Cell Value	="FOUT"	text	NO
L4:N63	Cell	contains an error	text	NO

Ok what I forgot to mention is that the solution need to be added to the formula in cell O5.

 

[Romano_odK]

Hi,
Not sure to fully understand your requirement ...
What about adding in cell P5 =IF((O5-N5)<(J5+L5+K5),"TE HOOG","")

Thank you, what I need is it to work in cell O5.

 

[James006]

Hi again,

Do you mean you would need an Event macro to pop up as soon as a value input ( in J5,K5,L5,N5) triggers the message "TE HOOG" ?

 

[Romano_odK]

Hi again,

Do you mean you would need an Event macro to pop up as soon as a value input ( in J5,K5,L5,N5) triggers the message "TE HOOG" ?

That would be a solution too, but I rather have it working in cell O5. If that isn't possible then yes a macro would be fine.

 

[Fluff]

I rather have it working in cell O5

You cannot have a formula that refers to itself, without enabling iterative calculation & that is something most people would recommend against doing.

 

[Romano_odK]

You cannot have a formula that refers to itself, without enabling iterative calculation & that is something most people would recommend against doing.

Ok so what would the best solution then be, a macro as suggested?

 

[Fluff]

3 options I can think of.
1) A macro
2) Put the warning in another cell.
3) Use conditional formatting.

 

[Romano_odK]

3 options I can think of.
1) A macro
2) Put the warning in another cell.
3) Use conditional formatting.

Ok then I will try option 3. Thank you.