Posts by mohammedismail

    Hi Guys,


    Please could you help me out with a formula which will help me identify how many Rows belong to Top 10 % Group Or top 20% Group. For Example in the below case there are 20 rows and Top 10% are the first 2 rows.
    [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 402"]Row Labels[/TD]
    [TD="width: 92"]Amount[/TD]

    [/tr]


    [tr]


    [td]

    ABC

    [/td]


    [td]

    63,855,641.60

    [/td]


    [/tr]


    [tr]


    [td]

    DEF

    [/td]


    [td]

    36,100,068.26

    [/td]


    [/tr]


    [tr]


    [td]

    SGH

    [/td]


    [td]

    23,324,389.74

    [/td]


    [/tr]


    [tr]


    [td]

    ABC

    [/td]


    [td]

    20,534,610.54

    [/td]


    [/tr]


    [tr]


    [td]

    DEF

    [/td]


    [td]

    14,017,828.67

    [/td]


    [/tr]


    [tr]


    [td]

    SGH

    [/td]


    [td]

    11,669,652.96

    [/td]


    [/tr]


    [tr]


    [td]

    ABC

    [/td]


    [td]

    11,452,970.69

    [/td]


    [/tr]


    [tr]


    [td]

    DEF

    [/td]


    [td]

    10,868,123.63

    [/td]


    [/tr]


    [tr]


    [td]

    SGH

    [/td]


    [td]

    10,072,343.93

    [/td]


    [/tr]


    [tr]


    [td]

    ABC

    [/td]


    [td]

    9,995,804.15

    [/td]


    [/tr]


    [tr]


    [td]

    DEF

    [/td]


    [td]

    9,812,192.01

    [/td]


    [/tr]


    [tr]


    [td]

    SGH

    [/td]


    [td]

    9,680,726.24

    [/td]


    [/tr]


    [tr]


    [td]

    ABC

    [/td]


    [td]

    9,632,613.12

    [/td]


    [/tr]


    [tr]


    [td]

    DEF

    [/td]


    [td]

    8,159,122.82

    [/td]


    [/tr]


    [tr]


    [td]

    SGH

    [/td]


    [td]

    6,429,932.46

    [/td]


    [/tr]


    [tr]


    [td]

    ABC

    [/td]


    [td]

    6,065,187.29

    [/td]


    [/tr]


    [tr]


    [td]

    DEF

    [/td]


    [td]

    6,043,460.64

    [/td]


    [/tr]


    [tr]


    [td]

    SGH

    [/td]


    [td]

    5,887,114.29

    [/td]


    [/tr]


    [tr]


    [td]

    ABC

    [/td]


    [td]

    5,523,253.55

    [/td]


    [/tr]


    [tr]


    [td]

    DEF

    [/td]


    [td]

    5,231,107.87

    [/td]


    [/tr]


    [/TABLE]

    Hey Guys


    Can you please help me count unique values by Category. But the catch is if the product is already there in Batch1 it should not be counted in the Batch2. It should count only the unique values in Batch 2 which are not part of Batch 1 and 3. Same with Batch3.. the products must not repeat in Batch 1 or 2.


    Please find the sample sheet attached and also the desired output which I got using the pivot table.

    Files

    • Book2.xlsx

      (13.3 kB, downloaded 98 times, last: )

    Re: problem with AVERAGE function


    Try this formula, however it also takes the Decimals into consideration!


    [BFN]=AVERAGE(IF(A$1:A$37="","",VALUE(IFERROR(LEFT(A$1:A$37,FIND("#",A$1:A$37,1)-1),""))))[/BFN]


    It's an array formula so CTRL+SHIFT+ENTER

    Re: Macro that counts all characters in Worksheet


    Check out this, may be it gives more information than you need !



    Source

    Re: Depreciation


    Here you go !


    [BFN]=SUMPRODUCT($D$9:$D$16*$E$3/365*$C$9:$C$16)[/BFN]


    The Formula in your sheet need to have an additional bracket, currently it is incorrect!


    Your formula needs to be


    [BFN]=(($E$3*D9)/365)*C9[/BFN]

    Re: Sort Multiple Sheets with Macro Loop


    Here is the code.let me know if it helps!


    Code
    1. Sub M()
    2. For Each ws In ActiveWorkbook.Worksheets
    3. ws.Activate
    4. ws.Range("B4", Range("B" & Rows.Count).End(xlUp).Address).Sort Key1:=[b4], _
    5. Order1:=xlDescending, Header:=xlYes
    6. Next ws
    7. End Sub

    Re: Sum the data after filtering


    Please try and post the sample excel sheet for the ones who are willing to help. People do not want to spend time to create an excel sheet for someone else's work.. Hope you understand where I'm coming from !

    Re: Extract specific text based on codition from a string in a cell.


    Try the below Formulae..Let me know if that helps!


    In D2


    [BFN]=IFERROR(IF(IFERROR(SEARCH("Curr:",A2,1),0)>0,LEFT(A2,IFERROR(SEARCH("-",A2,1)-1,0)),IFERROR(LEFT(A2,IFERROR(SEARCH("Non-Compliant",A2,1),0)-1),LEFT(A2,IFERROR(SEARCH("Compliant",A2,1)-1,0)))),"ERROR")[/BFN]


    In E2


    [BFN]=IFERROR(IF(IFERROR(SEARCH("curr:",A2,1)>0,0),IFERROR(MID(A2,SEARCH("/",A2,1)+2,SEARCH(")",A2,1)-2-SEARCH("/",A2,1)),0),IF(RIGHT(A2,13)="Non-Compliant","Non-Complaint","Compliant")),"ERROR")[/BFN]


    Re: Cannot get PowerView on the ribbon


    Quote

    According to your description, you have already have the Power View add-in loaded, but your still can't set Power View tab in the ribbon. Right?
    In Excel, the Power View tab appears only when the current worksheets contain Power View reports. Please go to INSERT->Power View to insert a Power View report. If the button is gray, you may need to repair or reinstall your excel.


    Source

    Re: Paste the data based on condition


    Try the below code ! Next time please attach a sample workbook so that it will be easy for people who are willing to help!