Results 1 to 10 of 10

Thread: What is the right IF function to use to sum the last above cell has value & ignore 0

  1. #1

    What is the right IF function to use to sum the last above cell has value & ignore 0



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,
    I need a hand to sort this formula the right way.
    I am working with a call center workbook for daily agents performance and number of calls.
    Call center system generates only cumulative data.
    For example:-
    1st day=50 , which means net calls = 50 as it is the 1st day and no other previous any calls.
    2nd day=80 , which means net calls = 80-50= 30 net calls.
    So I set a formula to calculate today cumulative calls volume minus yesterday cumulative calls volume as shown above.
    My problem arise in case there is any day off for any agent.
    This is current reads of the system and net calls calculated manually
    successnet
    1st day1515 (1st day and no other previous any calls)
    2nd day 28 13 (cumulative today success calls as read from the system =28) - (cumulative yesterday success calls as read from the system =15) = 13 (net calls)
    3rd day 42 14 same
    4th day 55 13 same
    5th day 0 0 Agent is off
    6th day 0 0 Agent is off
    7th day 0 0 Agent is off
    8th day 80 25 This is 80-55 which represent today calls - the last working day before the vacation of the agent which is 55, so 80-55=25 net calls.
    This is my problem, what is the right formula function to use to calculate the net calls correctly and take agent vacation in consideration?
    9th day 99 19 same
    10th day 114 15 same
    11th day 128 14 same
    12th day 156 28 same


    This is the excel sheet for this values to tell me the right thing.
    https://www.dropbox.com/s/3ou1axh904...llsFormula.xls


    So please what is the right formula to calculate the net calls and take this vacation into consideration?


    Thanks

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    742
    Articles
    0
    Excel Version
    Excel 2010 64bit
    try
    =IF(B3=0,0,B3-SUM($D$2:$D2))
    in D3 and drag down

  3. #3
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    This thread is crossposted in other forums. Please post all links to those threads.


  4. #4
    Quote Originally Posted by NBVC View Post
    This thread is crossposted in other forums. Please post all links to those threads.
    Yes that was me, what is wrong in doing so?
    Thanks

  5. #5
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    742
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Over at that other forum, when you post something, right under your user name it says, I agreed to these rules, and #4 says you don't do that.

  6. #6
    Quote Originally Posted by NoS View Post
    Over at that other forum, when you post something, right under your user name it says, I agreed to these rules, and #4 says you don't do that.
    Ok, sorry, what shall I do now to be considered an appropriate

  7. #7
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    Copy and paste the link(s) to your thread(s) from those forums here, and the same at each of those forums. People want to know that you have the question elsewhere so that they know if it has been answered already or if there are already enough people getting somewhere with a solution. This will allow them to make a choice whether to contribute something useful, or help someone else while you are being helped elsewhere already.

    Read this: http://www.excelguru.ca/content.php?184


  8. #8
    Quote Originally Posted by NBVC View Post
    Copy and paste the link(s) to your thread(s) from those forums here, and the same at each of those forums. People want to know that you have the question elsewhere so that they know if it has been answered already or if there are already enough people getting somewhere with a solution. This will allow them to make a choice whether to contribute something useful, or help someone else while you are being helped elsewhere already.

    Read this: http://www.excelguru.ca/content.php?184
    Ok my friend, no problem and sorry if I misbehave
    This is the link you asked for:-
    http://www.excelforum.com/excel-form...ml#post3767581
    Now my question is how to adjust this equation:-
    =IF(B2=0,0,B2-SUM(C$1:C1))
    How to let it start from C367 ? instead of C1?
    I tried but it reports an error #value, so please help

  9. #9
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    Actually, I am pretty sure I saw your thread at Ozgrid forum too. Please post the link. And if you posted elsewhere, please also post those links.


  10. #10
    Quote Originally Posted by NBVC View Post
    Actually, I am pretty sure I saw your thread at Ozgrid forum too. Please post the link. And if you posted elsewhere, please also post those links.
    Yes I did posted in several forums as I mentioned.
    I thought we bypassed this issue to back to topic.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •