Forum Discussion

Random_User1's avatar
Random_User1
Copper Contributor
Dec 06, 2019

Help with complex formula

Hi. I'm in some need of help with some formulas that I've pretty much given up on and I was hoping for some suggestions. Attached are two screenshots of two sheets in a spreadsheet I'm working on; I ...
excel
Excel on Mac
Formulas and Functions
Random_User1's avatar
Random_User1
Copper Contributor
Dec 06, 2019
Thanks, Sergei; but I'm getting a Value Not Available Error on that one I'm afraid.
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Dec 06, 2019

Random_User1 

Could you submit sample file removing all not needed information?

  • Random_User1's avatar
    Random_User1
    Copper Contributor
    Dec 06, 2019

    I seem to have found the problem when putting the sample together. The original sheet starts with a Pokemon with only one type, cell D9 is left blank, filling in this cell with a second type makes the formula work but it continues to fail on ones with single types. It would be very handy if the formula could work with both of these scenarios, single and dual type, rather than having one formula for each, though I'm not sure if that's possible without modifications. In any case, sample attached.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Dec 06, 2019

      Random_User1 

      Thank you for the file. For cell N30 formula is

      =IFERROR(
    INDEX('Type Match-Ups'!$C$3:$U$21,MATCH(N$29,'Type Match-Ups'!$B$3:$B$21,0),MATCH($C$22,'Type Match-Ups'!$C$2:$U$2,0))*
    INDEX('Type Match-Ups'!$C$3:$U$21,MATCH(N$29,'Type Match-Ups'!$B$3:$B$21,0),MATCH($D$22,'Type Match-Ups'!$C$2:$U$2,0)),
"===")

      and drag it to the left and right.

      Please check attached.

      • Random_User1's avatar
        Random_User1
        Copper Contributor
        Dec 06, 2019

        Thanks very much, Sergei; the formula works perfectly. I'd managed to work around the issue with single-types as well, I'd filled the blank type box with a white-fonted N/A and added a hidden N/A row and column to the Type Match-Up chart with a value of 1 across the board; editing the formula to include this extra row and column now has it work exactly the same in every case. The ??? boxes had to also be renamed just to ? for some reason as it made the formula behave somewhat temperamentally. I have my doubts anyone would need something like this, but reattaching the sample with successful edits for anyone who might find it useful.

Resources