Results 1 to 4 of 4

Thread: Input data then clear cell but save to another cell..Is there a formula for this?!

  1. #1

    Input data then clear cell but save to another cell..Is there a formula for this?!



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

    I want to be able to see the average rating for each employee.. Is there a way to put in the rating from one survey and have it automatically add it another cell and then have it clear the first cell?

    Here is an example of what I would like to do:
    Say we get 2 surveys with one rating of 5 and one rating of 10... If I input the 5 into cell Q4 and have it automatically put into cell R4 and then it gets deleted from Q4 but saved to R4... and then I can add the rating of 10 to Q4 and it automatically ads it to R4 and clears Q4 and totals R4 to an average of 7.5.

    This way, with each new survey I can just input each rating to Q4 and have it cleared for the next rating and then have an average rating of all the surveys in R4... we do not need to keep track of how many ratings we are getting, just the average rating.


    I have attached the excel sheet if you would like to take a look at what I am trying to do..

    If this is possible could someone please help.. I would really appreciate it!!
    Or if anyone has any ideas of an easier way to do this please let me know.

    Thank you soo much!
    Attached Files Attached Files

  2. #2
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    The problem with what you are trying to do, is to get an actual average you need to keep track of how many values are added up to be averaged.
    for example, you have a 5 and a 10 for an average of 7.5. if your next entry is another 10 your average should be 8.33, however if you don't keep track of how many items you have and take the average from R4 and add the new value and then average you end up with 8.75. I will think on this some more, because we could have S4 be a running total of all the values, and T4 be a running total of how many entries. then R4 could be an acurate average.

  3. #3
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    I had some time to work on this, I added this code on the worksheet change event.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$Q$4" Then
            With ActiveSheet
                If IsEmpty(.Range("Q4")) = False Then
                    .Range("S4") = .Range("S4") + .Range("Q4")
                    .Range("T4") = .Range("T4") + 1
                End If
            End With
        End If
    End Sub
    Surveys 1-simi.xlsm
    Last edited by NBVC; 2014-01-30 at 06:10 PM.

  4. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    773
    Articles
    0
    Excel Version
    2010
    Its a bit difficult to advise without knowing how much data your processing. You can't really do this by formula alone, because your wanting to clear and re-use cells as part of the process
    You could handle a small amount of data manually as in A1 + A2 =A3. A1 is your B/ f total; A2 is your new rating; A3 is your c/f total. To update you Copy and Paste Special values from A3 to A1 and set A2 = 0
    You would also need a counter for the surveys in order to strike an average. The simpler way to do this would be to have a detail area or sheet with a summary area/sheet for your totals and averages then you
    dont need to re-use cells.
    If you have a large amount of data and its organised and laid out consistently I would probably recommend using a macro or VBA module to update the cells assuming you want to go with the re-use approach.

    HTH

    Hercules
    I

Posting Permissions

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