BattleTech - The Board Game of Armored Combat

Off Topic and Technical Support => Off Topic => Topic started by: Weirdo on 22 June 2017, 09:01:11

Title: Funky excel help needed.
Post by: Weirdo on 22 June 2017, 09:01:11
I'm trying to add a complex formula into a starship construction spreadsheet, but the formula needed is currently beyond my excel-fu. Can anyone help? The rule is as follows:
Quote
Probes add to the ship's ORAT(offensive rating) as follows:

HULL          DIVISOR
1-3              (Engines+6)*2.1
4-8              (Engines+6)*1.4
9-15            (Engines+6)*1.0
16-24          (Engines+6)*0.8
25+             (Engines+6)*0.7     

In the spreadsheet, the hull value will be in cell AV8, and engine value in cell AV9.

Thanks for any help!
Title: Re: Funky excel help needed.
Post by: monbvol on 22 June 2017, 10:25:20
Easiest way I can think to do that would be to set up a table with the divisor and use a lookup function.

It'll be easier for me to just attach a sheet with how I'd do it than explain.
Title: Re: Funky excel help needed.
Post by: Simon Landmine on 22 June 2017, 10:44:14
Quote from: monbvol on 22 June 2017, 10:25:20
Easiest way I can think to do that would be to set up a table with the divisor and use a lookup function.

It'll be easier for me to just attach a sheet with how I'd do it than explain.

Seconded.
Title: Re: Funky excel help needed.
Post by: Kidd on 22 June 2017, 11:15:59
Quote from: monbvol on 22 June 2017, 10:25:20
Easiest way I can think to do that would be to set up a table with the divisor and use a lookup function.

It'll be easier for me to just attach a sheet with how I'd do it than explain.
thirded.

Built my own Excel Mech-maker spreadsheet, its all a mass of Lookups.
Title: Re: Funky excel help needed.
Post by: Weirdo on 22 June 2017, 13:08:32
I believe that fixed that part, thanks!
Title: Re: Funky excel help needed.
Post by: monbvol on 22 June 2017, 13:26:19
No problem Weirdo.
Title: Re: Funky excel help needed.
Post by: Daryk on 22 June 2017, 18:01:32
I'd fourth the suggestion, but the issue is already resolved... LOOKUP is a really useful function!
Title: Re: Funky excel help needed.
Post by: Feenix74 on 22 June 2017, 19:49:19
Fifth-ed.

The old way to do it before tables and LOOKUP functionality was available would be to use a series of nested IF statements in your formula but that can get very complicated very fast.
Title: Re: Funky excel help needed.
Post by: Simon Landmine on 23 June 2017, 04:38:36
Quote from: Feenix74 on 22 June 2017, 19:49:19
The old way to do it before tables and LOOKUP functionality was available would be to use a series of nested IF statements in your formula but that can get very complicated very fast.

Yep. [winces]

Alternatively, for those feeling especially adventurous (and with some minor programming knowledge), you can use Excel's built-in VBA support, and write a function of your own, probably using a SELECT CASE statement, feeding in the Hull and Engines values and spitting out the ProbeORATBonus.