« previous next »
Pages: [1]   Go Down

Author Topic: Funky excel help needed.  (Read 1100 times)

Weirdo

  • Painter of Borth the Magic Puma
  • Catalyst Demo Team
  • Major General
  • *
  • Posts: 40834
  • We can do it. We have to.
    • Christina Dickinson Writes
Funky excel help needed.
« 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!
Logged
My wife writes books
"Thanks to Megamek, I can finally play BattleTech the way it was meant to be played--pantsless!"   -Neko Bijin
"...finally, giant space panties don't seem so strange." - Whistler
"Damn you, Weirdo... Damn you for being right!" - Paul
"...I was this many years old when I found out that licking a touchscreen in excitement is a bad idea." - JadeHellbringer
"We are the tribal elders. Weirdo is the mushroom specialist." - Worktroll

monbvol

  • Colonel
  • *
  • Posts: 13286
  • I said don't look!
Re: Funky excel help needed.
« Reply #1 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.
Logged

Simon Landmine

  • Lieutenant
  • *
  • Posts: 1224
  • Enthusiastic mapmaker
Re: Funky excel help needed.
« Reply #2 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.
Logged
"That's Lieutenant Faceplant to you, Corporal!"

Things that I have learnt through clicking too fast on 'Move Done' on MegaMek: Double-check the CF of the building before jumping onto it, check artillery arrival times before standing in the neighbouring hex, and don't run across your own minefield.

"Hmm, I wonder if I can turn this into a MM map."

Kidd

  • Major
  • *
  • Posts: 3535
Re: Funky excel help needed.
« Reply #3 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.
Logged

Weirdo

  • Painter of Borth the Magic Puma
  • Catalyst Demo Team
  • Major General
  • *
  • Posts: 40834
  • We can do it. We have to.
    • Christina Dickinson Writes
Re: Funky excel help needed.
« Reply #4 on: 22 June 2017, 13:08:32 »
I believe that fixed that part, thanks!
Logged
My wife writes books
"Thanks to Megamek, I can finally play BattleTech the way it was meant to be played--pantsless!"   -Neko Bijin
"...finally, giant space panties don't seem so strange." - Whistler
"Damn you, Weirdo... Damn you for being right!" - Paul
"...I was this many years old when I found out that licking a touchscreen in excitement is a bad idea." - JadeHellbringer
"We are the tribal elders. Weirdo is the mushroom specialist." - Worktroll

monbvol

  • Colonel
  • *
  • Posts: 13286
  • I said don't look!
Re: Funky excel help needed.
« Reply #5 on: 22 June 2017, 13:26:19 »
No problem Weirdo.
Logged

Daryk

  • Lieutenant General
  • *
  • Posts: 37351
  • The Double Deuce II/II-σ
Re: Funky excel help needed.
« Reply #6 on: 22 June 2017, 18:01:32 »
I'd fourth the suggestion, but the issue is already resolved... LOOKUP is a really useful function!
Logged

Feenix74

  • Captain
  • *
  • Posts: 3026
  • Lam's Phoenix Hawks
Re: Funky excel help needed.
« Reply #7 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.
Logged
Incoming fire has the right of way.

The only thing more accurate than incoming enemy fire is incoming friendly fire.

Always remember that your weapon was built by the lowest bidder.

                                   - excepts from Murphy's Laws of Combat

Simon Landmine

  • Lieutenant
  • *
  • Posts: 1224
  • Enthusiastic mapmaker
Re: Funky excel help needed.
« Reply #8 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.
Logged
"That's Lieutenant Faceplant to you, Corporal!"

Things that I have learnt through clicking too fast on 'Move Done' on MegaMek: Double-check the CF of the building before jumping onto it, check artillery arrival times before standing in the neighbouring hex, and don't run across your own minefield.

"Hmm, I wonder if I can turn this into a MM map."
Pages: [1]   Go Up
« previous next »