help with div#0 problem with sumproduct

[jram]

Hi

the following formula reinflates a set of values and then applies another percentage to them. However, there are some rows without percentages and therefore it is dividing by 0.

I thought I could simply add a check if column HF is 0,the formula would ignore them, but this appears not to be the case - can you help please?

sumproduct(--((HF112:offset(HF112,G108,0))>0),F112:offset(F112,G108,0)/HF112:offset(HF112,G108,0),HG112:offset(HG112,G108,0))

If possible I would like to avoid using an array as I need to copy and paste this quite a few times and the cell references will not change.

Many thanks for your help, this will save me having to put in another load of tables.

Kind regards
James

[DonkeyOte]

James,

I'd make the point that although a SUMPRODUCT is not a CSE Array (CTRL + SHIFT + ENTER) it is processed in the same way - SUMPRODUCT is not deemed to be significantly more efficient than a traditional array - it's advantage over a CSE is that it does not require CSE (!) - ie more robust in the fact of end user alteration.

With use of OFFSET you're making the SUMPRODUCT Volatile (see link in sig.) - this means every time XL recalculates all of your SUMPRODUCTs will recalculate regardless of whether or not they were impacted directly by the change made that caused the recalc... normally XL employs smart recalculation to only calculate cells directly impacted by whatever change was made - with Volatiles this does not apply and all are recalculated regardless... this brings calculation overheads obviously which are compounded when either there are large numbers of volatile functions (in big models) and/or the volatile functions are poor performers anyway (ie Arrays and this incl. Sumproduct).... so where possible avoid using Volatile Arrays... you could possibly use INDEX rather than OFFSET.

All that being said you may not have an option...what is the formula in HF - can you alter this to default to 1 or does that cause issues elsewhere ?

Assuming you can't revise HF then a CSE array equivalent which would resolve your issue (though I appreciate you would prefer to avoid):

Please Login or Register  to view this content.

[jram]

Hi

Thanks, I really appreciate your answer. I'm relieved it works (although had to change the isnumber to check if it was >0, as some of the formulas return a zero) as I'm at the edge of my excel knowledge and beyond. I'm really appreciate the support, excelforum is so great! cheers.