Average calculation

rsoper

New Member
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.
.
.

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

tactps

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

Tazguy37

MrExcel MVP
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

New Member

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".

MrExcel MVP
rsoper said:
... 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

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

gives 0.883337560233325 for your intial sample.

=1-SUMPRODUCT(A2:A9-B2:B9)/SUM(A2:A9)

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

Replies
0
Views
139
Replies
1
Views
232
Replies
2
Views
153
Replies
1
Views
261
Replies
2
Views
225

1,181,124
Messages
5,928,246
Members
436,594
Latest member
asifm0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back