**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.*