Please help with adjusting/fixing existing formulas.

chaos247

New member
Joined
Apr 2, 2013
Messages
5
Reaction score
0
Points
0
Hi,

I have created a spread sheet that is used as an assessment tool for a call centre. it has been reviewed and there are some changes that need to be made. Im having a little difficulty as im not that proficient at writing formulas yet. You’ll see the once I’ve created are pretty simple. PLEASE PLEASE PLEASE help me with this.

Formals are stored in E23, E34, etc...

The changes I have to make:

-there are cells that are marked as “auto zeros.” If it is marked as “no” it should be counted as an auto zero in cell C50. At the moment when the sheets are blank, its i reading as a auto zero(see C55) How do i adjust my formula to do that?

-When assessing, every drop down must be used. There can be no blanks. If there is, it should come back as an error.

-Each call score needs to be out of 100% even though the score sheet only totals to 61%. This should reflect in E48, F48... etc. Do i just use the method i used in D58:D60?

The summary sheet:

I used a pretty simple macro. I need this to calculate automatically each time the sheet is used not when the summary button is pressed. Would i simply insert the following?

Private Sub Worksheet_Change(BYVal Target As Range)

Your help will be much appreciated.
 

Attachments

  • Test sheet.xlsm
    318.6 KB · Views: 13

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
Hi Chaos247
Ive resolved some of your points:
To count the autozeros I placed = SUM(E50:L50) in C55 and
=COUNTIFS(E20:E47, "=No", $D$20:$D$47, "=AUTO ZERO") in C50,
Copying across to L50.
Im putting an error message in E51 if any cells in the E col are blank (copy across to row L)
=IF(COUNTIF(E20:E47,"")>4, "ERROR", "")

HTH
 
Top