I am using Excel 2007, I have seen that the AGGREGATE function that I want To regret, it does not exist in 2007.
I had the following formulas to get the top 10 products in the dataset.
= INDEX ($ A $ 5: $ W $ 24389, match (large ($ V $ 5: $ V $ 24389, BO1), $ V $ 5: $ V $ 24389,0), 1)
BO1: List of bks 1 to 10, (to give the number of results for that k ).
It works well, as long as the list does not put subtypes in it then it is then "" (well, 0 ) As it gets in the form of one of the MAX value sub-formulas.
How do I get to the top 10, which ignores SUBTOTAL s?
It was my thinking ... and this - it leaves the cells nicely, but returns them one by one on the list, I think I want to return this array to the formula Required, but I can not understand it
If the column A value (products) are always empty on the "sub-totals" rows, then this version Must be sufficient to use if you do not have a duplicate in any column V:
= INDEX ($ A $ 5: $ A $ 24,389, Match (Large (if $ A $ 5: $ A $ 24,389 with & lt; & gt; "", $ V $ 5: $ V $ 24,389), BO1), $ V $ 5: $ V $ 24389,0 )) Confirmed with
CTRL + SHIFT + ENTER
.... but assuming column V (or between prices or values and values) Duplicate values can be in between Betlot), use this formula in the first cell ( BO2 ?)
= INDEX ($ A $ 5: $ A $ 2438 9, match (Large (if ($ A $ 5: $ A $ 2438 9; "", $ V $ 5: $ V $ 2438 9), BO1), IF ($ A $ 5 : $ A $ 24389 & lt; & gt; "", $ V $ 5: $ V $ 2438 9), 0))
...... and then < Code> BP2 Use this version:
= INDEX ($ A $ 5: $ A $ 24389, match (large (if ($ A $ 5: $ A $ 2438 9 & lt; & Gt; ", $ V $ 5: $ V $ 24,389), BP1), IF (ISNA (match ($ A $ 5: $ A $ 24,389, $ BO2: BO2,0)), IF ($ A $ 5: $ A $ 24,389 with & lt; & gt; with " CTRL + SHIFT +
< / Code>
and copied to
No comments:
Post a Comment