Cannot got a spreadsheet to function
Results 1 to 3 of 3

Thread: Cannot got a spreadsheet to function
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Dec 2011
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Cannot got a spreadsheet to function

    I am trying to get a spreadsheet working for a game I play which is useful information. The spreadsheet is hosted here and I saved a copy to my own Google Sheets. I wanted to make some changes and add some things but I'm running into issues. There appears to be differences with Google Sheets and Excel and I am not familiar with using Google Sheets at all. When I downloaded the file as an excel document, it came up with errors and a lot cells were displaying errors. I investigated the cells formulas, correcting issues as I went but gave up as I couldn't get to the root of the problem. I suspected it might be something to do with named ranges but honestly I don't know.

    My second attempt, I painstakingly recreated the document from scratch, copying formulas in cells as I went and not using the named ranges. This again came up with errors and I managed to correct most as it seems that the following works in Google Sheets but not in Excel:

    Code:
    =ARRAYFORMULA(IF(COUNTIF(IF(Gear!$A$2:$A$124>0,Gear!M2:M125, 0),"T3")
    I replaced that with this:

    Code:
    =COUNTIFS(Gear!A:A,1,Gear!M:M,"T3")
    I fixed any issues I could, but I hit a deadend when looking at the "Rotation" tab column C. When I evaluate the formula, the cells contain legitimate data for every part of the formula but it displays #VALUE ! unless I select the formula bar and press enter, which returns 0 (not the correct answer when using evaluate formula).

    I thought there was a way to attach a file so I could show where I'm up to with this, but I can't seem to find the button. Alternatively, is there someone where I can upload this and provide a link to?
    Last edited by missing string; Jan 8th, 2019 at 08:05 AM.

  2. #2
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,497
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Cannot got a spreadsheet to function

    That first formula is invalid in any application. There are four opening and only two closing parentheses.

  3. #3
    Board Regular
    Join Date
    Dec 2011
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cannot got a spreadsheet to function

    It was just a snippet of the formula. The full code is this:

    Code:
    =ARRAYFORMULA(IF(COUNTIF(IF(Gear!$A$2:$A$124>0,Gear!M2:M125, 0),"T3") > 3,1.04,1)*B20*(Spells!D6+Spell*Spells!E6)*ShadowDamage*(1-DotMitigation)*Spells!H6*(1-B22))
    The latter half of the formula is the same which is why I didn't bother with it.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •