Averaging N-Largest Or Smallest Numbers in an Array Containing Blanks

Averaging number is easy in excel, we can use AVERAGE() and AVERAGEA() to achieve the task but it becomes tricky if you want to calculate it for Array with Blank (that are supposed to return zeros) & even more if you want to do it for first N Largest (or Smallest) numbers. In this post i will be discussing both the First ten "Largest & Smallest" Number only by using below formula:

For Largest:

=SUM(LOOKUP(LARGE(IF(ISBLANK(B1:B23)=FALSE,ROW(1:23)),ROW(1:10)),ROW(1:23),IF(ISBLANK(B1:B23)=FALSE,B1:B23)))/10

The Formula is an array formula (i.e. need to be execuated with Ctrl+Shift+Enter), the data is Organized in Cells A1:B23. The conditions that formula should obey are that:

1. The formula should average the "Latest" ten values in the Column B.
2. That the formula when averaging should not include any empty/blank cells, if so it should move to the next cell in the Array.
 
Downloading this sheet will facilitate the working on your side.

The ISBLANK() is actually responsible for Evaluating that the Cells in Range B1:B23 are not blank, if NOT, they will return a Series of Numbers generated from 01 to 23 being produced by ROW(1:23), the same is used by LARGE() and the Values are evaluated for being amongst top ten or not thus the situation becomes:

=LARGE({1,2,3,4,5,6,False,....,22,23},{1,2,3,...,9,10})

Here LARGE() simply ignore FALSE, and return following as the result: {23,22,21,…,16,12,11}. The Second Part of the LOOKUP() execute to give an array of values that will be used as the Lookup_Range with non-blank cells viz {1,2,3,4,5,6,False,....,22,23}. Thus the situation become like this:

=LOOKUP({23,22,21,…,16,12,11},{23,22,21,…,3,2,1},{1,2,1,2,...2,3,4})

..When LOOKUP() is evaluated the Result is an array of numbers that are the largest then, all being non-blanks and looks like this:

=SUM({4,3,2,1,1,3,2,1,4,3})/10

The value thus divided by 10 gives the Average that was required.

For Smallest:

We will be using following formula:

=SUM(LOOKUP(SMALL(IF(ISBLANK(B2:B24)=FALSE,ROW(1:23)),ROW(1:10)),ROW(1:23),IF(ISBLANK(B2:B24)=FALSE,B2:B24)))/10


The working of the formula is same except that in-place of LARGE() we have used SMALL(), the ISBLANK() function will check for the Non-Blanks cells as usual and then SMALL() will create an array of that like: {1,2,...,6,False,False,...,23}. The First Ten Smallest Amongst these will be: {1,2,3,4,5,6,9,10,11,12}.

The rest of the process is same as that for LARGE() part of the article. This array will be matched against the entire range of {1,2,..,23} and the third part of the LOOKUP() will be set to give the corresponding Array, like below:

=SUM({1,2,1,2,3,4,1,2,3,4})/10

when divided by 10 will give you the Average of the Ten Smallest Non-Blank cells in the Array.

Hope you will like this post, please comment to give feedback!!! Thanks.

Progressive Pricing Explained

Progressive Pricing Explained (Using FIFO Approach):

Today I will explain how to use a Progressive Pricing formula to Calculate the Value of certain goods purchased at different price level.

Lets consider this problem: You are a Purchase Manager how purchase Chocolates from different suppliers. In your inventory is present a stock of 15000 Kg of Chocolates that is issued to the factory on FIFO basis. FIFO means that the stock that is purchased first will be issued first. Now you want to calculate how much worth chocolate has been issued to the factory.

A manual practice will required you to multiply the issue with the stock issued for each price level. The process is not hectic if the number of suppliers are small, but what if it goes to multiple dozen?? The process can be simplified if you use spreadsheet for the purpose.

Example: Consider following Table. It contains the Suppliers, Items, Qtty,  Rate & Cum. The first four columns are self explanatory, the last column is the Accumulate sum of the total chocolates present in the stock. This column will facilitate the working of the formula we are going to discuss.

Download this Excel Sheet  and it will facilitate the learning process.


In Cell H1 i have entered the Qtty of the Chocolates issued and in G5 entered the following Formula:

=IF($H$2-E5<1,IF($H$2+C5-SUM($C$5:C5)<1,0,$H$2+C5-SUM($C$5:C5)),C5)
 

 



The first part of the outer IF() check whether the stock under consideration is exhaustive to full fill the demand. In out case, it is 2000 against the qtty issued (14751) so the argument evaluates to False, Since it evaluates to false, the second condition of the IF() is the result that we find in the cell G5.

The Same process continues till we reach Cell G10. In Cell G10 the stock of chocolates is 3000 Kg while we have already issued 12000 Kg from previous suppliers (The 12000 Kg is evident from the Cumulative Qtty  Column). We will be issuing not all 3000 Kg but just 2751 to make it to 14751 Kg.

=IF($H$2-E10<1,IF($H$2+C10-SUM($C$5:C10)<1,0,$H$2+C10-SUM($C$5:C10)),C10)

At this point the first part of the outer IF() will return True when it evaluates $H$2-E10<1, here it will be less then 1, triggering the True portion of the First IF(). The Second IF() will ensure that we get only 2751 out of 3000 this way:

 $H$2+C10-SUM($C$5:C10)<1   =  14751 +  3000 - 15000 = 17751 - 15000 = 2751

Since the statement is False, the second of the the IF() will be evaluated to give you 2751 in G10, had it been True, we would have got Zero in it, just like we get it in Cell G11.

The Formula in Column H5 Simply Multiply the quantity with the respective rate to give it to you the total amount of chocolate issued

I will try to make another post explain a single-formula for the whole process. Hopefully in that approach, we will not be needing this table at all.

Fell free to give feedback on this post.

Welcome 2013: The Cycle Starts...

Hi All,

Here is a mixed thought, this is the first post of the year 2013 and i am empty minded. I planned to write something on using Statistics with Excel 2007 but by now i have not been able to do any thing. Mostly due to my dis-foucus on writing and concentration on learning the VBA. 

I have been handy at writing the codes that suite my requirement but had never been a great programmer. unlike programming i have mastered quite a bit how formulas works, but not always, as they, work so it is quite needed sometime to knew ABC of visual basic. I am on it, and hopefully will learn it very quickly. 

I will try to come up with some useful thing as soon as possible, by then enjoy reading my last few posts :)

Take Care,
Faseeh