Forum Discussion

jkuchman's avatar
jkuchman
Copper Contributor
Mar 03, 2020
Solved

Unable to Figure Out Reason For An Error

The cells Y15, Y16, Y22 and Y23 are currently displaying errors, and I cannot figure out why they are. I have reformatted the formulas, but I am still unable to figure out why they do not work.
excel
Excel on Mac
Formulas and Functions
office 365
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Mar 04, 2020

    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.

jkuchman's avatar
jkuchman
Copper Contributor
Mar 04, 2020

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.

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Mar 04, 2020

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.