Would an IF/OR formula work for this?

[Marq]

I have a progress sheet I use to track the quantity of insulation installed.

My current formula in Q12 is this: =IF(J12>0,L12/J12,0)

Its telling me if the estimated quantity in J12 is greater than zero, then divided the quantity installed to date (L12) by the estimated quantity (J12)....which then produces me a percentage complete.

If the workers exceed the quantity installed to date my percentage complete will then exceed 100%...so I have line items showing 223%...and 132%...I need those to max out at 100%

I would like the formula to still do what its doing now BUT make the percent complete MAX OUT at 100%.....so even if they exceed the estimated quantity, the cell the formula is in will not go above 100%

Can someone please help me out?

 

[AlanY]

how about

=IF(J12>0,MIN(1,L12/J12),0)

 

[Marq]

how about

=IF(J12>0,MIN(1,L12/J12),0)

That works perfect!..Thanks!

I just thought of a cool idea...(not sure if I should make another thread for this since its a formatting issue??,,but i'll just post it here unless Im told to move it)..how do I make the 100% show up as red and bold?

Meaning, in cell M12 I show the diff between the quantity estimated (j12) and the quantity installed (L12). the red and bold would be based on the value of M12...if M12 is negative then make Q12 value shows as red and bold.

 

[AlanY]

use conditional formatting like this

Book1
	J	K	L	M	N	O	P	Q
12	100		150	-50				100%
Sheet1

Cell Formulas
Range	Formula
M12	M12	=J12-L12
Q12	Q12	=IF(J12>0,MIN(1,L12/J12),0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Q12	Expression	=$M$12<0	text	NO

 

[Marq]

use conditional formatting like this

Book1
	J	K	L	M	N	O	P	Q
12	100		150					100%
Sheet1

Cell Formulas
Range	Formula
Q12	Q12	=IF(J12>0,MIN(1,L12/J12),0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Q12	Expression	=$Q$12>=1	text	NO

Book1
	J	K	L	M	N	O	P	Q
12	100		50					50%
Sheet1

Cell Formulas
Range	Formula
Q12	Q12	=IF(J12>0,MIN(1,L12/J12),0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Q12	Expression	=$Q$12>=1	text	NO

Is M12 being referenced in this scenario? Because if they install exactly what is estimated and nothing more, then Q12 will show as 100% and there would be no reason to "flag" Q12 with a red and bold 100%...….but if they exceed the quantity (based on negative value in M12), then make Q12 red and bold.

 

[AlanY]

use conditional formatting like this

Book1
	J	K	L	M	N	O	P	Q
12	100		150	-50				100%
Sheet1

Cell Formulas
Range	Formula
M12	M12	=J12-L12
Q12	Q12	=IF(J12>0,MIN(1,L12/J12),0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Q12	Expression	=$M$12<0	text	NO

will this works for you?

 

[Marq]

will this works for you?

Yep!!..THANKS!!

I click it showing its a solution but I can only click one per thread.

 

[AlanY]

Yep!!..THANKS!!

I click it showing its a solution but I can only click one per thread.

you're welcome