Nested, Multi Step Formula & Convert Numbers to PERCENTAGE then to Code Number

[hwhuyler]

I have spreadsheets with multiple rows 3000, 6000, or more rows.
Column 1 or Cell A1 is the TOTAL.
Column 2 or Cell A2 is the amount

If cell A2 is more than 90% of cell A1 I want to show a number 1 in cell A3.
If cell B2 is between 89% & 80% of cell B1 I want a 2 in cell B3.
If cell C2 is between 79% & 65% of cell C1 I want a 2 in cell C3.
If cell D2 is less than 65% (64% or less) than D1 I want a 4 in D3.

 

[hwhuyler]

Appreciate any thoughts and formula to help me move on.

 

[mikerickson]

Try putting this in A3.

=5-MATCH(A2/A1, {0,0.65,0.8,0.9} )

 

[markmzz]

Try this:

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right">8851</TD><TD style="TEXT-ALIGN: right">2702</TD><TD style="TEXT-ALIGN: right">3778</TD><TD style="TEXT-ALIGN: right">9923</TD><TD style="TEXT-ALIGN: right">8391</TD><TD style="TEXT-ALIGN: right">6513</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right">8391</TD><TD style="TEXT-ALIGN: right">2169</TD><TD style="TEXT-ALIGN: right">2469</TD><TD style="TEXT-ALIGN: right">8391</TD><TD style="TEXT-ALIGN: right">1532</TD><TD style="TEXT-ALIGN: right">2169</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right">94,80%</TD><TD style="TEXT-ALIGN: right">80,27%</TD><TD style="TEXT-ALIGN: right">65,35%</TD><TD style="TEXT-ALIGN: right">84,56%</TD><TD style="TEXT-ALIGN: right">18,26%</TD><TD style="TEXT-ALIGN: right">33,30%</TD></TR></TBODY></TABLE>

Plan2​

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>A3</TH><TD style="TEXT-ALIGN: left">=LOOKUP(A2/A1,{0;0.65;0.8;0.9},{4;3;2;1})</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Markmzz