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

[jasmine dee]

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!

[Simi]

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.

[Simi]

I had some time to work on this, I added this code on the worksheet change event.

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

[Hercules1946]

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