Text in a single cell based on logic of multiple other cells

[dtrimble]

Good morning,

In the attached Excel document, I'm working on some analysis of voter registration data in California's 58 counties. I have all the data broken down by party registration, etc. with a separate row for each county.

The percentage values found in the ranges N4:N61 are the total % of registered Democrats in each county. P4:P61 hold the total percentages for Republicans. AF4:AF61 are Independents. And AD4:AD61 are for all 3rd parties combined.

I have a column - A4:A61 - labeled the "Majority Meter". I'd like the text of that cell to be either "Democrat", "Republican", "Independent", or "Other" depending on which category holds the majority number of voters for that row's county. That means some IF logic based on multiple tests, and that's where all my formula attempts keep falling apart with any number of syntax errors.

I know it needs to compare the percentage of each of the ranges specified above to test for which is largest, but it's not working out.

Any suggestions would be appreciated. Document with data is attached in XLSX.Voters & Jurisdictions by County - Updated January 2012.xlsx Using Excel for Mac 2011 and 2010 for Windows.

Many thanks,
dt

[Pete_UK]

Put this formula in A4:

Please Login or Register  to view this content.

then copy down.

Hope this helps.

Pete

[dtrimble]

Pete,

That worked magically. I'm just amazed every time I learn new things in Excel; it's amazing what it can accomplish when you know it well enough. Thanks so much for the help on this!

I do have one more question; more an extension of the one above.

I have added conditional formatting to the A column. It colors the cell red for Republican, blue for Democrat, and leaves it uncolored/normal for anything else. Easy enough. But to finish it, I would like to add an additional rule that colors it purple if the difference between Democrats (N4:N61) and Republicans (P4:P61) is less than or equal to a certain amount -- let's say a maximum 10% difference. OR if Independents (AF4:AF61) are greater than, say, 30%.

I'm thinking I'd have to do this with VBA, but before I go coding it, is there a reasonably straight forward way to do this with conditional formatting rules using a custom formula?

dt

[FDibbins]

Pete is off-line right now, and without digging too deep into what you are doing, what you want sounds like a fairly simple task for CF. To begin with, a quick tip - I always create all but the simplest of CF in the sheet itself, to save a little effort of keep going into CF to modify the formula if it's not giving what you want. You create it so that it gives a TRUE or FALSE answer. So try something lie this and see how far you get...

=(sum(N4:N61)-sum(P4:P61)/sum(P4:P61)>=0.1

not sure what you want to compare the independents to, but the concept above should help you

[dtrimble]

Hmmm. Getting closer, but I'm not trying to do the comparison on the entire range (the sums in your formula). It's on each individual row. So...and just thinking "out loud" here...for row 4, I'd be trying to determine if the difference between N4 and P4 is <= 10.00%, ----OR---- if AF4 > 30% (with no comparison), and color accordingly.

[dtrimble]

Problem solved. I used a different approach, using MAX() and MIN() in the formula and a conditional formatting rule.

Thanks for the help!

[FDibbins]

happy to have helped, and even happier that you figured it out for yourself