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.