Divide Arrays

[anthonyexcel]

I have 2 Arrays each located in a cell. I would like to divide the first one by the second one. Please see below.

Array
{0;1;0;1;0;0;1}
{0;1;2;2;0;1;2}

<tbody>
</tbody>

<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC ;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC ;
}
.tableizer-table th {
background-color: #104E8B ;
color: #FFF ;
font-weight: bold;
}
</style>

Answer
{#DIV/0!;1;0;0.5,#DIV/0!,0,0.5}

<thead>
</thead><tbody>
</tbody>

 

[dave2018]

I have 2 Arrays each located in a cell. I would like to divide the first one by the second one. Please see below.

Array
{0;1;0;1;0;0;1}
{0;1;2;2;0;1;2}

<tbody>
</tbody>

<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC ;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC ;
}
.tableizer-table th {
background-color: #104E8B ;
color: #FFF ;
font-weight: bold;
}
</style>

Answer
{#DIV/0!;1;0;0.5,#DIV/0!,0,0.5}

<tbody>
</tbody>

you are getting an error #DIV/0! because u cant divide anything by 0!


if you are looking to populate 0 instead of the error, you can wrap it in an iferror, (which is equivalent to =0/0 which will equal 0...)

=IFERROR({0;1;0;1;0;0;1}/{0;1;2;2;0;1;2},0)

(if that doesnt work for you, pls respond how you get to the 2 arrays?)

 

[anthonyexcel]

Hi dave2018,

Thanks for the reply. I am looking for a VBA Function something like =xArray(A2,A3) and that will return the answer {#DIV/0!;1;0;0.5,#DIV/0!,0,0.5} in another cell.

 

[dave2018]

Hi dave2018,

Thanks for the reply. I am looking for a VBA Function something like =xArray(A2,A3) and that will return the answer {#DIV/0!;1;0;0.5,#DIV/0!,0,0.5} in another cell.

care to post a snapshot of how it should look? or is this how it looks now?


im just wondering how you get the 2 arrays to begin with?

 

[anthonyexcel]

dave2018, that is exactly what it looks like. It is part of some formula evaluation that I am doing.

 

[dave2018]

dave2018, that is exactly what it looks like. It is part of some formula evaluation that I am doing.

in my example, they are text.

Meaning, in A1: "{0;1;0;1;0;0;1}" Not: ={0;1;0;1;0;0;1}

in that case, you need to parse each value into a real array, not text values in 1 string...

=IFERROR(FILTERXML("<t><s>"&SUBSTITUTE(MID(A1,2,LEN(A1)-2),";","</s><s>")&"</s></t>",{"//s";"//s"})/FILTERXML("<t><s>"&SUBSTITUTE(MID(A2,2,LEN(A2)-2),";","</s><s>")&"</s></t>",{"//s";"//s"}),0)

here is a screenshot of when i evaluate F9 in edit mode:


does that work?

 

[anthonyexcel]

dave2018 that seems to work!!! Thanks!! I never used the FILTERXML function!! Nice to know!!!

 

[dave2018]

dave2018 that seems to work!!! Thanks!! I never used the FILTERXML function!! Nice to know!!!

LOL. Me neither!! until 2 days ago when i saw it on stackoverflow here!! i am addicted to this formula!

(u probably understand the hack being worked here, but if not, i can try to explain)

 

[anthonyexcel]

dave2018,

Thanks for the link!! Appreciate it!! LOL we are all addicted to something!!