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

# Comparing data in one cell to a range of cells

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

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

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".

• ###### Comparing data in one cell to a range of cells

Build a summary with Set Name as the first key field, and the Count field also as a key field. I know, weird. :confused:

Now add the Count as a measure, and double click the measure field to open the properties dialog. On the Calculation tab, select the Percent of Subtotal option.

You don't actually need to have the Set Name field display a subtotal amount in order to make the calculation work as you need. The end result is:

Set Name    Count    Percent

Set A           5    0.42

Set A           7    0.58

Set B           1    0.1

Set B           9    0.9

/code

How's that?

• ###### Comparing data in one cell to a range of cells

This does the trick. I appreciate it, Data Kruncher.

• ###### Comparing data in one cell to a range of cells

Great! Thanks for the update.