Inserting Logic inside an Array Formula

B

BMPeers

New Member
Joined
Nov 5, 2004
Messages
36
Dear Mr Excel Gurus,
<br />
<br />
Its nearly ten years since I have joined the board, and I still need your help and wisdom. I think this should be quite easy, but I have struggled with this problem. The problem essentially involves inserting an ‘if’ statement inside an array formula.
<br />
<br />
In column A is count of items distributed by age. The total is 389 items. I want to reduce this count by 55 in total. The current chance of an item being removed is shown by the distribution in column B, if this was applied I would lose 9.481 from the original count, as per column C.
<br />
<br />
To remove more items I have used a factor in C1 (currently set to 5), this increases the chance of an item being removed, ie. Column E = column B * 5. However the total being removed can never be greater than 1, see for example row 32.
<br />
<br />
This is why although the factor is 5 I still have not removed 55 items. What I could do is set a goal seek to change F34 to 55, by altering cell E2. However I can’t run a goal seek in this model.
<br />
<br />
Is it possible to find the factor required using only an array formula, with the array in column A, column B and the target 55??
<br />
<br />
Many Thanks in advance
Brian


<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">

<head>

<link rel=File-List href="Book3_files/filelist.xml">
<style id="Book2_11956_Styles">
<!--table
{mso-displayed-decimal-separator:"\.";
mso-displayed-thousand-separator:"\,";}
.xl1511956
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:black;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Calibri, sans-serif;
mso-font-charset:0;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
mso-background-source:auto;
mso-pattern:auto;
white-space:nowrap;}
.xl6411956
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:windowtext;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Arial, sans-serif;
mso-font-charset:0;
mso-number-format:0;
text-align:center;
vertical-align:bottom;
background:aqua;
mso-pattern:black none;
white-space:nowrap;}
.xl6511956
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:black;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Calibri, sans-serif;
mso-font-charset:0;
mso-number-format:General;
text-align:center;
vertical-align:bottom;
mso-background-source:auto;
mso-pattern:auto;
white-space:nowrap;}
.xl6611956
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:black;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Calibri, sans-serif;
mso-font-charset:0;
mso-number-format:General;
text-align:center;
vertical-align:bottom;
mso-background-source:auto;
mso-pattern:auto;
white-space:normal;}
.xl6711956
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:black;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Calibri, sans-serif;
mso-font-charset:0;
mso-number-format:Fixed;
text-align:center;
vertical-align:bottom;
mso-background-source:auto;
mso-pattern:auto;
white-space:nowrap;}
.xl6811956
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:black;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Calibri, sans-serif;
mso-font-charset:0;
mso-number-format:Fixed;
text-align:general;
vertical-align:bottom;
mso-background-source:auto;
mso-pattern:auto;
white-space:nowrap;}
.xl6911956
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:black;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Calibri, sans-serif;
mso-font-charset:0;
mso-number-format:General;
text-align:center;
vertical-align:bottom;
border:.5pt solid black;
background:#D9D9D9;
mso-pattern:black none;
white-space:nowrap;}
.xl7011956
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:black;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Calibri, sans-serif;
mso-font-charset:0;
mso-number-format:"0\.000";
text-align:center;
vertical-align:bottom;
mso-background-source:auto;
mso-pattern:auto;
white-space:nowrap;}
.xl7111956
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:black;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Calibri, sans-serif;
mso-font-charset:0;
mso-number-format:0;
text-align:center;
vertical-align:bottom;
mso-background-source:auto;
mso-pattern:auto;
white-space:nowrap;}
-->
</style>
</head>

<body>
<!--[if !excel]>  <![endif]-->
<!--The following information was generated by Microsoft Excel's Publish as Web
Page wizard.-->
<!--If the same item is republished from Excel, all information between the DIV
tags will be replaced.-->
<!----------------------------->
<!--START OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD -->
<!----------------------------->

<div id="Book2_11956" align=center x:publishsource="Excel">

