5 Replies Latest reply: May 15, 2014 10:08 AM by Data Kruncher RSS

    Comparing data in one cell to a range of cells

    SheridanBI _

      Hi All,

       

      I'm looking for a way to compare data in one cell against data in a range of cells. In Excel, an equivalent comparision would be something like a sumif function. Ex: sumif(B:B,$B4,E:E). I'm trying to assign weights to line items based on certain criteria that I would like to incorporate into a Monarch model so that it is already in place when I export the file.

       

      Thanks.

        • Comparing data in one cell to a range of cells
          Olly Bond

          Hello Sheridan,

           

          There's a lot you could do in the report window if your data comes that way. If you're working only in the table window, then this might need two passes. Please could you let us know what format data you're working with, and ideally post a fragment between and tags, without the spaces?

           

          Best wishes,

           

          Olly

            • Comparing data in one cell to a range of cells
              SheridanBI _

              Type     Count                        Formula                       Desired Result

              Set A     5.00     $B2/SUMIF($A$2:$A$5,$A2,$B$2:$B$5)     0.42

              Set A     7.00     $B3/SUMIF($A$2:$A$5,$A3,$B$2:$B$5)     0.58

              Set B     9.00     $B4/SUMIF($A$2:$A$5,$A4,$B$2:$B$5)     0.90

              Set B     1.00     $B5/SUMIF($A$2:$A$5,$A5,$B$2:$B$5)     0.10

               

              With the "Desired Result" column, I am trying to get the percentages based on "Type" criteria. So the first line: Set A with a count of 5 is 42% of the total of the "Count" column that meets the "Type" column criteria of "Set A".