Posts by jag.seven

  • Lookup Based On Criteria and Populate

    Re: Lookup Based On Criteria and Populate


    Hi,


    I tried changing the parameters and the following is what happened:


    1) Nothing changed in the data populated. It is the same data as per the attachment
    2) No stock name is populated in cell B15 downwards.


    For example, I entered more than $5.70 and less than $7.35 for last done and the following stock and the relevant data should have have populated:


    [TABLE="width: 923"]

    [tr]


    [td]

    Name

    [/td]


    [td]

    Last Done

    [/td]


    [td]

    Market Cap (M)

    [/td]


    [td]

    PE

    [/td]


    [td]

    Price/NAV

    [/td]


    [td]

    High, 52 Wks

    [/td]


    [td]

    Low, 52 Wks

    [/td]


    [td]

    Revenue Growth

    [/td]


    [td]

    Current Ratio

    [/td]


    [td]

    Debt to Equity

    [/td]


    [td]

    ROA

    [/td]


    [/tr]


    [tr]


    [td]

    AMMB

    [/td]


    [td]

    $ 5.76

    [/td]


    [td]

    $ 17,361.71

    [/td]


    [TD="align: right"]12.886[/TD]
    [TD="align: right"]1.633[/TD]

    [td]

    $ 7.05

    [/td]


    [td]

    $ 5.30

    [/td]


    [TD="align: right"]10%[/TD]
    [TD="align: right"]1.2[/TD]
    [TD="align: right"]0.4[/TD]
    [TD="align: right"]2%[/TD]

    [/tr]


    [tr]


    [td]

    CIMB

    [/td]


    [td]

    $ 7.27

    [/td]


    [td]

    $ 54,036.27

    [/td]


    [TD="align: right"]14.837[/TD]
    [TD="align: right"]2.156[/TD]

    [td]

    $ 9.01

    [/td]


    [td]

    $ 6.56

    [/td]


    [TD="align: right"]3%[/TD]
    [TD="align: right"]2[/TD]
    [TD="align: right"]0.6[/TD]
    [TD="align: right"]1%[/TD]

    [/tr]


    [tr]


    [td]

    RHBCAP

    [/td]


    [td]

    $ 7.31

    [/td]


    [td]

    $ 16,022.91

    [/td]


    [TD="align: right"]11.076[/TD]
    [TD="align: right"]1.435[/TD]

    [td]

    $ 10.40

    [/td]


    [td]

    $ 6.53

    [/td]


    [TD="align: right"]6%[/TD]
    [TD="align: right"]3.2[/TD]
    [TD="align: right"]0.8[/TD]
    [TD="align: right"]3%[/TD]

    [/tr]


    [/TABLE]



    However, when I clicked on the macro button, nothing happened.

  • Lookup Based On Criteria and Populate

    • [INDENT=5][INDENT]Hi,


      I have a stock data in sheet 1 as follows:



      Last Done
      Market Cap (M)
      PE
      Price/NAV
      High, 52 Wks
      Low, 52 Wks
      Revenue Growth
      Current Ratio
      Debt to Equity
      ROA


      In sheet 2, I have set a parameter table (cell c3 to c12) to enter a range of criteria. Based on these range of criteria entered in parameter table, I would like to populate all relevant stocks in cell b15 downwards and their respective data in cell b15 to l15 downwards in sheet 2 from sheet 1 that satisfies the criteria in the parameter table.


      Is there a way to create a formula to achieve this?


      Appreciate all help.


      I have a cross post here:


      http://www.mrexcel.com/forum/showthread.php?t=605515
      http://www.excelforum.com/exce…ria-and-populate.html</a>[/INDENT]



      [/INDENT]


  • Time Formula

    Re: Time Formula


    Hi,


    I hope I am getting this right.


    The downtime occurred on Feb 14 on 8.58 pm. The operating hours for this branch on Monday is between 9.30 am to 10.30 pm. For Feb 14, based on the operating hours on Monday, the downtime only affected the outlet from 8.58 pm to 10.30 pm which is 1 hour and 32 minutes.
    On Feb 15, the operating hours for the branch is still from 9.30 am to 10.30 pm. Therefore it is 13 hours.
    The downtime finally stopped on Feb 16 at 9.06 am. However, this did not affect the branch since the branch only opens at 9.30 am on Feb 16 which is a Wednesday.


    Therefore, in total, the downtime hours that affected the operating hours of the branch is 14 hours and 32 minutes.

  • Time Formula

    Hi,


    I have the following sheet that calculates the downtime in branches based on the specific operating hours everyday.


    The downtime ahould only take into account during the operating hours and should exclude hours of downtime during non operating hours.


    In the example attached, the actual downtime during operating hours is 14 hours and 32 minutes. Although the downtime lasted from Feb 14 to Feb 16, the downtime hours that affected operating hours was 14 hours 32 minutes and not 36 hours and 8 minutes.


    Is there anyway we could build a formula for this ?


    Appreciate all the help.



    I have a cross pots here:


    http://www.mrexcel.com/forum/s…php?p=2627565#post2627565

  • Change PivotChart Numbers To Percentage

    Re: Change PivotChart Numbers To Percentage


    Hi,


    My apologies. I think I might not have been clear.


    In my pivot table, I only need two data to be in % as below:


    1) Jan Error Points >>This should be in normal numerical . Example is 1.25
    2) Feb Error Points >>This should be in normal numerical . Example is 1.25
    3) Jan Q3 Score >> This should be in %
    4) Feb Q3 Score >> This should be in %


    The pivot chart should be as per the pivot table above in which error point should be in normal numerical and Q3 score should be in %.


    Based on the above, I changed data # 3 and 4 to % in the pivot table by right click>Numers>Percentage.


    However, the chart still shows say example 0.9397 for Jan Q3 Score and Feb Q3 Score instead of % as per the pivot table , say example 93.97%.


    Is there a way to do this ?

  • Calculate Percent Increase Based On Matrix

    Re: Calculate Percent Increase Based On Matrix


    Hi,


    I am not sure how to use vlookup on this one. The vloopup reads based on one criteria.The scenario that I have has multiple criteria.


    For example,


    1) On column I, for Abraham, since he has a 2 rating and a 78% ratio, he should get a 13% salary increase based on the salary grid which shows that a 2 rating with a ratio of 75% to 79% is 13% increase in salary.


    2) On column J, for Abraham, since he has a job time of 43 months, the bonus grid indicates that with a 2 rating and a job time of more than 18 motnhs, he should get 24.99% of bonus.


    Is is possible to use vlookup for both the criteria above or any other formula? I tried IF and AND but it I am not getting it right.


    Appreciate assistance.

  • Calculate Percent Increase Based On Matrix

    Hi,


    I need to calculate the % of salary increase and bonus for staff based on a matrix.


    1) The salary increase in based on the salary grid. Each staff is allocated a ratio as shown in cell H4 to H25. For example, for Abraham, he has obtained a rating of 2 and has a ratio of 78%. Looking at the salary grid, he should get a 13% salary increase.


    2) The bonus is also based on a bonus grid. The bonus is based on the rating and job time.The date used for current date calculation is Jan 11 2008. For example, Abraham has a rating of 2 and a job time more than 18 months.Therefore, he should get a bonus of 24.99% based on the bonus grid.


    I am trying to create a formula in salary increase% column (yellow) and the bonus % column (blue) to automatically update from the salary grid and bonus grid. The actual staff number is quite long and using a correct formula would ensure the data ia accurate based on the grid/matrix.


    Appreciate any help.

  • Populate Data From Another Sheet With Criteria

    Hi,


    I hope the thread title is correct.


    I have a spreadsheet which I need to populate the data from sheet based on current date.


    Cell C7 in the "Staff" tab needs to update from tab "Data" for John based on the current date . The current date is located in cell A3 in the "Staff" tab.


    On daily basis, as I open the spreadsheet , cell C7 should update from the "Data" tab automatically based on the date in cell A3 in the "Staff" tab.


    Example, on January 4 2008, cell C7 should populate as 2 from the "Data" tab.


    I tried using the IF formula, but I cant expand the formula for the whole month of January since it is limited only to 7 arguments.


    Appreciate help.

  • Determine Right Or Wrong Answer From Option Button Group

    Hi ,


    I am working on creating an examination questions in Excel. I want to also build in formulas in the Excel sheet so that the answers will be calculated automatically as the candidates choose the answers.


    I have inputed an option button from the froms toolbox beside each answers for the candidate to click to indicate the answers.


    Is it possible to calculate the correct answer or wrong answer with a formula when the candidate clicks on the option button ? Is there a formula that I can use ?


    Appreciate assistance.