Results 1 to 2 of 2

Thread: Please help with adjusting/fixing existing formulas.

  1. #1

    Please help with adjusting/fixing existing formulas.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 D5860?

    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.
    Attached Files Attached Files

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •