Solved

Excel formula needed

Posted on 2014-12-01
23
119 Views
Last Modified: 2014-12-09
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
0
Comment
Question by:cheryl23
  • 7
  • 3
  • 3
  • +5
23 Comments
 
LVL 4

Expert Comment

by:Brad Rubin
ID: 40475043
I would do this with Array formulas to find Min and Max and take the difference between the two.

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:$C$46))

Find the MIN (Array formula needs to use Ctrl+Shift+Enter to set formula)
=MIN(IF($A$2:$A$46=H2,$C$2:$C$46))

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
0
 
LVL 23

Expert Comment

by:Michael74
ID: 40475051
Here is a VBA script that will do this for you.

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

Open in new window

0
 
LVL 22

Expert Comment

by:Flyster
ID: 40475176
See attached. It uses this array formula:

=INDIRECT("C"&MATCH(A2,A:A,0))-INDIRECT("C"&MAX(ROW($2:$46)*($A$2:$A$46=A2)))

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
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40475210
@Cheryl

How many unique acct# values will you have?
0
 
LVL 5

Expert Comment

by:Hakan Yılmaz
ID: 40475389
If you can add columns, you can add one column to check if score in row is maximum of accounts score, and one column to check if minimum.
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
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40475761
For those of you looking at Max & Min scores, the question was relating to First and Last score. Looking at the first acct# the first and last are not the same as Max and Min.

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
0
 
LVL 5

Expert Comment

by:Hakan Yılmaz
ID: 40475775
In my comment, I write "max" and "min" by mistake.
The sheet i made takes first and last scores by date, not by row number.
And then calculates difference as Latest Score - First Score.
0
 

Author Comment

by:cheryl23
ID: 40476326
Wow, thanks so much for the responses.  I really appreciate your time.
@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.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40476358
@Cheryl

Thanks.  One follow-up question...
The worksheet had some empty columns (B and D).  Does this sample represent what your actual worksheet looks like?
0
 
LVL 22

Expert Comment

by:Flyster
ID: 40476966
@Cheryl

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

Accepted Solution

by:
aikimark earned 500 total points
ID: 40477108
1. Put this formula in F2: =IF(A2<>A1,C2,F1)
2. Put this formuls in G2: =IF(A3<>A2,F1-C2,"")
3. Fill-down F2:G2 to the last data row
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 4

Expert Comment

by:Brad Rubin
ID: 40477474
Nice one aikimark! Simple and easy.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40477669
It is certainly simpler than my first pass and second pass.  Glad you liked it.  The first time I used this type of formula was for an EE question that needed to create sequence/ID numbers within same-valued cell ranges.
http: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
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40478342
Are you wanting to add an entry against each person or would you be interested in a separate report whereby you enter/select the patient acct# and the report shows you the required info for that person?

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
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40478570
Went for it anyway!!

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
0
 

Author Closing Comment

by:cheryl23
ID: 40489554
This solution worked out great and was very easy to incorporate.  Thank you all for your help.
0
 
LVL 12

Expert Comment

by:tel2
ID: 40490006
A couple of minor points about aikimark's (rather nice) solution:

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.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40490152
@tel2

Although it was revised by the OP, the original requirement was
Take the beginning score and subtract it from an ending score
So, the score associated with the earliest date is subtracted from the score associated with the latest date.
0
 
LVL 12

Expert Comment

by:tel2
ID: 40490174
True, aikimark, and column F contains the earliest dates, right?  So F needs to be subtracted from C, which is C-F, right?  So why are you subtracting C from F here?:
    =IF(A3<>A2,F1-C2,"")
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40490364
The earliest score is carried down to the point where the account number changes.
0
 
LVL 12

Expert Comment

by:tel2
ID: 40490393
I'm well aware of that, aikimark, that's why I said:
  "...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.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40490402
There is a definitional interpretation between "first" and "last" and earliest and latest.  That is all.  Your version of the formula is probably what the OP used.  I didn't ask, since the OP didn't post a comment about my formula results or how to use the results.  I do not think there is any "bottom" to get to.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This is about my first experience with programming Arduino.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

3 Experts available now in Live!

Get 1:1 Help Now