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 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