0

I work in oDesk, and I recently found out how the rating is computed. I made a personal worksheet for myself to keep track of my record. However, the formula I use is too long, and I was wondering on how to shorten it.

The formula is:

(sum of total cash earned multiplied by rating, respectively) divided by (sum of total cash earned)

Let's say I have two columns here:

Column A (total cash earned) - Column B (rating)

$4.50 - 4.80

$5.33 - 5.00

$27.00 - 4.00

My Excel formula is:

=((A1*B1)+(A2*B2)+(A3*B3))/(SUM(A1:A3))

Is there a way to shorten it, and possibly accommodate future entries without having to edit the formula?

All help is appreciated. Thanks!

Our Man in Bananas
  • 5,809
  • 21
  • 91
  • 148
Shiro
  • 1

2 Answers2

0

(Sounds like code golf.) This is four characters (only) shorter: 

=SUMPRODUCT(A1:A3,B1:B3)/SUM(A1:A3)

but may be more readily extendible.

Or eight characters shorter but perhaps less easy to read:

=(A1*B1+A2*B2+A3*B3)/SUM(A1:A3)
pnuts
  • 58,317
  • 11
  • 87
  • 139
0

How about this :

  A1       B1       C1         D1      E1
  $4.50    4.80     =A1*B1             =SUM(C:C)/SUM(A:A)
  ...
  ...

or using "pnuts" idea here is an option, which is more easy to maintain that will "accommodate future entries without having to edit the formula":

  A1       B1       C1         
  $4.50    4.80     =SUMPRODUCT(A:A*B:B)/SUM(A:A)  
  ...

Please run a few tests (I have not used SUMPRODUCT much in my life).

El Scripto
  • 576
  • 5
  • 8