Solved Is there a way to simplify this excel formula?

Various / CUSTOM BUILT
October 21, 2016 at 04:37:09
Specs: Windows 7, Intel i7 4770k, 32GB 1600MHz RAM
I'm trying to process a couple of values in Excel, and came up with this formula, which works:
=$A3*B3+$A4*B4+$A5*B5+$A6*B6+$A7*B7+$A8*B8+$A9*B9+$A10*B10+$A11*B11

However it's very long and I feel there is a better way to perform such a simple operation. (basically multiplying two values on each column, then moving down one row and performing the same operation until row 11, adding up the products of each calculation.)

Don't worry if plan A fails, there are 25 more letters in the alphabet ;)

message edited by RainBawZ


See More: Is there a way to simplify this excel formula?

Report •

#1
October 21, 2016 at 04:59:16
Hi RainBawz

Are you able to use column C to hold the values of A*B?

In C3 you will then have '=A3*B3'

if you were able to do that then all you would need to do is add all of column C from C3 to C11. Or even have it sum it for you at the bottom.

Other than that all I can offer is a VBA solution not sure this is what you want but its always good to have options.

Sub DoCalc()
    
    Dim iMult As Long
    Dim iSum As Long
    
    iMult = 0
    iSum = 0
        
    For Each bcell In Range("A3:A11")
        iMult = bcell * bcell.Offset(0, 1)
        iSum = iSum + iMult
    Next bcell
    
    Debug.Print iSum
    
End Sub


Report •

#2
October 21, 2016 at 12:38:35
✔ Best Answer
How about this formula...


=SUMPRODUCT(A3:A11*B3:B11)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
October 22, 2016 at 03:09:51
Orrrr Derbys suggestion lol

Report •
Related Solutions


Ask Question