|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
What is the right IF function to use to sum the last above cell has value and ignore zero?
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 success net 1st day 15 15 (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
|
||||
|
||||
Your link was nonfunctional for me.
It sounds like some days you get a cumulative figure, and sometimes it comes through as zero. Add a column that uses either the prior cumulative number or the number above. E.g. A2=15 A3=18 A4=42 A5=55 a6=0 a7=0 a8=0 a9=80 Then E1=0 E2=if(A2>0,A2,E1) F2=E2-E1 And copy down. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Using the sum function to display number as well as words in a single cell | officeboy09 | Excel | 8 | 10-30-2013 05:38 AM |
How can I ignore historic over-allocation | scott.hannaford | Project | 1 | 10-23-2013 06:22 AM |
How to ignore the in between cells? | babapusy | Excel | 1 | 01-21-2013 03:05 AM |
Ignore Resource Work Times | JR_12 | Project | 2 | 12-05-2011 06:35 PM |
Ignore Duplicate Emails | IanKM | Outlook | 1 | 10-20-2011 11:02 AM |