Posts by jag.seven

  • Return 1 Or Zero Based On Cell Value

    Hi,


    I have the following scenario


    Answer 1 Answer 2 Score
    Correct Correct 1
    Correct 1



    I need a formula in the "score" column to be based on the following criteria:


    a) If both answer 1 and answer 2 is "Correct" , then column "Score" should populate 1 . If not, it should populate 0.


    b) In some cases, answer 2 is blank- no answer is needed in answer 2. If answer 1 is "correct" and answer 2 is blank, then the "Score" should be 1. If not is should be 0.


    Currently I ma using the following formula , but it doesnt work.


    =IF(K8&L8="Correct",0,1)


    Appreciate assistance.

  • Sum By Condition & Other Tests

    Re: If And Sum Formula


    My apologies, the formula actually should be in K11.


    There is two part to the formula as below:


    F(F11=0,SUM($E$39+E11+J11-$F$39),SUM($E$39-H11+J11))


    The second part in bold is working fine. What I need the first part is the first part to have cell E39 (4153) minus the sum of cell F36 plus cell E11.


    For example, in cell K27, the formula result should be as follows:


    =4153- (1410+1289)
    =1454


    The 1289 in the above is a combination from cell E19 to E27.


    Is there a way to incorporate this criteria in the first part of the formula ? Thanks.

  • Sum By Condition & Other Tests

    I am trying to use IF and SUM formula as below in cell K11.
    =IF(F11=0,SUM($E$39+E11+K11-$F$39),SUM($E$39-I11+K11))


    However I am not getting it right on the first part of the formula.


    I need if cell F11 is empty, =cell E39 - (sum cell F39 with cell E11). Beginning from the point where column F is empty, the formula should add cell F39 with the cumulative column of column E.


    Example is on cell H27, the formula should add cell F39 with the total of cell E19 to E27.


    Appreciate help.

  • Caculate Number To Reach Target

    Re: Caculate Number To Reach Target


    Hi Darkyam,


    Thank you for the answers.


    If the total competed in column F is consistently more than column E, does column G still generate the correct value ? Example 129+(560-605)/25. Since (560-605) will generate a negative, will column G still be correct ?


    At the end of the month, the dept will be measured whether we achieved the target or not based on the same formula (H4 with total forecasted as 14,500).As long as H3 is TRUE, we would need to achieve 4153.If H3 is false, then the volume target is waived. For calculation purpose, we can have as TRUE so that even though it turns FALSE, we still have a target to work on.


    Is there any way to expand the current formula to incorporate this ?


    Appreciate your help and again thank you for your time and support.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Sorry for the duplicate reply.


    To show the minimum everyday, it would be more important to know how we are pacing against 4153 on a month to date basis.


    Just to check again, therefore, we can't expand the current formula to show what we need to complete everyday based on daily inbound and show how much we pacing againts the 4153 target ?

  • Caculate Number To Reach Target

    Re: Caculate Number To Reach Target


    Hi Darkyam,


    If the total competed in column F is consistently more than column E, does column G still generate the correct value ? Example 129+(560-605)/25. Since (560-605) will generate a negative, will column G still be correct ?


    At the end of the month, the dept will be measured whether we achieved the target or not based on the same formula (H4 with total forecasted as 14,500).As long as H3 is TRUE, we would need to achieve 4153.If H3 is false, then the volume target is waived. For calculation purpose, we can have as TRUE so that even though it turns FALSE, we still have a target to work on.


    Is there any way to expand the formula to incorporate this ?


    Appreciate your assistance.


    Again, thanks you for your patience and support.

  • Caculate Number To Reach Target

    Re: Caculate Number To Reach Target


    Hi ,


    I need assistant based on the spreadsheet that has been modified.


    1) I was trying to calculate manually to understand the formula in column G. In cell G12 for example, if I am understanding the formula correctly, we take 129 + 450 -438 / 28. I am getting 5.025 but the formula gives 129.


    2) The formula in G seems to default to divide by 28 days but shouldn't this be divided by the balance of working days everyday?


    3) The formula in G does not minus off the target in F2. Does it not suppose to minus F2 and divide by remaining working days ?


    Appreciate your help.
    Thanks.

  • Caculate Number To Reach Target

    Calculate Number To Reach Target-Part 2


    Darkhyam and anyone on the forum,


    My apologies.


    Let me explain again how this works.


    My company will assign my dept a target to achieve for competed items. This target is calculated based on the formula below:


    =((14,500*0.36)+((C3*0.13)*2))*0.44*1.05
    = 4163 items


    All items in the above formula is fixed everymonth except the number 14,500. This is the total items forecasted to be received in my company for the month of December. Out of this number, my dept is expected to complete 4163 items.


    At the end of the month , the same formula is used to determine whether my dept achieved the target or not. However, at the end of the month, actual received for the month of December is used instead of the forecasted amount. Assuming the actual received by the company is 15,502, then my dept should have completed 4440 as below:


    =((15.502*0.36)+((C3*0.13)*2))*0.44*1.05
    = 4440 items


    Therefore, everyday we need to key in the total received by the company to check how many items need to be completed by my dept.
    I need to create a formula to tell me how many items I need to complete everyday based on actual received everyday.


    The tricky part is this. The target will vary everyday since actual received will differ.


    In addition, my dept will not be held as breach of target if the total actual received by the company was less by more than 10% compared to the forecasted amount or more by 10%.


    So if it actual forecasted was less than 10% ( say 6%), then we will still need to achieve that target based on that amount ( the target formula will need to calculated based on less than 6% which is 13,360) and the same applies if target is more but less than 10%



    I hope this helps. I have reattached the amended spreadsheet to for a clearer picture.
    Appreciate help from all.

  • Caculate Number To Reach Target

    Re: Formula With Multiple Criteria


    Darkyam,


    The term BC is merely the name for the item in my dept.


    Inbound needs to be accounted since if you look in cell F2, the formula to calculate the target for the month includes total inbound which is based on total forecasted inbound in cell C3.


    At the end of the month, the same formula in cell F2 will be used to determine whether we achieved the target or not. The difference at the end of the month will be the total forecasted inbound in cell C3 will be replaced with total actual inbound in cell C2.


    Therefore, I need the formula to show me everyday the minimum number to complete each day to achieve the target.


    Do let me know if you need additional info. Appreciate your help.

  • Caculate Number To Reach Target

    Re: Formula With Multiple Criteria


    Hi Darkyam,


    The "Target BC" is calculated based on expected inbound(cell C3). However, this is only forecast.This forecast is broken down daily in cell C9 downward.


    This numbers will then be replaced with actual number which is entered daily in D9 downwards. Therefore, the cumulative actual inbound is in cell C2.


    On daily basis, we enter the actual completed item in cell G9 downwards.


    The formula needs to take into account the actual inbound in cell C2 and the actual completed in cell G9 downwards to generate the minimum item to be completed to achieve the "Target BC" in cell F2.


    As we enter the actual numbers in cell D9 downwards and cell G9 downwards on daily basis, the formula should populate the minimum number to completed to achieve the target.


    Please let me know if you need any additional information .

  • Caculate Number To Reach Target

    I have a target to achieve every month in my department for the number of items completed named BC.I am trying to create a formula so that I know how many minimum items I need to complete every day in order to achieve this target by month end. The target to complete each month is calculated with multiple variables and therefore I am not sure how create a formula to calculate the minimum item to be completed everyday to achieve the target by month end. I have attached the spreadheet and appreciate help. The target to achieve is named "Target BC" in red.


    In addition, at the beginning of the month, we only have forecasted numbers and these numbers needs to be replaced by actual numbers everyday.


    Appreciate help.

  • Count Between 2 Numbers

    I am trying to use a countif formula to calculate how many cells are above 95% from a given row.. However, some of the cells in the row has numerical values and not percentage. How can I modify the formula to calculate how many cells are above 95% from the whole row? Example as below:


    A B C D
    94.8% 4.25 3.25 96.5%



    Based on the above, I used a countif formula to calculate how many cells are above 95% but the solution was wrong. Appreciate help.

  • Summarize Worksheet Table

    I need to help to summarize a long list of data. This data consist of a long list of staff id with exception type and points. However, I have only a certain number of staff from the long list which is applicable to my department. For example, I only need data for 4 staff from the list. This list will duplicate the staff id based on dates. Therefore, there might be several cells with exception type and points for that particulat staff . Example is for staff M41 as below:


    Date Staff ID Exceptiontype Points Item#
    Dec 1 M45 Contract 1.0 44556
    Dec 1 M41 Contract 1.0 44578
    Dec 2 M43 Contract 1.0 44579
    Dec 3 M46 Contract 1.0 22569
    Dec 4 M41 Contract 1.0 44572


    Based on the above data, I need to summarize as below:


    Staff ID Exception Type Points Exception #
    M41 Contract 1.0 44578
    M41 Contract 1.0 44572


    How do I build a formula to summarize as above from the list ? I have a total of 33 staff id and the actual list has 100 staff id from which only 33 is applicable to me. Appreciate help.
    Attached is an example.