Hi Peter,

Welcome to our forum! I've moved this post from the public Dashboards & Charts forum to the private Dashboard course members forum. This will ensure you get priority support from me and keep our course materials only accessible to those who've paid for them.

In the file you attached there are formulas for those with 365 and those with 2019 and earlier. e.g. on the Sales Bar sheet columns A:F are for 365 and columns H:J are for 2019 and earlier.

There are orange comment boxes on the 'Sales Line & Column charts' sheet that explain the differences for 2019 and earlier users.

I'm not sure what you mean by 'I'm not able to get the file to work'. As long as you don't edit the formulas they will retain the values as calculated when I created the file. This file is designed to be a reference for you that you can use to troubleshoot if you get stuck when practicing.

I hope that clarifies things, but please come back if you have further questions.

Mynda

Hi Mynda,

Thank you for the quick reply. To explain the issue better, I have taken two screenshots of the messages that appears when I open the file, see the attached word file.

I´m not able to get the file to work on my computer with Excel 2019. I am aware that the new formulas for version 365 does not work for me, but the older versions and formulas should work. When I look in the cell area for version 2019 and 2016 it seems that they have some of the now 365 formulas or are linked to the formulas in the cell area where you have used the new 365 formulas.

Please, get back.

/Peter

Hi again Mynda,

I also took a screenshot from the worksheet "Sales Line & Column Charts"

Hi again Mynda,

I took a new screenformshot from another worksheet, please see the attached file. I do not recognize the formulas for version 2019 and earlier. i e SORT and UNIQUE that are used together with the "old" formulas.

/Peter

Hi Peter,

None of the files are attached that you mention. Remember to click the 'Start Upload' button after selecting your file and wait for the green text notification below the attachments pane to let you know when the upload is complete.

Mynda

Hi Mynda,

Thank you for your help, ok, I will try again. I attach all 3 word files. It seems to work this time.

/Peter

Hi Peter,

Thanks for sharing the screenshots. The formulas should still show the results as long as you don't edit them. Anyhow, I've attached a file here that has all the 365 formulas removed so it should work on your PC.

Let me know if you have any problems.

Mynda

Hi Mynda,

Thank you for the service and quick replies! Yes, the file is ok now and seems to work perfectly. Please, notice that I have not tried to edit the formulas and the error message as you can see in the word file "errors in file" showed up directly after open the file. I had to press ok and then the error messages in cells showed up.

/Peter

Thanks for confirming, Peter. I can only think there is something throwing Excel 2019 off that's causing these errors. Glad the new file is working for you now.

Hallo Maynda, 

I too am receiving an Excel generated error messages when trying to write the following formulas (Note I created my own sheet hence the cell references are slightly different to those in the tutorial)

1. =SORT(UNIQUE(Sales[[Category]:[Product]]), {1,2})

2. =IF(A2=A1,"",A2)

Not sure if this is the same as Peter Lindstrom's errors in this thread. I am running Excel in MS_365. Screen prints of error messages are attached and  MS Account info

Thanks

Alistair

oops, typo, meant Mynda, Maynda sorry !!!

Hi Alistair,

That looks like a bug! I'd send a frown to Microsoft by clicking the Feedback icon on the Help tab of the ribbon, or the smiley face in the top right of the screen if you have it.

Be sure to include a screenshot so they can see the problem, but also when you include screenshots, even if they're blank, they get given priority. 

Mynda 

Hi Mynda,

Thanks for your reply. I did as you suggested and sent the error to Microsoft via the feedback option in the ribbon.

However I may have discovered why the error message is displayed. There is a difference between the formula used in the Tutorial 3.01 Formulas for Analysis & the formula used in the Practice sheet 3.0. The Tutorial uses a comma & the practice sheet uses a semi-colon. It applies to both the =IF & =FILTER formulas. In the Tutorial the =IF(is timed at 0:4:07) & the =FILTER(is timed at 0:4:55) When I use the ; the formula works correctly. Please see the attached screen shots.

Alistair

Ah, Alistair I presume you are somewhere in Europe, or at least in a region that uses semi-colons where I would use a comma. When you open a file containing formulas entered with commas it will automatically convert it to the correct format for your region. When you watch my tutorials you need to keep in mind that when I use a comma you should use a semi-colon. Therefore, there is no bug in Excel, it's just that you have different location settings to me.

Hope that clears it up.

Mynda