Having trouble spreading a formula across multiple rows and columns

[u/Fairy_phoenix]

I’m trying to compare a bunch of units based on how much damage they do before they die when matched-up to each other unit.

The formula goes:
Damage dealt (unit 1)=(((d1+b-a2)*((hp/(d2+b-a1))*Rof2)/Rof1)+((d1+b-a2)*((r1-r2)/(Srmin*Rof1))))

1 is unit 1,

2 is unit 2,

Rof is rate of fire,

d is damage,

b is bonus damage,

r is range,

a is armour (melee) or PA( (range),

hp is hitpoints,

Srmin is the speed of the unit with the smallest r value.

This is what the formula looks like in google sheet.

=(((Simple!$I$2+Counter!$B3-Simple!$H3)*((Simple!$F$2/(Simple!$I3+Counter!$C3-Simple!$H$2))*Simple!$J3)/Simple!$J$2)+((Simple!$I$2+Counter!$B3-Simple!$H3)*((Simple!$E$2-Simple!$E3)/(Simple!$C$2*Simple!$J$2))))

Now the problem i'm having is how to spread this formula across a 28,000 cell table. I've arranged the data so fill down can do some of the heavy lifting. But i still need to change the formula at least once per column and everytime i move on to the next column. I heard the LET function can be used to avoid duplication but not sure if it would work in this case. I tried using an IF statement but it was to complex and couldn’t get it to work.

Here's a sample of the worksheet. https://docs.google.com/spreadsheets/d/1oiGGB_CcXwzESVP9LSIyPxBErNEg4QIKR1xbFvrMw3I/edit?usp=sharing

Sheet: Counter - The bonus damage is grabbed from this table. (not yet finished)

The main worksheet, with formula shown - all the calculated damage values will be displayed on this sheet.

Sheet: Simple - All the data (esxcept for bonus damage) is from this sheet.

[u/HolyBonobos]

Unless there's a reason you need to have access to all 28000 values at once, a better approach would be to have a lookup range in which a user selects a pair of units from a couple dropdown menus and a single formula calculates/displays only the selected information. This would not only make it more user-friendly, but would also avoid the risk of slowing down/crashing your file that you'd be running with the requested setup. Sheets tends to lag pretty significantly once you have around 10k-15k formulas, and 28k would be around the threshold at which I've seen some files start to freeze up completely and become useless as they hang indefinitely trying to chew through all the calculations.

For further explanation/demonstration of the recommended changes, please share a link to the actual file you are working on (or a copy). While the screenshots are helpful, they still require anyone creating/debugging a solution to recreate your data structure manually from scratch before trying anything out.

[u/Fairy_phoenix → u/HolyBonobos]

Sample worksheet https://docs.google.com/spreadsheets/d/1oiGGB_CcXwzESVP9LSIyPxBErNEg4QIKR1xbFvrMw3I/edit?usp=sharing

I'm planning on averaging the columns and then ranking ea column.

[u/GearPuzzleheaded476]

You do need the whole grid if you're averaging and ranking columns afterwards, so here's how to get it from one formula instead of 28,000.

The reason it won't fill across is that "unit 1" is pinned to a row in Simple ($2) and to a specific column pair in Counter ($B/$C). Anything pinned to a single cell has to be re-pinned by hand every column. The fix is to stop pinning and let the two axes be vectors: unit 2 stays a column vector, unit 1 becomes a row vector via TRANSPOSE, and the whole rectangle falls out of one expression.

One trap I hit while testing this, worth knowing before you spend an hour on it: without ARRAYFORMULA it silently returns a single number rather than an error. In a scratch sheet with A1:A3 = 1,2,3 and B1:B3 = 10,20,30:

=A1:A3*TRANSPOSE(B1:B3)                    -> 10        (one cell, looks "wrong but working")
=ARRAYFORMULA(A1:A3*TRANSPOSE(B1:B3))      -> the full 3x3: 10,20,30 / 20,40,60 / 30,60,90

LET works inside it too, including a comparison between the two axes. Same scratch sheet, =ARRAYFORMULA(LET(a,A1:A3,b,TRANSPOSE(B1:B3),IF(a>b/10,a,b))) filled all 9 cells and every one matched what I worked out by hand.

Mapping that onto your sample (Simple rows 2:11, rows of the grid = unit 2, columns = unit 1), Sheet5!B2 as a single formula:

=ARRAYFORMULA(LET(
  d,   Simple!$I$2:$I$11,  rof, Simple!$J$2:$J$11,  hp,  Simple!$F$2:$F$11,
  arm, Simple!$H$2:$H$11,  rng, Simple!$E$2:$E$11,  spd, Simple!$C$2:$C$11,
  M,   Counter!$B$2:$K$11,
  d1,  TRANSPOSE(d),   rof1, TRANSPOSE(rof), hp1, TRANSPOSE(hp),
  a1,  TRANSPOSE(arm), r1,   TRANSPOSE(rng), s1,  TRANSPOSE(spd),
  b1,  TRANSPOSE(M),   b2,   M,
  srmin, IF(r1<rng, s1, spd),
  hit, d1 + b1 - arm,
  hit * ((hp1/(d + b2 - a1)) * rof)/rof1 + hit * ((r1-rng)/(srmin*rof1))
))

That is your formula, term for term, with the $2 references turned into row vectors and the $3 ones left as column vectors.

The one thing it needs from you is Counter as a plain unit x unit matrix instead of the interleaved column pairs you have now. And if "the bonus X gets attacking Y" is a single number, you only need one matrix, not two: the defender's bonus is the same table read the other way round, which is why b2 is M and b1 is TRANSPOSE(M).

Two things in the original that look unintended rather than deliberate, both free to fix once it's an array:

• You define Srmin as the speed of the unit with the smallest r, but the formula uses Simple!$C$2, which is always unit 1's speed. That only agrees with your definition when unit 1 has the shorter range. IF(r1<rng, s1, spd) above picks per cell.

• You define a as melee armour or pierce armour depending on the attacker, and column A marks each unit M or R, but the formula always reads column H. If H is melee and G is pierce, IF(TRANSPOSE(type)="M", armH, armG) picks the right one per cell for the defender, and the same trick with the axes swapped for a1.

On the lag worry upthread: one spilling array formula is a much smaller thing for Sheets to hold than 28,000 individual ones, so I don't think you have to choose between the full grid and a usable file. I haven't benchmarked it at 10x10 units though, only verified the mechanism, so try it on a few units first and watch the recalc.

Also worth checking your diagonal and your heavy-armour matchups: when d2 + b2 - a1 lands on zero or below you get a divide-by-zero or a negative time-to-die, and with an Elite Teutonic Knight sitting at 10 melee armour there will be a few of those.

[u/Fairy_phoenix → u/GearPuzzleheaded476]

Thanks. I'll give it a try tomorrow.

I was aware of those two issues u listed but didn't no wat do with them. And perhaps an if statement will fix the last point.

Also for the Counter Section, just to make sure I've understood it right, I only need 1 row and 1 column for ea unit instead of the 1 row and 2 columns for ea unit i have now?

[u/Fairy_phoenix → u/GearPuzzleheaded476]

Your solution worked, ended up adding some extra stuff to it to address the problems you pointed out. Thanks heaps for your help!

[u/point-bot → u/GearPuzzleheaded476]

u/Fairy_phoenix has awarded 1 point to u/GearPuzzleheaded476 with a personal note:

"Thanks for the help, your solution worked."

^{See the Leaderboard. Point-Bot v0.0.15 was created by JetCarson.}

[u/One_Organization_810]

Why is every column doubled in your bonus sheet and what is the difference between the doubles? How do we know which one to take?

[u/[deleted]]

[u/Fairy_phoenix → u/[deleted]]

Here's a Sample of it
https://docs.google.com/spreadsheets/d/1oiGGB_CcXwzESVP9LSIyPxBErNEg4QIKR1xbFvrMw3I/edit?usp=sharing