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

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.

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

      Random_User1 

      Thank you for the update. Bit more cosmetic

      - formula could be shortened to

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

      above is for D30

      - to differentiate blank cells and zero formula for conditional formatting rule could be

      =(D$30=0)*NOT(ISBLANK(D$30))

      instead of Cell value=0

       

      - I'd don't use merged cells for I29:K29 and I30:K30, Center across selection instead

      Above is for Windows, I guess for Mac is similar

      When you may drag formula from D30 till end of the row. For I30:K30 it will appear as

      just Del on J30 and K30 and it become

Resources