I am trying to write a formula that will give me the bottom 5 performing items within a given dept. Each department has a different number of items. I would like for it to be dynamic based on whatever dept number I key in. I've attached an example. Sorry if I am not explaining this correctly!
Here is with an automated macro. You can enter the number of items to be reviewed
Here is an ARRAY formula you can use, enter it in a cell, then fill it down for 5 total rows and it will give you the 5 worst performers:
ARRAY formulas are entered by pressing Ctrl + Shift + Enter, instead of just Enter!
Formula:
Please Login or Register to view this content.
- Moo
Here is your original sheet, with my formula inserted to show you the setup...
(See attached file)
- Moo
Moo ~
Thanks so much! This is exactly what I needed. For the sake of learning, I can follow along with your formula but wasn't sure why you added the (ROW A1) part of it. Can you explain?
Thanks again! Super helpful.
Glad to help...
ROWS($A$1:$A1) <--- This is the 'k' value for the SMALL function. ROWS($A$1:$A1) = 1. So the first cell you enter the formula in, it will pull the smallest value. When you fill that formula down, the range will change to ROWS($A$1:$A2), then ROWS($A$1:$A3), and so on, which changes the 'k' value to 2nd lowest, 3rd lowest, etc.
You could code it by hand and just use 1 in the original formula, but then you would have to change that 1 to a 2, then a 3, then a 4, etc...
For short lists that would be ok, but for longer lists, and ease of use, using the ROWS modification can save time.
Hope that makes sense. =)
- Moo
Seriously, thank you so much! This was so helpful for me and really saved me a lot of time. My first time posting
You're quite welcome. If you feel so inclined, feel free to click that little star at the lower left of one of my posts to add to my 'reputation'. It is always appreciated by the helpful members here. =)
- Moo
Moo~
I have another issue. This formula works great with Excel 07; however, I have some collegues that for business reasons utilize Excel 03. They are unable to see the data. Is there something I need to do to get this to work for both versions?
Thanks!
If they are not seeing data, what are they seeing? If they are getting #N/A, then they did not enter the formula as an ARRAY formula by pressing Ctrl + Shift + Enter.
Otherwie, there is nothing in that formula that isn't compatible with Excel 2003. (As far as I know!)
- Moo
It's an excel document that I shared with other folks. Anyone with 2007 can see the formula, however, the folks with 2003 get the #NA when openign the document. Does that mean they would have to press Ctrl + Shift + Enter for every person who opens it? If I've created the document, saved it, and emailed it.
works fine for me in excel 97
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
I can see it on my 2003?? Do your colleagues have the conversion package (available free of charge from the MS website)??
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
you should not need any conversion package. As Moo said, there is nothing inthat formula that 2003 would not be able to use (like iferror or sumifS() for instance)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
It's giving the error message #NUM, not #NA - my apologies. I've even saved the file as Excel 97-03 Workbook and emailed it to them. When they open, the #NUM error comes up.
attach a sample workbook that gives the same problem then we can try opening it on various versions of excel
in the meantime here is another non array method using a helper column
Last edited by martindwilson; 07-09-2013 at 06:30 AM.
See attached and let know if you are able to produce the same error. Thanks!!!!!
Last edited by coda1395; 07-09-2013 at 09:28 AM. Reason: Attachment change
what's attached?
Your (coda1395's) attached sample works fine for me on 2004 Mac and 2011 Mac. Don't have access to my home computer right now for PC.
- Moo
Last edited by Moo the Dog; 07-09-2013 at 12:07 PM.
I think I may have solved the problem.... Excel will not allow the use of entire columns in array formulas... and since your named ranges refer to entire columns it is causing the error.
For instance, for the named range 'catid', use A1:A65535, instead of A:A
http://support.microsoft.com/kb/166342
- Moo
Last edited by Moo the Dog; 07-09-2013 at 12:08 PM. Reason: added link to microsoft support detailing #NUM error with arrays and full columns
yep now its been attached i can confirm i see #num! errors in 97 ,and i know you cant use whole ranges in excel 97, but perhaps if you had told us you 'were using named ranges from the beginning!
My apologies Martindwilson but I'm on here asking for help, because I DON'T know the reason for the error. I was unaware that using entire columns was not a viable option, especially considering it was working for me just fine on my version.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks