Compatibility Functions

[Hyperpolymath]

I am using the statistics functions on Excel 2016, which have various items listed as 'compatibility functions', for instance, =mode and =stdev. I wanted to know, if I import an old spreadsheet:

1) Is there somewhere I can find out which of the new functions the deprecated ones refer to? For instance:

a) does =mode become (or act as) =mode.sing or =mode.mult
b) does =stdev become (or act as) =stdev.s or =stdev.p

2) Is there a way of converting all of the functions across the entire workbook/worksheet/in a range to one or the other reliably and automatically? I have spreadsheets that are very large and include functions AND text, so if I were to just do a 'find and replace' I risk changing text that says to users 'mode' to 'mode.mult' which is sort of helpful to me, but will confuse people. However, since they will assume that when I write 'mode' in text for their interpretation, I will always (for me) be talking about results that are =mode.mult or =stdev.s outputs it will be perfectly fine for them not to know the detail. Of course, I need to ensure that this is absolutely going to get it right behind the scenes too, so I don't want any errors on this correction, or it makes the output completely meaningless even though it will look fine!

Thanks so much!

Jonathan

 

[rlv01]

1) See this link

https://support.office.com/en-us/ar...eference-3D03E2D6-8559-4962-B037-58AC27EFA2AD

2) You could code something in VBA, but I doubt it would be worth the effort. Unless there is an accuracy issue affecting your current formulas, I would recommend staying with the legacy function unless you are in the mood for a major revamp. The 'if it ain't broke, don't fix it rule' should be your guide. Remember that if you substitute the new function on a mission critical workbook you cannot just accept Microsoft's word that the new function won't behave differently, you have to validate.