Would an IF/OR formula work for this?

Marq

Well-known Member
Joined
Dec 13, 2004
Messages
913
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
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?
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,238
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
how about

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

Marq

Well-known Member
Joined
Dec 13, 2004
Messages
913
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Oct 30, 2014
Messages
4,238
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
use conditional formatting like this

Book1
JKLMNOPQ
12100150-50100%
Sheet1
Cell Formulas
RangeFormula
M12M12=J12-L12
Q12Q12=IF(J12>0,MIN(1,L12/J12),0)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
Q12Expression=$M$12<0textNO
 

Marq

Well-known Member
Joined
Dec 13, 2004
Messages
913
Office Version
  1. 365
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

use conditional formatting like this

Book1
JKLMNOPQ
12100150100%
Sheet1
Cell Formulas
RangeFormula
Q12Q12=IF(J12>0,MIN(1,L12/J12),0)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
Q12Expression=$Q$12>=1textNO


Book1
JKLMNOPQ
121005050%
Sheet1
Cell Formulas
RangeFormula
Q12Q12=IF(J12>0,MIN(1,L12/J12),0)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
Q12Expression=$Q$12>=1textNO

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

Well-known Member
Joined
Oct 30, 2014
Messages
4,238
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
use conditional formatting like this

Book1
JKLMNOPQ
12100150-50100%
Sheet1
Cell Formulas
RangeFormula
M12M12=J12-L12
Q12Q12=IF(J12>0,MIN(1,L12/J12),0)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
Q12Expression=$M$12<0textNO
will this works for you?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,589
Messages
5,597,053
Members
414,118
Latest member
moversnpackers

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top