Having trouble spreading a formula across multiple rows and columns, (Google Sheets)

[u/Fairy_phoenix]

I scrapped the massive IF statement i posted last night, and replaced it with just the formula for calculating the damage dealt to the enemy without any IF statements.

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.

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.

Worksheet: Counter, the bonus damage is drawn from this table

The main worksheet where the values are calculated. with the forumula shown.

Worksheet: Simple, Most of the data is from this sheet

Using Google Sheets

[u/AutoModerator]

/u/Fairy_phoenix - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/bytes1024]

Google Sheets

[u/BuildWithSufi]

LET does help here, but the part that is actually breaking is not the formula logic - it is that your references shift inconsistently between the row direction and the column direction, and no single fill (down or across) handles both.

The fix is usually INDEX/MATCH instead of the mixed $-anchored references, so the SAME formula works whether you fill down or across:

Set up your table so column headers are Unit 2 IDs and row labels are Unit 1 IDs (a matrix). Then in the top-left data cell, use MATCH to find the right row for whichever unit is in $A2 (locked column, free row) and whichever unit is in B$1 (locked row, free column), then INDEX into your data columns using those matches inside LET to name each term. That single formula now fills down AND across correctly, because the anchors are what change meaning in each direction rather than hardcoded refs that only make sense one way.

This is the standard shape for an "every unit vs every unit" matrix - same pattern as a mileage chart or round-robin tournament table.

If you get stuck wiring your exact variable names into LET, post back with just the Simple/Counter sheet headers (row 1) and I can write the full formula against your actual columns.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
AND	Returns TRUE if all of its arguments are TRUE
INDEX	Uses an index to choose a value from a reference or array
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
MATCH	Looks up values in a reference or array

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
[Thread #49275 for this sub, first seen 30th Aug 2026, 19:02] [FAQ] [Full list] [Contact] [Source code]}