LinkBack URL
About LinkBacks
I'm trying to calculate the median value of a subset of values based on a conditional operator. The formula I'm attempting utilizes an array but I'm not sure what I'm doing wrong.
I'm trying to calculate the median value of a subset of values based on a conditional operator. The formula I'm attempting utilizes an array but I'm not sure what I'm doing wrong.
E3=aggregate(16,2,a2:a100/(a2:a100<>"")/(a2:a100<=d3),0.5)
E3=AGGREGATE(16,2,A:A/(A:A<>"")/(A:A<=D3),0.5)
empty cell are zero in Array
Last edited by CARACALLA; 10-25-2021 at 03:19 PM.
its because you are using the whole column reference - change to A:A to the actual range A2:A15
Not 100% sure why, but i have seen these issues in quite a few array formulas and know to referer to the actual range
i'm sure someone may explain why with more knowledge then myself
=MEDIAN(IF(A2:A15<=D3,A2:A15))
as an array
{=MEDIAN(IF(A2:A15<=D3,A2:A15))}
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Thanks Wayne, you solved my problem. It is odd that I couldn't reference the entire column but I think I learn something new about arrays every time I use them.
Thank you for solving my issue and have a great day!!
Formula post 2 works ?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks