Average calculation

[rsoper]

I have a table that calculates on time delivery.

Values are:
Items delivered Items delivered on time OTD
A B =1-(A-B)/B
There are many items in this file.
.
.
I am interested in creating a value for wieghted average, where a row with more items delivered would have a higher impact on the total weighted average than items with less delivered.
.
.
I am struggling on the formula, please help.

 

[tactps]

Post your worksheet so that we can see the data and explain a bit further what you want to achieve.

 

[Tazguy37]

I do see a problem with the formula you've got now: If less than half of the items are delivered on time, you'll get a negative result.

 

[rsoper]

To give more information.

Example
Items Items
Ordered On-time" Formula
1364 1223 89.66%
937 800 85.38%
879 777 88.40%
703 669 95.16%
28 9 32.14%
18 5 27.78%
1 0 0.00%
13 0 0.00%

Total Item 3943 3483 52.32% Average

As I stated the Formula is = 1 - (A-B)/A

This value gives "on-time" %. But look at the numbers when averaging the formula column. 52.32% is the correct average, but it is being skewed by the last 4 items with low %'s. I want to make a value that considers the fact that the first 4 items had many more items thus should have a higher weighting making the weighted average closer to 80% area.
"weighted average".

Thanks for your help.

 

[Aladin Akyurek]

... I want to make a value that considers the fact that the first 4 items had many more items thus should have a higher weighting making the weighted average closer to 80% area.
"weighted average"...

=1-SUMPRODUCT(A2:A9-C2:C9)/SUM(A2:A9)

gives 0.883337560233325 for your intial sample.

 

[Tazguy37]

=1-SUMPRODUCT(A2:A9-C2:C9)/SUM(A2:A9)

gives 0.883337560233325 for your intial sample.

Aladin, you did mean:
=1-SUMPRODUCT(A2:A9-B2:B9)/SUM(A2:A9)

right? That confused the heck out of me for a few minutes, phew!

 

[Barrie Davidson]

See http://www.mrexcel.com/board2/viewtopic.php?t=126049