+ Reply to Thread
Results 1 to 10 of 10

Compare values in two columns and get a percentage of accuracy?

  1. #1
    Registered User
    Join Date
    05-21-2019
    Location
    Scotland
    MS-Off Ver
    Office 365
    Posts
    6

    Compare values in two columns and get a percentage of accuracy?

    Hi I have one column which states what sequence was presented e.g. (1, 3, 5) and then another to indicate what participants recalled e.g. (1, 3, 2). I am wondering whether there is a way to compare the two and create a third column which gives a percentage of how many values were correctly recalled?

    Thank you!

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Compare values in two columns and get a percentage of accuracy?

    Try this:

    =SUMPRODUCT(--(A1:A3<>C1:C3))/COUNTA(A1:A3)

    The result from your example would be 33.33%

  3. #3
    Forum Contributor
    Join Date
    03-28-2018
    Location
    Houston, TX
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    127

    Re: Compare values in two columns and get a percentage of accuracy?

    Are you interested in how many numbers from the sequence were recalled in the correct order or just in how many numbers from the sequence were recalled altogether? As an example say the presented sequence is 1, 2, 3, but the participant recalls it as 1, 3, 2. Is this scored as 33% or 100%?

  4. #4
    Registered User
    Join Date
    05-21-2019
    Location
    Scotland
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Compare values in two columns and get a percentage of accuracy?

    Thank you so much for your response.

    That would be 100%, so order does not matter.

  5. #5
    Registered User
    Join Date
    05-21-2019
    Location
    Scotland
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Compare values in two columns and get a percentage of accuracy?

    Hi, thank you so much for your response. The numbers are all in one column, so the presented sequence is in A1, and the recall one in A2.

  6. #6
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Compare values in two columns and get a percentage of accuracy?

    See if this is what you are looking for:

    recall.png

    B2:J3 are helpers. The answer is in A3.
    I have arbitrarily extended the formulas to allow for 9 possible numbers, that can be expanded further as needed.
    In B1 copied across to J2 then down to B2:J2
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    This splits the comma separated numbers in A1 and A2 into separate columns.
    In B3 copied across to J3:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    This counts whether each digit in row-1 occurs in row-2 and discounts unused columns if there are less than 9 digits.
    In A3, the required answer:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    This provides the required percentage of correctly recalled digits.

    The attached workbook implements the above.

    Let me know if this is close to what you are looking for.
    Attached Files Attached Files
    Last edited by GeoffW283; 05-29-2019 at 08:55 PM.
    Geoff

    Did I help significantly? If you wish, click on * Add Reputation to say thanks.
    If your problem has been resolved please select ?Solved? from the Thread Tools menu

  7. #7
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Compare values in two columns and get a percentage of accuracy?

    Quote Originally Posted by zarza786 View Post
    The numbers are all in one column...
    From post #1:
    Quote Originally Posted by zarza786 View Post
    Hi I have one column ... and then another...
    It's going to be very difficult giving you a working solution with inconsistencies like this.


    If the solution offered by GeoffW283 in post #6 does not work for you, I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.

    To upload an Excel workbook, follow these steps:
    1) Click on "Go Advanced"
    2) Click on "Manage Attachments"
    3) Click on "Choose File"
    4) Choose your file and click on "Open"
    5) Click on "Upload"
    6) Click on "Close this window"

  8. #8
    Registered User
    Join Date
    05-21-2019
    Location
    Scotland
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Compare values in two columns and get a percentage of accuracy?

    Thanks for your help! I managed to use this and then tailor it to my data as I am doing it across rows rather than columns. Thank you so much!

  9. #9
    Registered User
    Join Date
    05-21-2019
    Location
    Scotland
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Compare values in two columns and get a percentage of accuracy?

    Managed to work on it with GeoffW282's suggestion, thank you. Apologies for the inconsistencies, I am new to this!

  10. #10
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Compare values in two columns and get a percentage of accuracy?

    OK, glad you got it working and thanks for the feedback. If that's all you need then can you mark the thread as solved - thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 11-02-2018, 06:56 AM
  2. Pivot - Percentage to compare two subtitle columns
    By 125ml in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 07-11-2018, 06:01 AM
  3. Compare Pivot Table Columns to show Percentage Change
    By PTables in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-29-2016, 02:52 AM
  4. Prediction Accuracy as a Percentage
    By JFactor in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-12-2012, 08:12 PM
  5. Compare two columns as a percentage
    By dgclubbie in forum Excel General
    Replies: 2
    Last Post: 08-09-2011, 05:03 AM
  6. Calculating forecast accuracy as a percentage
    By AussieExcelUser in forum Excel General
    Replies: 2
    Last Post: 06-07-2006, 01:19 AM
  7. [SOLVED] Compare columns and get a percentage
    By Kirk Pepper in forum Excel - New Users/Basics
    Replies: 12
    Last Post: 03-13-2006, 10:25 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1