Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel - calculation for 50 cells w/o VBA coding it

Posted on 2013-01-08
4
Medium Priority
?
209 Views
Last Modified: 2013-01-10
Hello Experts,

I wanted to see if there is a way to write an Excel Formula that will calculate a large set of numbers without having to type it all out.  I am trying to avoid using VBA coding, if possible.  But will resort to it if needed.

Range("C57") = sum("D6*$L$6)+(D7*$L$7) this continues to row 50.  It is always column D * L.
Is there a way to write this as a function instead of typing 45 steps?

Otherwise I need to set it as a change cell event for range (D6:I50).

If I can be helped with both of these scenerios - it would be great.

Thank you,
Michael
0
Comment
Question by:mike637
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38757101
If you mean that C57 should have:

=SUMPRODUCT(D6:D7,L6:L7)

and C100 should have:

=SUMPRODUCT(D49:D50,L49:L50)

then simply enter that first formula into C57, and copy it down through C100.

If you meant to fix the references to Col L, then use this instead:

=SUMPRODUCT(D6:D7,$L$6:$L$7)
0
 

Author Comment

by:mike637
ID: 38757530
Hi Matt,

Not exactly,

There is a column of numbers in column D (starting at row 6)  I need to mulitpy D6 by L6, and D7*L7 and, D8*L8 and D9*L9 and so on until I reach row D55*L55. All of this needs to be contained in Cell "D57" to calc this whole process.

I am attaching a sheet with an example.

I have to use this formula in 35 other instances in the same sheet with different different rows and columns.  But if I can get the first example down to a simplified formula, then I can use that in the other instances.

I wanted to see if there was a way to write it so I did not have to type out all 50 expressions.

Thanks,
Michael
Book1.xlsx
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 38757572
OK, so use this in D57:

=SUMPRODUCT(D6:D55,$L$6:$L$55)

Then copy that across through J57 if you like.
0
 

Author Closing Comment

by:mike637
ID: 38763872
Hi Matthewspatrick:

Thank you - worked perfectly!

Michael
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question