Cannot got a spreadsheet to function

missing string

Board Regular
Joined
Dec 20, 2011
Messages
53
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:

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,080
Office Version
365
Platform
Windows
That first formula is invalid in any application. There are four opening and only two closing parentheses.
 

missing string

Board Regular
Joined
Dec 20, 2011
Messages
53
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,452
Messages
5,486,991
Members
407,575
Latest member
calc

This Week's Hot Topics

Top