Excel calculating drop rates

Question

To determine the Drop rates for each item, I want an excel spreadsheet that I can update with new information each time I play. This will allow the results to be more precise.

example data:

Drop # = how many times the item has been dropped

Drop % = the percentage that that item was dropped

The data is in a table, the total value of Drop # is not. Each Drop# should represent an input, so I can add the quantity dropped of each item after I stopped playing.

Answer 1

Each column's "total" row is just the sum of the cells above it. The formulas should be like so:

Cell   Formula
C25    =SUM(C6:C24) 
D25    =SUM(D6:D24)
 . . .

Then, each cell in the second column will be an

When you copy and paste the formula into other cells, Excel will leave that portion of the formula alone since the dollar signs make the cell references absolute rather than relative.

It is not necessary to calculate the sums for this; for example, using B6, =SUM(C6/Z6)/SUM($C$6:$Z$24) would also work. However, I kept it as is since I figured the totals by themselves could be instructive to you.

If you need more or fewer columns, feel free to change the Z, which assumes that your data extends up to column Z.

I believe there is a technique to support any amount of columns, but I'm not certain right now. I'm sure you can find it on this site or Google.

overall drop % (for all data). You calculate it by dividing the sum of that row by the sum of all rows:

Cell   Formula
B6     =SUM(C6:Z6)/SUM($C$25:$Z$25)
B7     =SUM(C7:Z7)/SUM($C$25:$Z$25)
 . . .