Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Help
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Help
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 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Erika" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Help
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. -- Wag more, bark less "Erika" wrote: 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Help
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 O1:P13 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,P1:P13)) ** 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. -- Biff Microsoft Excel MVP "Erika" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|