Excel - COUNT Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

need a formula for incremental and decremented to justify the overall incident count
Solved by I. C. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 01/08/2018 - 12:45
hello..
User 01/08/2018 - 12:45
Hello
Excelchat Expert 01/08/2018 - 12:45
could you explain your problem..??
User 01/08/2018 - 12:46
do you see the sheet
Excelchat Expert 01/08/2018 - 12:47
yes..
User 01/08/2018 - 12:47
see column R and S ..
User 01/08/2018 - 12:47
the sum is 711..
User 01/08/2018 - 12:48
650 for Email/Phone & 400 for Chat& Self Service
User 01/08/2018 - 12:49
Per incident = 0.06
User 01/08/2018 - 12:50
so anything after 650 tickets, then per incient should be 0.06 \(marks) and now if its less than it shud be -0.06
User 01/08/2018 - 12:51
if 650 tickets , points = 40 , anyting above that 650 +0.06
User 01/08/2018 - 12:51
if less tan 650 , then 40-0.06( per incident)
User 01/08/2018 - 12:52
Did you get that
Excelchat Expert 01/08/2018 - 12:52
so 650 is max number..??
Excelchat Expert 01/08/2018 - 12:53
how do you get 650 for Email/Phone & 400 for Chat& Self Service
User 01/08/2018 - 12:53
i have a sheet for that ..
User 01/08/2018 - 12:53
let me put that
User 01/08/2018 - 12:54
in sheet 2 ..
User 01/08/2018 - 12:54
let me explain again ..
Excelchat Expert 01/08/2018 - 12:54
ok
User 01/08/2018 - 12:55
650 and 400 are baseline numbers
User 01/08/2018 - 12:55
650+0.06 = ? similarly 650-0.06 =?
Excelchat Expert 01/08/2018 - 12:56
ok.. base line for email is 650 and chat is 400
User 01/08/2018 - 12:56
yes t
Excelchat Expert 01/08/2018 - 12:57
so you have two different sheets for each agent?
User 01/08/2018 - 12:57
one sheet ..
User 01/08/2018 - 12:58
sheet 2 is where i have the count of the chats or emails handled
User 01/08/2018 - 12:58
sheet 1 should talk about score
Excelchat Expert 01/08/2018 - 12:58
ok..
Excelchat Expert 01/08/2018 - 12:59
let me explain your request, correct me if I misunderstood you..
User 01/08/2018 - 12:59
sure
Excelchat Expert 01/08/2018 - 01:01
You want to score Email/Phone = 40 + (Incident-650) *0.6
User 01/08/2018 - 01:02
Yes ,, but if less than 650 than (40-incident:650)*0.06
User 01/08/2018 - 01:02
there should be a negative scoring ..
Excelchat Expert 01/08/2018 - 01:02
ok.. under stand..
Excelchat Expert 01/08/2018 - 01:03
where do you want enter scoring formula...E5,,??
User 01/08/2018 - 01:04
in sheet 1 - U5
Excelchat Expert 01/08/2018 - 01:04
hold on..
User 01/08/2018 - 01:05
ok
Excelchat Expert 01/08/2018 - 01:06
I entered formula at U5..
User 01/08/2018 - 01:07
keep that chat seperate ..
User 01/08/2018 - 01:07
Phone and email = 650
User 01/08/2018 - 01:08
for Chats you should put the values in column "AC"
Excelchat Expert 01/08/2018 - 01:08
hold on..
User 01/08/2018 - 01:08
ok
Excelchat Expert 01/08/2018 - 01:09
I entered formula in AC5..
User 01/08/2018 - 01:10
formula looks to be good
User 01/08/2018 - 01:11
but AA5+AB5 = 123
User 01/08/2018 - 01:11
how come score is showing 383.38
Excelchat Expert 01/08/2018 - 01:12
sorry.. i multiplied 0.06.. I have corrected to 0.6
Excelchat Expert 01/08/2018 - 01:13
which one i correct 0.06 or 0.6
User 01/08/2018 - 01:13
0.06
Excelchat Expert 01/08/2018 - 01:13
hold on..
Excelchat Expert 01/08/2018 - 01:15
ok..
Excelchat Expert 01/08/2018 - 01:15
reason score is 383.38 is..
Excelchat Expert 01/08/2018 - 01:15
base line 400
Excelchat Expert 01/08/2018 - 01:15
total is 123..
User 01/08/2018 - 01:16
yep
Excelchat Expert 01/08/2018 - 01:16
227 under the 400 base line..
Excelchat Expert 01/08/2018 - 01:16
400-277*0.06
User 01/08/2018 - 01:16
ah..
User 01/08/2018 - 01:17
baseline 400 means 40 points
Excelchat Expert 01/08/2018 - 01:17
got it..
Excelchat Expert 01/08/2018 - 01:18
I changed 40 points for baseline
Excelchat Expert 01/08/2018 - 01:18
both of the formulas..
User 01/08/2018 - 01:18
letme check that
User 01/08/2018 - 01:21
yes makes sence ..
User 01/08/2018 - 01:21
can i ask another question
Excelchat Expert 01/08/2018 - 01:21
sure..
User 01/08/2018 - 01:24
IN sheet 4 , go to column AL4
Excelchat Expert 01/08/2018 - 01:25
ok..
User 01/08/2018 - 01:25
For this Target is <1 and point = 5 ..
User 01/08/2018 - 01:26
the points should show in Sheet 1# Column M5
Excelchat Expert 01/08/2018 - 01:27
ok..??
User 01/08/2018 - 01:28
yep
Excelchat Expert 01/08/2018 - 01:28
M5 is worked hours.. and AI4 is working days..
Excelchat Expert 01/08/2018 - 01:29
sorry..
Excelchat Expert 01/08/2018 - 01:30
AL is accuracy...
Excelchat Expert 01/08/2018 - 01:30
If AL4 is less that 1 , and point will be 5..??
Excelchat Expert 01/08/2018 - 01:31
how about 1 and over ..??
Excelchat Expert 01/08/2018 - 01:31
it will be 0..??
User 01/08/2018 - 01:31
ItsColumn L5 in sheet 1
Excelchat Expert 01/08/2018 - 01:31
OK..
Excelchat Expert 01/08/2018 - 01:31
L5..
User 01/08/2018 - 01:32
Yes L5 for time sheet accuracy
Excelchat Expert 01/08/2018 - 01:32
ok.. got it..
Excelchat Expert 01/08/2018 - 01:32
so if time sheet accuracy is 1, you want to score as 5..??
User 01/08/2018 - 01:33
Let me explain
Excelchat Expert 01/08/2018 - 01:33
ok..
User 01/08/2018 - 01:34
Time sheet meaning filling attendance ..
User 01/08/2018 - 01:34
This cannot go wrong by employee ..
User 01/08/2018 - 01:34
<1 = 5 points
User 01/08/2018 - 01:36
did you get that ..
Excelchat Expert 01/08/2018 - 01:37
what do you mean <1
User 01/08/2018 - 01:37
yes absolutely
Excelchat Expert 01/08/2018 - 01:37
is less than 1.. or greater than 1..
User 01/08/2018 - 01:38
less than :)
Excelchat Expert 01/08/2018 - 01:38
if accuracy is less than 1.. agent get 5 points..
Excelchat Expert 01/08/2018 - 01:38
if 1 and higher will get 0 points..??
User 01/08/2018 - 01:39
yes
User 01/08/2018 - 01:39
u have saved . me .. you are a champ
Excelchat Expert 01/08/2018 - 01:39
ok..
Excelchat Expert 01/08/2018 - 01:39
I entered formula for time sheet..
User 01/08/2018 - 01:40
i wantd to ask the last one ..
Excelchat Expert 01/08/2018 - 01:40
go head..
User 01/08/2018 - 01:40
looks like i have 4 mins left
Excelchat Expert 01/08/2018 - 01:40
quickly..
Excelchat Expert 01/08/2018 - 01:40
yes..
User 01/08/2018 - 01:40
in column AK ..
Excelchat Expert 01/08/2018 - 01:41
ok..
User 01/08/2018 - 01:42
if agents gets 100% then score should be 10+2.00 otherwise negative score
User 01/08/2018 - 01:42
points= 10 . score = 2.00 after 10.otherwise negative
User 01/08/2018 - 01:42
2mins .. thats it
Excelchat Expert 01/08/2018 - 01:45
that's it..
User 01/08/2018 - 01:45
it was simple ..

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc