Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

I hope to find an excel expert who can help create a formula, which would

Take the beginning score and subtract it from an ending score on each account number (there will be several scores for each acct #, but we just need the difference from the first score and the ending score and have that number placed in a cell. We currently do this manually, each month.

I've attached a sample worksheet.

If you can help, it would be so appreciated.

score-sheet.xlsx

Take the beginning score and subtract it from an ending score on each account number (there will be several scores for each acct #, but we just need the difference from the first score and the ending score and have that number placed in a cell. We currently do this manually, each month.

I've attached a sample worksheet.

If you can help, it would be so appreciated.

score-sheet.xlsx

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

It assumes that accounts are grouped but the dates can be in any order

I have used constants to make editing it for another workbook easier

```
Sub scores()
Const ACCT_COL As String = "A"
Const SCORE_COL As String = "C"
Const DATE_COL As String = "E"
Const RESULT_COL As String = "F"
Const START_ROW As Long = 2
Dim acct As String
Dim lastRow As Long, i As Long, startRow As Long, endRow As Long
lastRow = Range(ACCT_COL & Rows.Count).End(xlUp).Row
acct = Range(ACCT_COL & START_ROW).Value
startRow = START_ROW
endRow = START_ROW
For i = START_ROW + 1 To lastRow
If Range(ACCT_COL & i).Value <> acct Then
Range(RESULT_COL & i - 1).Value = Range(SCORE_COL & startRow).Value - Range(SCORE_COL & endRow).Value
startRow = i
endRow = i
acct = Range(ACCT_COL & i).Value
End If
If Range(DATE_COL & i).Value < Range(DATE_COL & startRow).Value Then
startRow = i
End If
If Range(DATE_COL & i).Value > Range(DATE_COL & endRow).Value Then
endRow = i
End If
Next
End Sub
```

=INDIRECT("C"&MATCH(A2,A:A

The Match function finds the row number of the first occurrence of the acct# and Max function find the last. Indirect is used to find the score associated with those row numbers. Conditional formatting is used to "white out" the duplicate values.

Flyster

score-sheet.xlsx

Then you can more easily continue calculating difference.

If you are using Excel 2010 or later, i will suggest you to use Calculated Tables as in sheet2 of attached file. With this way, your formulas are more secure and easier to understand.

score-sheet.xlsx

You could however use the Max and Min on the dates.

@cheryl23 - are the scores cumulative or is each entry the score for that date? For acct# 121300 are you expecting result of:

1) -15 8 on 20 May less 23 on 20 Jan

2) 86 109 cumulative to 20 May less 23 on 20 Jan

3) Something else?

Thanks

Rob H

The sheet i made takes first and last scores by date, not by row number.

And then calculates difference as Latest Score - First Score.

@Aikimark - Your question: How many unique acct# values will you have? For the 6 month period we are reviewing there are 361 unique acct#s

Rob Henson: Your question: are the scores cumulative or is each entry the score for that date? The scores are not cumulative. 1) 15 8 on 20 May less 23 on 20 Jan (this is what we are looking for POSITIVE 15, 23 minus 8)

Background: We give an assessment and take the total score for that date. We give assessments throughout the treatment period. We look at first score and compare to last score (most recent date). If score has decreased, improvement has been made during treatment (a lower INDIVIDUAL ASSESSMENT SCORE is better). In the example above 23-8 = 15 would show a 65% improvement.

I need to review & test the solutions submitted, may take a couple days. Thanks to all.

Thanks. One follow-up question...

The worksheet had some empty columns (B and D). Does this sample represent what your actual worksheet looks like?

Will the data always be in chronological order? If so, the formula I provided will work for you. (See attached above)

2. Put this formuls in G2:

3. Fill-down F2:G2 to the last data row

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trialhttp:Q_27768088.html#a38133521

I pondered and ADO solution, but didn't try it since the OP asked for a formula solution. An example of an ADO solution is here if you're interested: http:Q_27515061.html#a37369496

As a separate report you could make use of DMIN and DMAX functions.

Let me know if you are interested in that approach and I will work a sample for you.

Thanks

Rob H

See attached, update acct# at top left of Report sheet, other fields will change accordingly.

Acct# can be changed to be drop down for selection if so required.

Date fields use DMAX and DMIN based on Acct#, Score fields then use Max & Min Dates and DGET for date and acct# to get the score.

Thanks

Rob H

score-sheet.xlsx

Since the original request was to "Take the beginning score and subtract it from an ending score on each account number...", I think this:

=IF(A3<>A2,F1-C2,"")

should have been this:

=IF(A3<>A2,C2-F1,"")

And if there is any possibility of having only 1 score for an acct #, then both of the above will give incorrect results, as the result should technically be 0, since X - X always = 0. I think this would fix that:

=IF(A3<>A2,C2-F2,"")

I know the original request said "there will be several scores for each acct #", but it's hard to be sure whether cheryl was trying to imply that there will never be only 1. What say you, Cheryl? Either way, I think the last option above should work.

Although it was revised by the OP, the original requirement was

Take the beginning score and subtract it from an ending scoreSo, the score associated with the earliest date is subtracted from the score associated with the latest date.

=IF(A3<>A2,F1-C2,"")

"...and column F contains the [insert: 'score from the'] earliest dates, right?".

If you can explain what is wrong with the logic of my last post, we might get to the bottom of this. Maybe I'm missing something.

Thanks.

Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.

Create a new column with unique account numbers that will be used to reference your list of data.

Create a Max column

Create a Min column

Create a difference column

Using your example:

Find the MAX (Array formula needs to use Ctrl+Shift+Enter to set formula)

=MAX(IF($A$2:$A$46=H2,$C$2

Find the MIN (Array formula needs to use Ctrl+Shift+Enter to set formula)

=MIN(IF($A$2:$A$46=H2,$C$2

Then =Max - Min gives you the score difference...it is all automated based on your data set.

I attached your example data with the solution I noted above.

example.xlsx