Array Formula - Help with Error in the Array

[Aalia]

Hi all,

Need help with fixing an error with a formula in an array.

Each Row in C to be divided by column B
Range is C3 through B7
I used {=C3:C7/B3:B7} to get the total of the result of each row, however the answer i get is zero as some of the rows are resulting in error values. Please help. Attached sample data.

Thanks

[joeu2004]

I think your intent is to perform the same calculation as =SUM(D3:D7) in D8. That is, the sum of the row-by-row ratios.

Array-enter (press ctrl+shift+Enter instead of just Enter) the following:

=SUM(IF(B3:B7>0, C3:C7/B3:B7))

[Aalia]

Thanks joeu2004, this works!! as I am just getting familiar with using arrays, it has motivated me to explore using it often.

[joeu2004]

Actually, I discourage the use of array-entered formulas, if/when there is a reasonable alternative.

The problem with array-entered formulas is: it is not uncommon to unconsiously press just Enter instead ctrl+shift+Enter. Often, that results in a #VALUE error; although that might be mysterious at first, at least it alerts us to our mistake. However, sometimes it results in a bogus result, due to "dumb luck"; so we overlook our mistake unless/until some other calculation seems incorrect. (Read about "implicit intersection".)

[Aalia]

Thanks for the feedback, appreciate it.