Hi again,
As I've now hit another problem I've started a new thread; please delete if you would prefer it stayed with the original.
I now have everything set up to do exactly what I want except this last bit.
All I want is for the overview page to show the top three referrers for each month. At the moment, it's finding the top and bottom 'scorer' but if more than one has the same score I can only see the first alphabetically.
Anyone know a way to do this?
Andrew
See attached for an idea...
I inserted columns prior to each category to extra the largest/smallest 3 Givens/Received, respectively. Those columns can be hidden if you wish.
Then I used formulas that refer to those new columns to extract your required data....
I only completed for Oct 08. You will need to copy all column formulas to the other months changing just the reference sheet names in the formulas.
Note: Formulas to extract the names are confirmed with CTRL+SHIFT+ENTER not just ENTER... then copied down.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Thanks very much.
I am now looking at it trying to understand it all.
If I have troubles with doing that can I come back and ask?
Andrew
Of courseOriginally Posted by Andrew-Mark
Sorry but after putting in more info to test my understanding of your formulae, I seem to have encountered an error (not convinced it isn't one of my own doing).
I can see you are using the number of rows to determine which number of referrals you want F4 to show on the overview sheet. It is now showing the greatest number from any person is 210, which is the total number of referrals in all. Looking at it it seems that all I needed to do was change the formula to
=LARGE('Oct ''08'!$AD$2:$AD$22,ROWS($A$1:$A1))
Or so I thought. Doing this mucked up the other bits and I haven't quite got my head around exactly what process you've followed to get the results.....yet
Ant tips? I would love to be able to work this out for myself as much as possible so it's a slow process, lol.
You are probably right... it should have been AD2:AD22, likewise for the referees, it should be I23:AC23.
So are these parts giving you the largest/smallest values as expected?
If yes, did you confirm the formula to extract the names with the CTRL+SHIFT+ENTER keys?
If still a problem, I would have to see your revised sheet showing the problem.
Okay... I see you attached the sheet as I was writing my message...
anyways, you were definitely correct... you need to change the bottom of the array in F4 and H4 to A22 and in J4 and L4 to AC23
Once you copy those down, the adjacent formulas will adapt...
Yes, sry, I realised I had forgotten to attach it again
I see that in your version same as mine, I have AC being both the top referrer and third placed referrer.
Still working out the forumulae to retrieve the names
Sorry, I hadn't noticed that you must have played with the formula in G4... It was not CSE confirmed...
Just go to that cell and hit F2 key on your keyboard, then hold the CTRL and SHIFT keys down and press ENTER. Now it should say AB1 in G4.. the rest of the cells are still CSE confirmed....as far as I can see.
Thank-you, that has sorted it.
Is there a simple rule as to when to use CSE?
A lot of what you have used is very new to me.
E.g. why do you use the ROWS command in conjunction with LARGE to find the top three number of referrals and not just put in 1, 2 or 3?
Still learning.
Seriously though, thank-you very much and I have managed to copy it to do all the months listed.
Andrew
Have a look here: http://www.cpearson.com/excel/ArrayFormulas.aspx
It describes CSE formulas (Array Formulas) very well...
I use Rows($A$1:$A1) instead of 1, 2, 3 simply so I can copy down the formula and it would self adjust the k value... you could hard code it with the 1, 2, 3 if you wanted to...but if you make changes, you would have to change each formula, instead of just the first and then copy down....
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks