Excel formula needed

[bradleyandrewdavis]

I am trying to formulate something specific and encompassing seemingly way more than one function at once. Here's what I want to do:

Step 1: calculate the average of the 10 lowest numbers of the most recent 20.
Step 2: multiply that number by .96, and then I want to truncate that yield to one decimal point.

Further, if there are fewer than 20 numbers in the spreadsheet, I don't want to average the 10 lowest. For example, if there are only 10 numbers, I want to average the lowest 3, then follow step 2 above.

I know this formula, if possible, will be rather lengthy. If it could be explained in words what all the nesting is and what the parts of the formula mean, I would appreciate it!

 

[bklynboy]

Though not an accounting question you could use the following (which I found in an Excel forum):

=ROUND((SUM(SMALL(B1:B23,ROW(INDIRECT("1:10")))))*0.96,1)

Since this is an array formula, you must press Ctrl+Shift+Enter rather than just Enter when you first enter the formula and whenever you edit it later. You can adjust the formula based on number of rows looking to capture or use IF statements to do different alternatives.

B1:B23 is where data is located
ROUND returns the result to one decimal point
SMALL - returns the smallest value in the set
INDIRECT is the set of numbers to return (10 in this case)
ROW is the reference to number of rows
SUM adds up the 10 smallest values