Hi Gurus
I am trying to come up with a formula which will allow me to get Column G in my attached spreadsheet.
Column F formula gives me the the top 5 average and it corresponds to sites 11 - site 15 (Column A). I want column G to give me the Last year figures for the same sites column F picked out (sites 11-15) and give me the average for them. Is there a neat way to do this without using any helper columns?
Try
G6Formula:
Please Login or Register to view this content.
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
g6=SUMPRODUCT(LARGE((B$6:B$100)*(C$6:C$100>=LARGE(C$6:C$100,5)),{1,2,3,4,5}))/5
I am unable post other formula here see the attached file
Last edited by samba_ravi; 03-28-2018 at 03:50 AM.
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Or try:
=AVERAGE(IF(C6:C29>=LARGE(C6:C29,5),B6:B29))
Enter with Ctrl+Shift+Enter.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks