Hello,
My understanding of array formulae is limited, and this problem has me stumped.
If the array formula involves division, and several of the "pairs" results in #DIV/0!, it seems the whole formula results in #DIV/0!(?).
Is there any way to handle the error, so that the "pairs" that do work "survive"?
Keeping safe and in good spirits
Tim C
Hi Tim,
What version of Excel are you using?
If I divide an array by another array with a 0 in it, the only #DIV/0 I get is in the position corresponding to where the 0 is in the 2nd array.
Can you provide some examples - please supply a workbook.
Regards
Phil
Hello Phil,
We're still using Excel 2010 here, but they are gradually rolling out Office 365.
I was looking at the following https://www.myonlinetraininghub.com/excel-forum/excel/extract-values-from-non-blank-cells-in-a-row , with a view to finding which columns of a pivot table contained data, the result being the column header - there's probably an easier way to do this (LOOKUP?).
I was getting the result #DIV/0!, but I managed to tweak the formula parrot-fashion to get the right result.
However, I'm now wondering how to deal with the situation where there are two values in the same row.
Sample file attached.
Hi Tim,
So in that sample it looks like data from a pivot table copy/pasted + some extra columns?
To check if a column has data you could use for example
=IF(COUNTA(D3:D63)>0,D2,"")
to check Col D.
All the columns in that workbook have data, so maybe I'm not completely understanding what you are trying to do. I'm not sure how 2 values in the same row fits into the picture.
Can you illustrate some examples and the expected outcomes?
Regards
Phil
