+ Reply to Thread
Results 1 to 2 of 2

Backing into Formula

  1. #1
    Registered User
    Join Date
    10-27-2013
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2003
    Posts
    1

    Backing into Formula

    This is a formula that is accurately being used in my spreadsheet. I did not create the formula but I am trying to "understand" what it is saying in the event I need to change it or edit it in the future. I have tried reading the formula and backing into it but I do not see how it's coming up with the answers. AS background, columns G4-BA4 assigned a percentage to the answeres in the columns below. The percentage adds up to 100 or 1.00. Columns G5:BA5 and below contain answers 1, 0, or NA. Hope this is enough to answer my question. Basically, if an answer is 0, the answer is wrong and it subtracts from 100% and based on the percentage that answer was given will result in a percentage correct. In example, if the question that they got wrong or "0" had 5% percentage points assigned, this formula returns back a 95%. AGain, I've tried backing into this but even though I know it works, I can't seem to get it to work in my own head. Just curious---

    =sumproduct((g5:ba5=1)*($g$4:$BA$4))/(sumproduct(g5:ba5=1)*$g$4:$ba$4))+sumproduct((g5:ba5=0)*($g$4:$ba$4)))

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Backing into Formula

    It looks like the idea is that you get the sum of numbers in row 4 when row 5 is 1 (SUMPRODUCT #1) and then divide that by the total of row 4 numbers when row 5 is either 1 or 0 (SUMPRODUCTs #2 and #3). The result is the score of correct answers as a percentage of all the non "NA" answers

    As written here you have some parentheses in the wrong places, I think it should be like this:

    =SUMPRODUCT((G5:BA5=1)*$G$4:$BA$4)/(SUMPRODUCT((G5:BA5=1)*$G$4:$BA$4)+SUMPRODUCT((G5:BA5=0)*($G$4:$BA$4)))

    although I think you could do that more simply with 2 SUMIFS - divide the %s for correct answers by those for all non-"NA" answers, i.e.

    =SUMIF(G5:BA5,1,$G$4:$BA$4)/SUMIF(G5:BA5,"<>NA",$G$4:$BA$4)
    Audere est facere

+ 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. [SOLVED] Backing up folder
    By dan in forum Excel General
    Replies: 4
    Last Post: 07-20-2006, 11:20 PM
  2. [SOLVED] Backing Up
    By Greg B in forum Excel General
    Replies: 7
    Last Post: 03-04-2005, 12:06 PM
  3. [SOLVED] Backing Up
    By Greg B in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-04-2005, 12:06 PM
  4. Backing Up
    By Greg B in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-04-2005, 12:06 PM
  5. [SOLVED] Backing up my folders
    By Greg B in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-03-2005, 06:06 AM

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