I'm surprised that most people seem to agree that this is interesting or that it could be considered a bug. It seems reasonable to assume that using formulas that reference cells that change between steps in the table is risky without at least testing. Further, maybe I've spent too long with Excel but the way it works is exactly as I would expect. I wouldn't want to rely on it because the way that multithreading works could change in a way that changes results. Regardless, great video as always and interesting discussion being stirred up.
If you think about how it does the calculations it doesn't seem buggy, but if you think about reading the output, I think it's more clear than there's an issue. The way I would normally read a data table (and tell others to) is 'if this value was put in then this is what the value in would become'. But in this case, that's not a correct reading. If it updated the calculated input values to be consistent with the data table output, that would be something. But as it is, it appears to be saying 'if sales are 1,800, this is net profit' but is actually showing you the net profit if sales are 1,980. 'Wouldn't want to rely on it' is spot on - it's scary to use a black box function when you're less than 100% clear on what it's really doing.
This is a fairly serious bug. But, it does explain why you get unexpected calculations if the replacement values aren't hard-coded. I think this is something that the Excel team should fix 😅
You nailed it - I realized later that I may even have used it myself in a ModelOff question once. Dealing with bugs when you’re deeply committed to backward compatibility is a minefield! 😬
Interesting, and a good warning for MEWC cases! Agree with @sledgehammer-productions comment, this feels like a circular reference as the Data Table is modifying cell D14 with inputs that rely on D14 itself, so also agree that Excel should throw a warning if this happens. I normally have a separate "Adopted" value for each (key) input assumption, and then add adjustments or sensitivity there (and then Data Table the sensitivity amount, which by default is zero, but scenarios of +/-10%).
That's a good workaround, especially if it's a model you want to run lots of sensitivities on. It does take something away from the plug-and-play appeal of a data table if you have to set something up for it though... I think you're pretty safe on this one for the MEWC - it's usually just a straight game value there, so not much risk of falling into this trap!
feels just like a circular calculation but without the warning that Excel gives you when that happens. I've only used data tables in MEWC Battles to go through the games. Ok, and once to go through a number of prepared invoices, just a list from 1 to 120, to see if the check-sum checks out.
I think this is technically corkscrew rather than circular - the input for row 2 depends on how the sheet is modified by row 1, but it doesn't iteratively update row 1 based on changes from row 1. But I agree it probably needs a warning! I don't use them as much in MEWC any more (probably not quite as much as I should, really...), but that's still probably where I do the majority of my data table-ing : )
You missed this because you had no measurement of the number of iterations required to solve. Best to hard code a base value and calculate the increases/decreases on that value. With severe time constraints you had to build a shortcut back of a cigarette box model.
That is interesting. I don't know about this. my guess is formula at d66 is refer to C69 if C69 is just =D14 and C70 is =D14*1.1 and add C71 is =D14*0.9 would that help?
I think the issue is that it modifies the spreadsheet with the value from one row, then reads the value of the next row from the modified spreadsheet. So any time the new input value depends on the cell being modified, the value used when you come to do the calculation (with the previous modified value loaded) won’t be the same as the value it looks like its using. That said, this is just a working hypothesis for now… I’ll give your suggestion a try when I’m at a computer later.
Sorry to hear it! It might be worth slowing it down or trying the subtitles. I can make it out OK, but I also know my own voice and speech patterns better than you do. I could probably do with a better mic, but honestly I think the real challenge is that I think faster than I can speak, so I’m probably prone to mumbling… but that one doesn’t have a simple tech solution! 😬
@@DimEarly sorry Dim..never ment to find fault just trying to get what you are saying..iam a great admirer of your content and you are my inspiration..please continue enlighting us with you channel..thanks a lot.
@@DimEarly There's free, well known audio editing software called "Audacity". You could get better and better audio quality by sinking increasing amounts of time into it, but the basic "Normalization" and "Noise Reduction" effects go a long way. You'd learn them both within an hour and they would add a few minutes to the video production workload.
