Four variables dynamic table with formulas in the cells to find average and total # cells

I am creating a tool to help me with a game that I am playing. I want to be able to see how increasing or decreasing certain statistics on my character favour or disadvantage me against other opponents in order to spend in-game currency on improvements to the abilities of my character in the most efficient and effective manner.

One aspect of the game is applying and receiving damage to and from an opponent. The applied damage is not fixed, but variable between two numbers (for example 220-256). The damage that the game randomly generates between those two numbers is mitigated by a second set of numbers similar to the first (for example 45-73). That number is also random. I want to be able to come up with an average of the total of every combination possible. This means that I would have a table (using the examples above) that is 37 x 29. This would not be hard if I only ever cared about one opponent, but I make comparisons to many different opponents throughout the game and the table sizes change every time a delta in damage and/or mitigation occur.

Variables

Lower Damage (LDmg) min 1
Upper Damage (UDmg) max 2000
Lower Absorb (LAbs) min 1
Upper Absorb (UAbs) max 1000

Table
number of Columns = UDmg - LDmg + 1 = x
number of Rows = UAbs - LAbs + 1= y

The top left cell contains a formula that results in an answer equal to LDmg-LAbs
The bottom left cell contains a formula that results in an answer equal to LDmg-UAbs
The top right cell contains a formula that results in an answer equal to UDmg-LAbs
The bottom right cell contains a formula that results in an answer equal to UDmg-UAbs


Restrictions

x < 200
y < 200
Negative values become zero (one cannot apply negative damage).

Example 1

Inputs

UDmg=20
LDmg=16
x=5
UAbs=5
LAbs=4
y=2

Table would look like

11 12 13 14 15
12 13 14 15 16

Outputs

Average 13.5

Number of cells would be x * y = 10

Example 2

UDmg=40
LDmg=30
x=11
UAbs=36
LAbs=31
y=6

Table would look like

0 0 1 2 3 4 5 6 7 8 9
0 0 0 1 2 3 4 5 6 7 8
0 0 0 0 1 2 3 4 5 6 7
0 0 0 0 0 1 2 3 4 5 6
0 0 0 0 0 0 1 2 3 4 5
0 0 0 0 0 0 0 1 2 3 4

Average 2.345
Number of cells would be x * y = 66

SOLUTION WAS to create a 200 x 200 table with conditional IFs that made the cells blank when others were blank or zero.

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet.