# 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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### 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!

#### Barrie Davidson

##### MrExcel MVP

Replies
6
Views
63
Replies
1
Views
109
Replies
0
Views
204
Replies
1
Views
218
Replies
1
Views
487

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,842
Messages
5,766,738
Members
425,376
Latest member

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

### Which adblocker are you using?

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