Share via

Complexed formula accounting for columns inserted in the middle somewhere

Anonymous
2016-10-20T00:36:43+00:00

For each row, I have numbers in columns x10 through bc10. Right now, in Cell BK10 I have the formula 

=(X10*Y10+Z10*AA10+AB10*AC10+AD10*AE10+AF10*AG10+AH10*AI10+AJ10*AK10+AL10*AM10+AN10*AO10+AP10*AQ10+AR10*AS10+AT10*AU10+AV10*AW10+AX10*AY10+AZ10*BA10+BB10*BC10)*(12/1740)




I would appreciate recommendations that could simply the formula to include automatically updating the formula when someone added in additional columns 

Example of columns added in the middle somewhere.... issue is I have to manually update formula

=(X10*Y10+Z10*AA10+AB10*AC10+AD10*AE10+AF10*AG10+AH10*AI10+AJ10*AK10+AL10*AM10+AN10*AO10+AP10*AQ10+AR10*AS10+AT10*AU10+AV10*AW10+AX10*AY10+AZ10*BA10+BB10*BC10+BD10*BE10)*(12/1740)

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2016-10-20T04:41:45+00:00

Try below formula:

=SUMPRODUCT((MOD(COLUMN(X10:BC10),2)=0)*(X10:BC10)*OFFSET(X10:BC10,,1))*(12/1740)

Presume you will be inserting 2 columns inbetween.

or this, as an array formula (CTRL+SHIFT+ENTER):

=SUM(IF(MOD(COLUMN(X10:BC10),2)=0,(X10:BC10)*OFFSET(X10:BC10,,1)))*(12/1740)

Regards,

Amit Tandon

Was this answer helpful?

0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-10-20T11:46:53+00:00

    You are welcome.

    If this response answers your question then please mark as Answer.

    Regards,

    Amit Tandon

    www.globaliconnect.com

    Was this answer helpful?

    0 comments
  2. Anonymous
    2016-10-20T11:34:51+00:00

    Sir

    Appreciate the assist...worked perfectly

    Was this answer helpful?

    0 comments