Posts by Fin Fang Foom

    Re: Top 5 values with corresponding data containing identical values


    Hi Beaczar,


    I'm not sure this what you are looking for, If not maybe someone else can help you out.


    In cell G1 input max value 5


    H2,


    =COUNTIF(B2:B26,">="&LARGE(B2:B26,G1))



    I2, copied down:


    =IF(ROWS(I$2:I2)<=$H$2,LARGE($B$2:$B$26,ROWS(I$2:I2)),"")



    J2, confirmed with CONTROL+SHIFT+ENTER, and copied down:


    =IF(I2<>"",INDEX($A$2:$A$26,SMALL(IF($B$2:$B$26=I2,ROW($B$2:$B$26)-ROW($B$2)+1),COUNTIF($I$2:I2,I2))),"")



    I uploaded a sample file for you.

    Files

    • students.xlsx

      (14.35 kB, downloaded 248 times, last: )

    Re: Lookup Value Across Multiple Worksheets And Retrieve Worksheet Name


    Hi,


    This is the first time in 4 years I've done this.


    Based on your description I uploaded an excel file for you.


    I couldn't get exactly what you want. But the formula below will display multiple result across cells.


    Input formula in cell C2 copy across and down.


    =IFERROR(IF(COLUMNS($C$2:C2)<=$B2,INDEX(XWSLST,SMALL(IF(Col_A=$B2,S+1),COLUMNS($C2:C2))),""),"")



    Now if you want multiple results in one cell then VBA or a 3rd part add-in will do the job. unfortunately I don't have the experience to help you on that.

    Files

    • Fruits.xlsx

      (11.76 kB, downloaded 316 times, last: )

    Re: Display Top/Bottom X In Chart/Graph


    Quote from Domenic

    Fin,


    The whole point of the originating thread was to create a chart without the use of additional cells, columns, formulas, and pivottables. In other words, to create a chart based solely on the source data. Is that your intention?.


    Yes, Some of the users don't know how to use pivottables and it probably be easier for them just entering the kth number in a paticular cell and have the chart adjust accordanly. Is that possible?




    Quote from Domenic


    Otherwise, as Dave as already mentioned, the pivottable is the easiest and most efficient way of generating a chart.


    Yes I like Dave's solution.

    Re: Count/Sum Unique Values/Entries


    Quote from daddylonglegs

    Thanks for the interest Fin Fang Foom


    I believe that both versions will work......unless you know different :question:



    The 1 gives me a 0 in the DCOUNT formula but when I changed it to a zero at the end of the Sumproduct your formula works perfectly.

    Re: Count/Sum Unique Values/Entries


    Quote from daddylonglegs

    Hello Madeleine,


    =SUMPRODUCT((B$2:B2>=date1)*(B$2:B2<=date2)*(H$2:H2=text1)*(C$2:C2=C2))=1



    Shouldn't the formula at the end of the Sumproduct have a zero so the DCOUNT could work properly?

    Re: Chart Text Strings &amp; Sum Up Values And Display It In A Descending Order On Chart


    Quote from Dave Hawley

    I don't know, is it? I would use AutoFilter or a PivotTable to show the top/bottom x.



    Thanks Dave I did not know a pivottable could do that.



    Thanks its perfect!


    P.S. If there is a formula version it's still be interesting to see how its done.

    Re: Create Chart Base On Text Strings And Sum Up Values


    Quote from Domenic

    Try changing the reference for Array2 to the following...


    =SUMIF(DynRange,Array1,OFFSET(DynRange,0,1))


    Hope this helps!



    Thank you so much its perfect.



    Thanks Domenic!

    Hi everyone,



    I came upon this link below about a week ago:


    http://www.ozgrid.com/forum/showthread.php?t=89715



    Domenic provied a solution that it will graph text strings in column A and it will display in the pie chart by using Define name range formulas. My dilema is I would like a second condition in those formulas. I would like the formula look in column A and sum up the values in column B base in column A and display it in the pie chart.

    Re: Dynamic Summary Which Updates As Sheets Added


    Here is a formula version.


    I use a define name range formula to reference all your worksheets.


    wks =MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,255)&T(NOW())



    Then input this formula in cell C4 and copy down.


    =SUMPRODUCT(N(INDIRECT("'"&wks&"'!"&CELL("address",B4))))


    Hope it helps!

    Files

    • Example2.xls

      (24.06 kB, downloaded 279 times, last: )

    Re: Extract Unique Values Between 2 Worksheets


    =SMALL(IF(FREQUENCY('Sheet1:Sheet2'!$A$2:$A$10,ROW($1:$10000)),ROW($1:$10000)),ROWS($B$2:$B2))



    Input formula in Sheet3 of cell B2 and copy down.


    The formula is an-array double click inside the cell and hit,


    Ctrl,Shift,Enter

    Re: Count By Criteria But Only Non-Contiguous Occurences


    Here is a couple of ways.


    This will count every other cell in column A
    =SUMPRODUCT(--(MOD(ROW(A2:A20)-ROW(A2)+1-(A2:A20="S"),2)=0),--(A2:A20<>""))


    If your ranges are scattered then you could try this formula.


    =SUM(COUNTIF(INDIRECT({"B20","D2","G15"}),"S"))



    Hope it helps!

    Re: Consolidate By Time From Different Worksheets `


    I notice the calculation is very slow. Then might want to use this formula below instead, its much more efficient but it will not return in a sorted matter.


    Input formula in cell A2 and copy down.


    =INDEX(Col_D,MATCH(0,IF(Col_D<>0,COUNTIF($A$1:A1,Col_D)),0))


    The formula is an-array must hold down:


    Ctrl,Shift,Enter



    Hope it helps!

    Re: Consolidate By Time From Different Worksheets `


    Quote from Shree

    Actually all I am looking for is I just want all the project nos. to be listed in column A of the worksheet "Project Nos".



    Then try inputing formula in cell A2 and copy down.


    =INDEX(Col_D,MATCH(SMALL(IF(Col_D<>0,IF(MATCH(Col_D,Col_D,0)=S+1,MMULT((Col_D>TRANSPOSE(Col_D))+0,(S+1)^0))),ROWS($A$2:A2)),MMULT((Col_D>TRANSPOSE(Col_D))+0,(S+1)^0),0))


    The formula is an-array must hold down:


    Ctrl,Shift,Enter