Average calculation

rsoper

New Member
Joined
Mar 31, 2002
Messages
23
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.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
Post your worksheet so that we can see the data and explain a bit further what you want to achieve.
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
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
Joined
Mar 31, 2002
Messages
23
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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
Joined
May 28, 2004
Messages
4,237
Aladin Akyurek said:
=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!
 

Forum statistics

Threads
1,147,508
Messages
5,741,568
Members
423,667
Latest member
Kai_357

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top