Hi! all,
How can I get " Values of Column A which are less than value in A1 and corresponding to top 3 in Column B".
I tried it with =SUMPRODUCT((INDEX($A$5:$C$21,0,3)=LARGE(INDEX($A$5:$C$21,0,3),1)),INDEX($A$5:$C$21,0,1))
but that doesn’t consider greater than or less than the value in A1, but that is what I need to get.
The calc file is attached.
Can someone please help. Thanks.
@johnsun, as suggested by you, I uploaded file Top3_1.ods with updated data & explanation contained within it, as to what I need. thank you.
As far as I understand the new description, you are having difficulties with setting a complex condition like “the values in column A are less (or more) than the value of cell C1 (SplitBy) AND the values in the column in which we are looking for TOP is greater than zero”. This “AND” condition is written very simply - the usual multiplication of conditions. That is, for Result Column1, the condition must be written as (SourceA<SplitBy)*(SourceB>0). The formula will not change very much =GETTOPVALUES(SourceA;(SourceA <SplitBy)*(SourceB> 0);SourceB;TopCount). Likewise for Result Column2 the formula will be very similar =GETTOPVALUES(SourceA;(SourceA <SplitBy)*(SourceB> 0);SourceB;TopCount).
To get the formulas for the Result Column3 and Result Column4 columns, simply replace all references to column B with the corresponding ranges from column C - =GETTOPVALUES(SourceA;(SourceA<SplitBy)*(C5:C50>0);C5:C50;TopCount) or =GETTOPVALUES(SourceA;(SourceA<SplitBy)*(OFFSET(SourceA;0;2)>0);OFFSET(SourceA;0;2);TopCount) or =GETTOPVALUES(SourceA;(SourceA<SplitBy)*(OFFSET(SourceB;0;1)>0);OFFSET(SourceB;0;1);TopCount) or give a name for the desired column C range and use this name in the formula =GETTOPVALUES(SourceA;(SourceA>SplitBy)*(SourceC>0);SourceC;TopCount)
@johnsun, Till now, I was using advanced filter + helper worksheets + pivot table on the raw data file (with roughly 15C x 200000R). Now, Pivot table + this macro / function will help me do it, hopefully with no scope for errors. Thank you once again. May God bless you.
How about solving the problem “head-on”, just as written?
First of all, what should be the array in which the three largest values will be searched for? You can form it by the formula
{=IF($A$5:$A$21<$A$1;$B$5:$B$21;"")}
It will return array like as {14400;18000;39600;25200;180000;39600;244800;10800;;;;;;;;;}
Now you can get the largest values of this array
{=LARGE(IF($A$5:$A$21<$A$1;$B$5:$B$21;"");<number_1_2_or_3>)}
Now get values which you search:
=IFERROR(INDEX($A$5:$A$21;MATCH(SUMPRODUCT(LARGE(IF($A$5:$A$21<$A$1;$B$5:$B$21;"");1));IF($A$5:$A$21<$A$1;$B$5:$B$21;"");0));"")
=IFERROR(INDEX($A$5:$A$21;MATCH(SUMPRODUCT(LARGE(IF($A$5:$A$21<$A$1;$B$5:$B$21;"");2));IF($A$5:$A$21<$A$1;$B$5:$B$21;"");0));"")
=IFERROR(INDEX($A$5:$A$21;MATCH(SUMPRODUCT(LARGE(IF($A$5:$A$21<$A$1;$B$5:$B$21;"");3));IF($A$5:$A$21<$A$1;$B$5:$B$21;"");0));"")
and get result
195
190
185
Update I cannot prevent you from constructing and using formulas like as
{=LARGE(IFERROR(IF(MATCH($B$5:$B$2100; IF(RANK(IF($A$5:$A$2100<$A$1; $B$5:$B$2100; ""); IF($A$5:$A$2100<$A$1; $B$5:$B$2100; ""); 0)<=3; IF($A$5:$A$2100<$A$1; $B$5:$B$2100; ""); ""); 0); $A$5:$A$2100; ""); ""); ROW(INDIRECT("1:"&(COUNT(IFERROR(IF(MATCH($B$5:$B$2100; IF(RANK(IF($A$5:$A$2100<$A$1; $B$5:$B$2100; ""); IF($A$5:$A$2100<$A$1; $B$5:$B$2100; ""); 0)<=3; IF($A$5:$A$2100<$A$1; $B$5:$B$2100; ""); ""); 0); $A$5:$A$2100; ""); ""))))))}
This array formula will calculate the values for Result Column1
I can only ask you not to. There are many reasons why large and complex structures should be avoided - they are easy to break and difficult to repair, they are difficult to understand (not only users, even the author after a while will not be able to say what exactly and how is calculated here), they are difficult for processing - Calc spends a lot of resources to calculate the values of several cells.
Just for example - try to find and change several characters in this line to get the formula for Result Column2
So I ask you - please, please don’t do this!
The fact that you’ve read somewhere that macros expose your computer to viruses is true. But this is not the whole truth. It’s like reading Mushrooms Are Poisonous and giving up the prized truffle, matsutake, morel and champignon mushroom forever!
A macro obtained from a reliable source (or written yourself) is no more dangerous than any other program already installed on your computer. At the same time, a good macro makes the computer much easier to use. Look here:
The GETTOPVALUES that appears in the formula bar in this video is, on the one hand, a macro, and on the other hand, nothing more than another function, of which there are many in Calc. Just try it - Top_macro.ods
The 3rd result is a tie between two numbers in Column A, 192.5 and 185 with same corresponding value in B5:B21 namely 39600. The proposed formula ignored 192.5 altogether and shows 185 for both 3rd and 4th largest value.
Actually, the data I have is much more and I need to scale up this formula for upto Top 10.
thank you
Oh, why didn’t you immediately mention this in the text of the question? Simplified the task - we got a simplified solution. By the way, which of the values of A would you choose in this case - 185 (because it occurs earlier) or 192.5 (because it is larger)?
@johnsun, From the data file I have, I just made a simplified sample file with fewer rows which is attached with the original question. I myself didn’t notice that earlier. sorry for that. Fortunately for me, this scenario too presented itself, to seek a solution from kind hearted people like you.
Now, to your question, which value I would choose in case of a tie, the purpose for which I am seeking this requires both values to be considered one after another… in either order. 192.5, 185 or 185, 192.5. Though not in the sample file, sometimes there are more than two in ColumnA in a tie.
thank you.
In other words, the result of the formula will not return the same number of values that match the condition for column B? Difficult task. Perhaps this can only be done with formulas. But it seems to me that a macro (a user-defined function) would be more appropriate here. Try this solution - Top3_macro.ods
@johnsun, I do not know how to make / use macros. That is because, I read somewhere that macros & enabling java exposes pc to viruses. So, I don’t know how I get a solution to the present problem just by using formulae.
thank you.
@Sai I understand your concerns. Trust me, I would not risk my reputation by sending viruses. I completed my answer, hope it helps
@johnsun, the GETTOPVALUES function / macro you gave works. As someone new to macros, I don’t know how to fine tune it for my purpose. If it were a formula, I could customize it myself. I fear, I might even spoil / break it in any such attempt.
Though not in this sample file, there are some rows in ColumnB which have zero values, Their corresponding values in ColumnA must be ignored in Result columns 1 and 2. How to do that.
Similarly, I also need result columns 3 and 4 which must display values of columnA corresponding to non-zero top values in ColumnC with the same pre-condition < A1 and > A1 respectively. For this also, all the same conditions for the Result columns as for result columns 1 and 2.
sorry if I am bothering you too much. thank you.
No, don’t worry, you don’t bother me. Most likely, you will not need to change anything in the macro. Treat it like a normal function with the weird name GETTOPVALUES - just pick a set of parameters that suits your data and your needs. I would do it for you, but I poorly understood the description of real data that you gave in the last comment - which cells besides the column A do you still need to display? C and D? K and M? Perhaps, if you edit your question and add Top3_1.ods, which will show the real state of affairs, then it will be easier to do.
@JohnSUN, as suggested by you, I uploaded file Top3_1.ods with updated data & explanation contained within it, as to what I need. thank you.