Posts by jag.seven

  • Formula To Calculate Based On Criteria

    Re: Formula To Calculate Based On Criteria


    Hi,


    My apologies. The work items completed by the staff are sometimes has multiple errors. Each errors are separated, thus creating a duplicate. Example is as below:


    Date Staff ID Work Item# Errors
    Aprl 18 2007 M52 B123566 Incorrect term
    Aprl 18 2007 M52 B123566 Incorrect payment
    Aprl 18 2007 M52 B123566 Incorrect name
    Aprl 18 2007 M53 B123577 Clean
    Aprl 18 2007 M54 B123588 Clean
    Aprl 18 2007 M54 B123589 Clean
    Aprl 18 2007 M55 B121359 Clean
    Aprl 18 2007 M56 B223587 Clean


    Based on the above table, I have to summarize the number of work items completed by each staff as below:


    Staff ID #of Work Items Completed
    M52 1
    M53 1
    M54 2
    M55 1
    M56 1


    Note that for M52, the work number is duplicated 3 times because there were 3 errors in that work item. However, the formula should return only 1 as the number of work item completed for the day since it was only one item.
    Is there a formula I can use ? Appreciate help.

  • Formula To Calculate Based On Criteria

    I have a spreadsheet that has staff id in one column and the work items number that they have done in a daily basis in another column.


    The actual list is very long. I need to summarize in another column how many work items that they have completed in a daily basis.


    I have attached a sample spreadsheet as an example. I would need to summarize in column H based on the staff ID. Some work items are shared by two staff but it will have to be counted as one work item completed for each staff. If work item B123466 is completed both by staff M56 and M54, then it will be counted as one for each.Currently, I am doing this manually with the filter function which is very tedious and often has mistakes. I would like to formularize this task.


    Appreciate help.

  • Formula To Adjust Based On Target Cell

    Re: Formula To Adjust Based On Target Cell


    ByThe Cringe2,


    I have created the same file for the month of April but I am not sure if the formula is correct.
    a)If all actual points are blank on the March tab(E35-E36), the formula is set at 95% for cell L15 only.
    B)However if all actual points are blank for the Apr tab(E34-E54), the formula is set at 95% for all cells from L11 to L15.


    Is the formula correct for the Apr tab ?


    Appreciate your help.

  • Formula To Adjust Based On Target Cell

    Re: Formula To Adjust Based On Target Cell


    ByTheCringe,


    Can you guide me to modify formula in column F so that it adjust automatically (from F31 to F51)towards month end target (K13) rather than only that particular week?


    As we accumulate error points, I would need to know how we are pacing towards the month end target which is eventually the final score.


    My apologies for the inconvenience. Appreciate your assistance.

  • Formula To Adjust Based On Target Cell

    Re: Formula To Adjust Based On Target Cell


    ByTheCringe,


    I agree with your suggestion. How can I create the formula in column F ? I am not sure with your question as below:


    "What happens to the error points when you have them?"


    The error points in column E is also a forecast only and I replace them at the end of everyday with actual error points in the Dept.


    Appreciate if you can guide me with the formula in the suggested column F.

  • Formula To Adjust Based On Target Cell

    Re: Formula To Adjust Based On Target Cell


    ByTheCringe,


    The cells in K9 to K13 shows a cumulative score from one week to another week and finally cell K13 will show the final month end score. As I change actual numbers in each column C31, D31 and E31, the rest of columm E downwards should adjust accordingly to indicate the minimum error points that should not be exceeded by the dept in order to achieve the 90% target for month end which is in cell K13 for the final month end score. Is this possible ? Thank you for your time and effort.

  • Formula To Adjust Based On Target Cell

    Hi,


    I have a spreadsheet at work . I am tracking the quality target for the department and I need to create a formula that adjust accordingly to the target set for month end which is 90%. I have build in the foreacasted numbers for the whole month but I need the formula to indicate the minimum error points needed to achieve the target of 90% for month end when I replace the forecasted numbers on a daily basis with actual numbers.


    I have attached the spreadsheet which will be clearer. Appreciate help.

  • Rounding Up Numbers

    Re: Rounding Up Numbers


    BytheCringe2,


    It worked on numbers. I have data with percentage as well. Is there a way to modify the formula for % a well ? For example , I have 105.66% that needs to read as 106%. Thanks for your time and patience.

  • Rounding Up Numbers

    I have a long list of data that is entered with 2 decimal points Example is 23.66.


    This column is then tied to another column with a certain criteria. I need to round up the cell to read as 24 instead of 23.66. I tried Excel's build in tool but the cell still reads as 23.66 even though it can be viewed as 24.When you click on the cell, it still reads as 23.66.


    Need assistance as the list is very long for me change manually one by one. Appreciate help.

  • Read Only For Other Users

    Hi,


    I have an Excel file that is accessed by many users throughout the day. However, I am owner of the file and I would need to make changes to the file priodically throughout the day. Is there a way in which I can set the file as read only for other users after I have closed the file and even when I open the file while others is accessing the file, I can make the necessary changes and save it ?Appreciate help.

  • Multiple Sum Condition

    Re: Multiple Sum Condition


    ByTheCringe2,


    Thank you for the solution.This will definitely help me in my spreadsheet at work.I was not aware that I could add the condition in the sumproduct as I thought I needed to incorporate an IF formula. Thank you again for your patience, time and effort.


    Kumar

  • Multiple Sum Condition

    Re: Multiple Sum Condition


    Hi Jigar,


    Actually what I need is the formula to calculate the sum of #of errors(column D) only if there is an error points (column E) in that specific time frame. In the file that you have attached , if I delete the error points in E5, then the formula should return nil for time between 11.30 pm to 12.00 am. Appreciate your time and effort. I tried using IF formula but could not work.

  • Multiple Sum Condition

    Re: Multiple Sum Condition


    Jigar,


    Thanks for the reply and my apologies for the duplicate post. I needed to create an additional information on the spreadsheet.There is an additional column as below:


    Staff ID
    #of Errors
    Error Points
    Time


    The aditional column based on the attachment on the previous post is #of errors.


    I need to sum the total #of errors between a specific time frame i.e 11.30pm to 12.00 am only of if there is an error points for that time. The original spreadsheet at my work has some fields without error points but with #of errors. I need to create a formula that sums the total #of errors for the particular time only if there is an error point for that specific timr frame.Appreciate your help.