LinkBack URL
About LinkBacks
Hey Guys, I need help with a formula. I am trying to calculate total calls made from the previous day. The problem that is happening is that the calls vary on a daily basis. I have tried several formulas ( I am not a master by any means on this) and nothing has worked like I needed it to. I have posted some dummy data below. "Yesterday" starts in A4 and "Today" starts in G4. I am trying to get a tally on the number of calls made daily. 0 stands for zero attemts; 2 stands for 2 attempts; 3 stands for 3 attempts; Complete is the addition of 0+2+3 columns. The tcm column in Today stands for Total Calls made.
Yesterday 0 2 3 complete
bike 300 125 50 475
skateboard 300 150 50 500
skates 0 0 50 50
scooter 0 125 65 190
Today 0 2 3 complete tcm
bike 100 325 50 475 200
skateboard 275 170 0 445 75
skates 0 0 7 7 43
scooter 0 23 86 109 154
For Yesterday, these were the calls they had. For Today, this is what is remaining. I need to find how many calls each one made. In the column for tcm, this is were the formula is to go to figure out the total calls made for each one. I have placed the actual numbers the formula needs to calculate so everyone know what numbers I will be needing.
For bike he had 300 calls in 0 attempts, he now has 100. He did no other calls in any other column, So today the 200 calls he made went to 2 attempts giving him 325. The 3 attempts stayed the same. Thus bike had 200 calls for the day.
Skateboard started out with 300 calls yesterday in 0 attempts. Today he has 275 calls. He did no calls for 2nd attempts, but finished the 50 he had in 3rd attempts. If you look at today, Skateboard has 275 attempts, because he only did 25 of them. Now look at second attempts. He has 170 calls - that's 20 added, not 25. (*This is important. Even though he made 25 calls, only 20 calls made it to second attempts. The reason the other 5 calls were not added is because they are now completed calls. If a call is completed, it will not go to another attempt.) And he finished the 50 calls he had in 3 attempts, giving him a total of 75 calls made for the previous day.
For Yesterday, Skates had no attempts for 0 and 2 attempts, but 50 for 3 attempts, he did all but 7 calls, leaving him with 43 calls made for the day.
For Yesterday, Scooter had no first attempts, 125 2nd attempts, and 65 third attempts. Today, Scooter had no first attempts, he finished 102 calls from the 2 attempts giving him a total of 23 for Today. He finished 52 3rd attempts leaving him with 13 calls left plus 73 calls carried over from the 2nd attempts he didn't complete, leaving him with a total 86 3 attempts left and a total of 154 calls completed since yesterday.
As you can see the numbers change daily and there is no exact a+b=c method involved. I hope someone can figure out something that can calculate these total calls made.
Sorry for the long post.
Register To Reply
Bookmarks