<table border=0 cellpadding=0 cellspacing=0 width=548 style='border-collapse:
collapse;table-layout:fixed;width:410pt'>
<col width=64 style='width:48pt'>
<col width=88 style='mso-width-source:userset;mso-width-alt:3218;width:66pt'>
<col width=99 span=2 style='mso-width-source:userset;mso-width-alt:3620;
width:74pt'>
<col width=27 style='mso-width-source:userset;mso-width-alt:987;width:20pt'>
<col width=91 style='mso-width-source:userset;mso-width-alt:3328;width:68pt'>
<col width=80 style='mso-width-source:userset;mso-width-alt:2925;width:60pt'>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 width=64 style='height:12.75pt;width:48pt'> </td>
<td class=xl6911956 width=88 style='border-left:none;width:66pt'>A</td>
<td class=xl6911956 width=99 style='border-left:none;width:74pt'>B</td>
<td class=xl6911956 width=99 style='border-left:none;width:74pt'>C</td>
<td class=xl6911956 width=27 style='border-left:none;width:20pt'>D</td>
<td class=xl6911956 width=91 style='border-left:none;width:68pt'>E</td>
<td class=xl6911956 width=80 style='border-left:none;width:60pt'>F</td>
</tr>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 style='height:12.75pt;border-top:none'>1</td>
<td class=xl1511956></td>
<td class=xl1511956>Factor</td>
<td class=xl1511956></td>
<td class=xl1511956></td>
<td class=xl1511956 align=right>5</td>
<td class=xl1511956></td>
</tr>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 style='height:12.75pt;border-top:none'>2</td>
<td class=xl1511956></td>
<td class=xl1511956></td>
<td class=xl1511956></td>
<td class=xl1511956></td>
<td class=xl1511956></td>
<td class=xl1511956></td>
</tr>
<tr height=34 style='height:25.5pt'>
<td height=34 class=xl6911956 style='height:25.5pt;border-top:none'>3</td>
<td class=xl6511956>Original Count</td>
<td class=xl6611956 width=99 style='width:74pt'>Chance of Disappearance</td>
<td class=xl6611956 width=99 style='width:74pt'>Removed</td>
<td class=xl6611956 width=27 style='width:20pt'></td>
<td class=xl6611956 width=91 style='width:68pt'>Increased Chance</td>
<td class=xl6511956>Removed</td>
</tr>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 style='height:12.75pt;border-top:none'>4</td>
<td class=xl6411956>50</td>
<td class=xl7011956>0.004</td>
<td class=xl7011956>0.211</td>
<td class=xl7011956></td>
<td class=xl7011956>0.021</td>
<td class=xl6711956>1.06</td>
</tr>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 style='height:12.75pt;border-top:none'>5</td>
<td class=xl6411956>39</td>
<td class=xl7011956>0.004</td>
<td class=xl7011956>0.168</td>
<td class=xl7011956></td>
<td class=xl7011956>0.021</td>
<td class=xl6711956>0.84</td>
</tr>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 style='height:12.75pt;border-top:none'>6</td>
<td class=xl6411956>55</td>
<td class=xl7011956>0.004</td>
<td class=xl7011956>0.235</td>
<td class=xl7011956></td>
<td class=xl7011956>0.021</td>
<td class=xl6711956>1.17</td>
</tr>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 style='height:12.75pt;border-top:none'>7</td>
<td class=xl6411956>46</td>
<td class=xl7011956>0.004</td>
<td class=xl7011956>0.198</td>
<td class=xl7011956></td>
<td class=xl7011956>0.022</td>
<td class=xl6711956>0.99</td>
</tr>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 style='height:12.75pt;border-top:none'>8</td>
<td class=xl6411956>38</td>
<td class=xl7011956>0.004</td>
<td class=xl7011956>0.167</td>
<td class=xl7011956></td>
<td class=xl7011956>0.022</td>
<td class=xl6711956>0.83</td>
</tr>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 style='height:12.75pt;border-top:none'>9</td>
<td class=xl6411956>29</td>
<td class=xl7011956>0.025</td>
<td class=xl7011956>0.728</td>
<td class=xl7011956></td>
<td class=xl7011956>0.126</td>
<td class=xl6711956>3.64</td>
</tr>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 style='height:12.75pt;border-top:none'>10</td>
<td class=xl6411956>22</td>
<td class=xl7011956>0.026</td>
<td class=xl7011956>0.576</td>
<td class=xl7011956></td>
<td class=xl7011956>0.130</td>
<td class=xl6711956>2.88</td>
</tr>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 style='height:12.75pt;border-top:none'>11</td>
<td class=xl6411956>17</td>
<td class=xl7011956>0.027</td>
<td class=xl7011956>0.443</td>
<td class=xl7011956></td>
<td class=xl7011956>0.133</td>
<td class=xl6711956>2.22</td>
</tr>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 style='height:12.75pt;border-top:none'>12</td>
<td class=xl6411956>14</td>
<td class=xl7011956>0.027</td>
<td class=xl7011956>0.382</td>
<td class=xl7011956></td>
<td class=xl7011956>0.137</td>
<td class=xl6711956>1.91</td>
</tr>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 style='height:12.75pt;border-top:none'>13</td>
<td class=xl6411956>14</td>
<td class=xl7011956>0.028</td>
<td class=xl7011956>0.385</td>
<td class=xl7011956></td>
<td class=xl7011956>0.140</td>
<td class=xl6711956>1.93</td>
</tr>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 style='height:12.75pt;border-top:none'>14</td>
<td class=xl6411956>13</td>
<td class=xl7011956>0.050</td>
<td class=xl7011956>0.621</td>
<td class=xl7011956></td>
<td class=xl7011956>0.248</td>
<td class=xl6711956>3.11</td>
</tr>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 style='height:12.75pt;border-top:none'>15</td>
<td class=xl6411956>11</td>
<td class=xl7011956>0.052</td>
<td class=xl7011956>0.555</td>
<td class=xl7011956></td>
<td class=xl7011956>0.261</td>
<td class=xl6711956>2.77</td>
</tr>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 style='height:12.75pt;border-top:none'>16</td>
<td class=xl6411956>7</td>
<td class=xl7011956>0.055</td>
<td class=xl7011956>0.405</td>
<td class=xl7011956></td>
<td class=xl7011956>0.276</td>
<td class=xl6711956>2.03</td>
</tr>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 style='height:12.75pt;border-top:none'>17</td>
<td class=xl6411956>4</td>
<td class=xl7011956>0.058</td>
<td class=xl7011956>0.257</td>
<td class=xl7011956></td>
<td class=xl7011956>0.292</td>
<td class=xl6711956>1.29</td>
</tr>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 style='height:12.75pt;border-top:none'>18</td>
<td class=xl6411956>4</td>
<td class=xl7011956>0.062</td>
<td class=xl7011956>0.257</td>
<td class=xl7011956></td>
<td class=xl7011956>0.310</td>
<td class=xl6711956>1.29</td>
</tr>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 style='height:12.75pt;border-top:none'>19</td>
<td class=xl6411956>4</td>
<td class=xl7011956>0.083</td>
<td class=xl7011956>0.324</td>
<td class=xl7011956></td>
<td class=xl7011956>0.416</td>
<td class=xl6711956>1.62</td>
</tr>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 style='height:12.75pt;border-top:none'>20</td>
<td class=xl6411956>4</td>
<td class=xl7011956>0.091</td>
<td class=xl7011956>0.324</td>
<td class=xl7011956></td>
<td class=xl7011956>0.454</td>
<td class=xl6711956>1.62</td>
</tr>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 style='height:12.75pt;border-top:none'>21</td>
<td class=xl6411956>3</td>
<td class=xl7011956>0.100</td>
<td class=xl7011956>0.324</td>
<td class=xl7011956></td>
<td class=xl7011956>0.499</td>
<td class=xl6711956>1.62</td>
</tr>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 style='height:12.75pt;border-top:none'>22</td>
<td class=xl6411956>3</td>
<td class=xl7011956>0.111</td>
<td class=xl7011956>0.324</td>
<td class=xl7011956></td>
<td class=xl7011956>0.555</td>
<td class=xl6711956>1.62</td>
</tr>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 style='height:12.75pt;border-top:none'>23</td>
<td class=xl6411956>3</td>
<td class=xl7011956>0.125</td>
<td class=xl7011956>0.324</td>
<td class=xl7011956></td>
<td class=xl7011956>0.624</td>
<td class=xl6711956>1.62</td>
</tr>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 style='height:12.75pt;border-top:none'>24</td>
<td class=xl6411956>2</td>
<td class=xl7011956>0.138</td>
<td class=xl7011956>0.313</td>
<td class=xl7011956></td>
<td class=xl7011956>0.688</td>
<td class=xl6711956>1.56</td>
</tr>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 style='height:12.75pt;border-top:none'>25</td>
<td class=xl6411956>2</td>
<td class=xl7011956>0.160</td>
<td class=xl7011956>0.313</td>
<td class=xl7011956></td>
<td class=xl7011956>0.798</td>
<td class=xl6711956>1.56</td>
</tr>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 style='height:12.75pt;border-top:none'>26</td>
<td class=xl6411956>2</td>
<td class=xl7011956>0.190</td>
<td class=xl7011956>0.313</td>
<td class=xl7011956></td>
<td class=xl7011956>0.950</td>
<td class=xl6711956>1.56</td>
</tr>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 style='height:12.75pt;border-top:none'>27</td>
<td class=xl6411956>1</td>
<td class=xl7011956>0.234</td>
<td class=xl7011956>0.313</td>
<td class=xl7011956></td>
<td class=xl7011956>1.000</td>
<td class=xl6711956>1.33</td>
</tr>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 style='height:12.75pt;border-top:none'>28</td>
<td class=xl6411956>1</td>
<td class=xl7011956>0.306</td>
<td class=xl7011956>0.313</td>
<td class=xl7011956></td>
<td class=xl7011956>1.000</td>
<td class=xl6711956>1.02</td>
</tr>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 style='height:12.75pt;border-top:none'>29</td>
<td class=xl6411956>1</td>
<td class=xl7011956>0.200</td>
<td class=xl7011956>0.142</td>
<td class=xl7011956></td>
<td class=xl7011956>1.000</td>
<td class=xl6711956>0.71</td>
</tr>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 style='height:12.75pt;border-top:none'>30</td>
<td class=xl6411956>1</td>
<td class=xl7011956>0.250</td>
<td class=xl7011956>0.142</td>
<td class=xl7011956></td>
<td class=xl7011956>1.000</td>
<td class=xl6711956>0.57</td>
</tr>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 style='height:12.75pt;border-top:none'>31</td>
<td class=xl6411956>0</td>
<td class=xl7011956>0.333</td>
<td class=xl7011956>0.142</td>
<td class=xl7011956></td>
<td class=xl7011956>1.000</td>
<td class=xl6711956>0.43</td>
</tr>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 style='height:12.75pt;border-top:none'>32</td>
<td class=xl6411956>0</td>
<td class=xl7011956>0.500</td>
<td class=xl7011956>0.142</td>
<td class=xl7011956></td>
<td class=xl7011956>1.000</td>
<td class=xl6711956>0.28</td>
</tr>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 style='height:12.75pt;border-top:none'>33</td>
<td class=xl6411956>0</td>
<td class=xl7011956>1.000</td>
<td class=xl7011956>0.142</td>
<td class=xl7011956></td>
<td class=xl7011956>1.000</td>
<td class=xl6711956>0.14</td>
</tr>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 style='height:12.75pt;border-top:none'>34</td>
<td class=xl7111956>389</td>
<td class=xl1511956>SUM</td>
<td class=xl7011956>9.481</td>
<td class=xl1511956></td>
<td class=xl1511956>SUM</td>
<td class=xl6811956 align=right>45.21</td>
</tr>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 style='height:12.75pt;border-top:none'>35</td>
<td class=xl1511956></td>
<td class=xl1511956></td>
<td class=xl1511956></td>
<td class=xl1511956></td>
<td class=xl1511956></td>
<td class=xl1511956></td>
</tr>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 style='height:12.75pt;border-top:none'>36</td>
<td class=xl1511956></td>
<td class=xl1511956></td>
<td class=xl1511956></td>
<td class=xl1511956></td>
<td class=xl1511956>TARGET</td>
<td class=xl1511956 align=right>55</td>
</tr>
<tr height=10 style='height:12.75pt'>
<td height=10 class=xl6911956 style='height:12.75pt;border-top:none'>37</td>
<td class=xl1511956></td>
<td class=xl1511956></td>
<td class=xl1511956></td>
<td class=xl1511956></td>
<td class=xl1511956></td>
<td class=xl1511956></td>
</tr>
<![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
<td width=64 style='width:48pt'></td>
<td width=88 style='width:66pt'></td>
<td width=99 style='width:74pt'></td>
<td width=99 style='width:74pt'></td>
<td width=27 style='width:20pt'></td>
<td width=91 style='width:68pt'></td>
<td width=80 style='width:60pt'></td>
</tr>
<![endif]>
</table>

</div>


<!----------------------------->
<!--END OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD-->
<!----------------------------->
</body>

</html>
 
Sort by date Sort by votes
Hello Brian, assuming the result you want will be somewhere in the range 1 to 10 then try this "array formula"

=MATCH(55,MMULT(IF(ROW(INDIRECT("1:10000"))/1000*TRANSPOSE(B4:B33)>1,1,ROW(INDIRECT("1:10000"))/1000*TRANSPOSE(B4:B33)),A4:A33),1)/1000

confirmed with CTRL+SHIFT+ENTER

that effectively generates 10000 results with your factor set at every 0.001 from 0.001 to 10 then MATCH finds the nearest match to get 55. Because of the way it calculates it will only be accurate to the nearest 0.01. For your example I get 6.237
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
951
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
706
mumps
mumps
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
1K
epactheactor
E
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
3K
Attrazion
A
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Share this page

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
Back
Top