Pages

▼

Monday, November 5, 2018

Any Clever Excel Solution?

I have figured out how to extract total number of incidents by decade, by cause, by weapon type.  Now I want to extract the number of dead by weapon type.  Here are the columns that I have:
There is a 1 in every weapon category in which a weapon of that type is known to have been used.  In many lines there is a 1 in more than one weapon column.  I would love to extract the total dead (column H) by weapon type where there is one only weapon type marked. 

If not possible for all weapon types, then extracting number of lines and total dead for every line where EXPLOSIVE or ARSON is the only weapon type.  (I am pretty sure that these two weapon types are very disproportionately responsible for the deaths per incident.  Things like bombing an airliner, or burning a bar or private school--93 dead--produce a lot more deaths than axes and hatchets: even mass murderers get tired from swinging something heavy and slippery.)
at

1 comment:

  1. ChuckCNovember 5, 2018 at 8:12 PM

    You might consider putting this into a real database (even microsoft access).

    The most straight forward way I see to do this in excel is to repeat the weapons columns once.

    Assuming Farmington in example is row 2, and AG is the first blank column after all your data:
    formula in AG2 =sum(k2:ac2) // this will give # of weapons used
    now repeat all weapon columns, AH to AZ (i.e fill-right AH to AZ)
    formula in ah2 is =if($ag2>1, 0, $h2*k2) // if multi-weapons ->0, else-> num_dead * (0 or 1)
    this will give num_dead for one weapon, 0 for non-used weapon, and 0 if more than one weapon

    Fill these down. Sum each column from row 2 to end should give totals by single-weapon-only

    ReplyDelete