Formula Errors

Question

&nbsp;I was creating a spreadsheet and used the SUM function to total up the columns individually as well as link it to a main cell to show the total of all columns. However, it gave me calculation errors. So, I deleted all the formulas. Yet, now it gives me a formula error "inconsistent calculated column formula" whenever I type any number in it. Is there a fix for this instead of having to ignore every rows error?&nbsp;Here's a copy of the Spreadsheet&nbsp;Here's a video of the error.&nbsp;https://netorgft9900763-my.sharepoint.com/:v:/g/personal/njhudsonjr_shareholdersrma_com/EWkepMHLa61PnZpUf-Zn-eIBHmfUgcXzOjm_VP0W7QKR1g?e=8bsQUA&nbsp;&nbsp;

joeuser2004 · Answer

ShareholdersRMA&nbsp; wrote:&nbsp; ``Here's a video of the error``&nbsp;"If a picture is worth 1000 words, an Excel file is worth a 1000 pictures".&nbsp; (wink)&nbsp;Please attach an Excel file that demonstrates the problem(s), or provide a link to the Excel file that you uploaded to a file-sharing website that does not require that we log in to download the file.&nbsp;-----ShareholdersRMA&nbsp; wrote:&nbsp; ``used the SUM function to total up the columns individually [....] it gave me calculation errors``&nbsp;Often, the devil is in the details that we must see and access ourselves. That is why we require an Excel file that demonstrates the problems.&nbsp;But for starters, it might help if you:&nbsp; (a) describe the calculation errors ("I expect this, but it returns that"); and (b) show us the formula(s).&nbsp; Bee sure to show values with 15 significant digits.&nbsp;-----ShareholdersRMA&nbsp; wrote:&nbsp; ``it gives me a formula error "inconsistent calculated column formula" whenever I type any number in it. Is there a fix for this instead of having to ignore every rows error?``&nbsp;Yes.&nbsp;First, those are not "errors".&nbsp; They are warnings.&nbsp;Excel is merely saying that adjacent columns have formulas or data of one form, and this cell has formulas or data of a different form.&nbsp;Excel is often wrong insofar as the differences are intentional.&nbsp;If you find such warnings distracting (I do!), I recommend disabling "background error checking" altogether.&nbsp;The navigation to the option might vary with newer versions of Excel.&nbsp; In my version, I click File &gt; Options &gt; Formulas and remove the checkmark next to "Enable background error checking".&nbsp;Also click "Reset Ignored Errors" to remove all existing warnings.

joeuser2004 · Answer

ShareholdersRMA&nbsp;&nbsp;The problem is:&nbsp; the Show Formula option is set.&nbsp;&nbsp;&nbsp;You can deselect the option by clicking Show Formula or by pressing ctrl+` (back-quote).&nbsp;&nbsp;

riny_van_eekelen · Answer

ShareholdersRMA&nbsp;The cell where you entered 092321 is formatted as a date. Excel drops the leading zero and returns a date based on the value 92321, which happens to be October 5, 2152.&nbsp;If you want the cell to display exactly what you enter, format it as a text en re-enter 092321&nbsp;

joeuser2004 · Answer

ShareholdersRMA&nbsp; wrote: ``When I put 092321 it comes back as 10/05/52``&nbsp;Because the F31 is formatted as Date, and the title in F27 is Estimated Complete Date.&nbsp;I was wondering why you are entering 92321, in the first place.&nbsp; And I was wondering why you are entering a 2152 date.&nbsp;But that's your business.&nbsp; I cannot help you with that.

hansvogelaar · Answer

ShareholdersRMA&nbsp;
Assuming that you wanted to enter the 23rd of September, 2021, you must indicate to Excel in some way that you want to enter a date:
9/23/21 or 09/23/2021 or 23-Sep-21
It is possible to make Excel convert 092321 to the date 09/23/2021, but that would require VBA code.

Forum Discussion

Formula Errors

Share

Resources