Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

finding average of last 5 payments

Posted on 2014-11-11
4
Medium Priority
?
96 Views
Last Modified: 2014-11-15
hi Folks
Got this issue. A customer wants to find a way to calculate the average of the last 5 payments made to employees. I've attached a file with the required answer...of course people will finish at different times, they may not get paid every week. So essentially it means find the last 5 cells with entries in them and get the average..
finding-average-of-last-5-payments.xlsx
0
Comment
Question by:agwalsh
4 Comments
 
LVL 18

Assisted Solution

by:krishnakrkc
krishnakrkc earned 800 total points
ID: 40434656
Hi

There may be better ways...In M3 and copied down

=AVERAGE(INDEX(B3:L3,,LARGE(IF(B3:L3<>"",COLUMN(B3:L3)-COLUMN(B3)+1),5)):L3)

It's an array formula. Confirmed with CTRL+SHIFT+ENTER

Kris
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 800 total points
ID: 40434657
In M3:
=AVERAGE((INDEX(B3:L3,LARGE(IF(B3:L3<>"",COLUMN(B3:L3)-1),MIN(5,COUNT(B3:L3)))):L3))
entered with Ctrl+Shift+Enter, then fill down.

(the MIN part is there in case there are less than 5 numbers available)
0
 
LVL 2

Accepted Solution

by:
Pratik Makwana earned 400 total points
ID: 40434679
attached is your excel with result...
finding-average-of-last-5-payments-1-res
0
 

Author Closing Comment

by:agwalsh
ID: 40444228
@Rory Archibold and @ krishnakrkc. Thank you both for an elegant, wonderful formula. A Thing of Beauty indeed. I gave Rory the Best Solution because he wove the Min function into it. @Pratik. I gave you a lower score because I simply couldn't open the file you sent me.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question