Formula Help

[Erika]

I have a spreadsheet with sales reps and I am tracking points if they make
their month or not. So for example

Jan Feb Mar April May June
John Doe Y N Y Y Y N
=10+0+10+11+12+0
Jane Smith N Y Y N Y Y
=0+10+11+0+10+11+12

I am trying to find a way to get a grand total - each month I come in the
spreadsheet and enter either a Y or a N. If they make their month they get
10 points, if they make their month the next month they get 11 points however
if they don't start over. The trick is if they don't make their month and
get a zero next they start back over at 10 and increase from there. I need
to track this for an entire year and keep a running total.

Any ideas?

 

[Luke M]

You could use this short macro. (Open VBA by right clicking on sheet tab,
view code, paste this in)
Select the cell you want total in, then run this (Alt+F8, select macro)

Sub FindMyTotal()

For Each cell In Range(ActiveCell.Offset(0, -12), ActiveCell.Offset(0, -1))
If cell.Value = "Y" Then
total = total + PriorCell + 10
PriorCell = PriorCell + 1
Else
PriorCell = 0
End If
Next cell
ActiveCell.Value = total
End Sub

 

[Brad]

Not the most elegant but it works
Assume that your "Y" and "N" are in columns F - Q

Starting in column S enter the equation
=IF(F11="N",0,IF(AND(E11<>"Y",F11="Y"),10,R11+1))
and copy that across to column AD

Adjust the rows and columns as necessary...

If this helps - click yes this answers the question.

 

[T. Valko]

Try this:

Create this 2 column table:

0........0
1.......10
2.......21
3.......33
4.......46
5.......60
6.......75
7.......91
8.......108
9.......126
10.....145
11.....165
12.....186

Assume that table is in the range O113

A1:L1 = y or n

Enter this array formula** in M1:

=SUM(LOOKUP(FREQUENCY(IF(A1:L1="y",COLUMN(A1:L1)),IF(A1:L1<>"y",COLUMN(A1:L1))),O1:O13,P113))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.