Forum Discussion
Unable to Figure Out Reason For An Error
jkuchman I noticed the "@" towards the end of the formula in Y15 and that made me wonder what you want to do. Removed it and it resulted in a #SPILL error. Then I just spelled out the entire formula below the table on the Hitters sheet, in order to undertand what's going on. I believe you need to replace the part with the "@" by this:
SUM(Hitters!AA11:AA24)/SUM(Hitters!J11:J24)... and then it will work. Deleting data from the Hitters list will now not affect the outcome in Y15. See attached.
The last parts of the formulae in Y15, Y22 and Y23 are the cause the #VALUE! error.
Hitters!$AA$11/Hitters!$J$11Both cells have a "hyphen", so you are trying to divide "-" by "-".
Y16 calls for Y22 (above), thus returns the same error.
I removed the hyphens and it started working! I started playing around with the formula again and I noticed that when I remove the contents from Row 15 on the"Hitters" sheet, my Y15 on the "Overall" sheet changes. This should not be the case since they are all averages and use the same exact numbers. Not sure if you are able to spot why this is. Thanks for your help!
jkuchman I noticed the "@" towards the end of the formula in Y15 and that made me wonder what you want to do. Removed it and it resulted in a #SPILL error. Then I just spelled out the entire formula below the table on the Hitters sheet, in order to undertand what's going on. I believe you need to replace the part with the "@" by this:
SUM(Hitters!AA11:AA24)/SUM(Hitters!J11:J24)... and then it will work. Deleting data from the Hitters list will now not affect the outcome in Y15. See attached.
Riny_van_Eekelen Thank you so much! I still do not see the "@" at the end of the formula on Y15 when I look back at it. I wonder if it has something to do with me being on a Mac instead of PC.
jkuchman No, it's not Mac related. I'm on one as well. The "@"-sign has to do with the new Dynamic Array formulae. I'm no expert on explaining this though. Deleting the symbol made Excel return an array for each value in AA11:AA24 divided by the value in J11, filling 14 cells at once. If your version of Excel supports this, you can see it in M34 on the Hitters sheet.