in the attached the formula in E5 is to return the average of the smallest 3 numbers in D5:D10. reference in g5 returnsthe correct value of {1,2,3} but when used in the formula of Average(small($D5:$d10,g5)) the contents of g5 is returned as "{1,2,3}". this causes a value error.
Maybe
Formula:
Please Login or Register to view this content.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
The count to average is dynamic. See the new Attachment.
Hi,
If you'll accept a single number e.g. 4 instead of {1,2,3,4} and an array formula then the following array formula (entered with Ctrl Shift Enter), will do what you want
Formula:Please Login or Register to view this content.
See attached.
Thanks Richard, Looks like this will solve the problem. I will use it in the work sheet that is much more complex.
Any place to reference the double quotes that return the {1,2,3}. I found the same using vlookup.
Hi,
If your question is about using Indirect to reference the string {1,2,3} then I don't think so.
But hey, when I say something is not possible then it's often the case that I'm shown to be wrong!
I don't think it's possible since I believe the {} symbols can only be manually added within a function - like say a Lookup. I don't believe an Indirect can interpret them.
Withdrawn by FR. Referenced wrong cell in INDIRECT.
Last edited by FlameRetired; 12-06-2017 at 02:36 PM.
Dave
I'll try again. Piggy-backing on Richard's formula this is convoluted. Array entered.Formula:Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks