Formula Errors

[ShareholdersRMA]

 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?

Here's a copy of the Spreadsheet

Here's a video of the error.

https://netorgft9900763-my.sharepoint.com/:v:/g/personal/njhudsonjr_shareholdersrma_com/EWkepMHLa61P...

[#2]

@ShareholdersRMA  wrote:  ``Here's a video of the error``

"If a picture is worth 1000 words, an Excel file is worth a 1000 pictures".  (wink)

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.

-----

@ShareholdersRMA  wrote:  ``used the SUM function to total up the columns individually [....] it gave me calculation errors``

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.

But for starters, it might help if you:  (a) describe the calculation errors ("I expect this, but it returns that"); and (b) show us the formula(s).  Bee sure to show values with 15 significant digits.

-----

@ShareholdersRMA  wrote:  ``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?``

Yes.

First, those are not "errors".  They are warnings.

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.

Excel is often wrong insofar as the differences are intentional.

If you find such warnings distracting (I do!), I recommend disabling "background error checking" altogether.

The navigation to the option might vary with newer versions of Excel.  In my version, I click File > Options > Formulas and remove the checkmark next to "Enable background error checking".

Also click "Reset Ignored Errors" to remove all existing warnings.

[#3]

I edited my post and added a copy of the Spreadsheet. Thanks.

[#4]

@ShareholdersRMA 

The problem is:  the Show Formula option is set.

You can deselect the option by clicking Show Formula or by pressing ctrl+` (back-quote).

[#5]

Thanks alot!

[#6]

Any idea why it keeps changing the date from what I put in?

[#7]

@ShareholdersRMA 

In Excel, dates are represented by an integer, with 1 representing 1/1/1900 normally.

In Show Formula mode, dates are shown by their integer value.

Similarly, times are represented by a fraction of a day, with 1/24 representing one hour.

In Show Formula mode, times are shown by their decimal value.

Likewise, percentages are shown by their decimal value.  For example, 12.34% is shown as 0.1234.

Etc, etc, etc.

[#8]

Thanks but that doesn't help. When I put 092321 it comes back as 10/05/52

[#9]

@ShareholdersRMA 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.

If you want the cell to display exactly what you enter, format it as a text en re-enter 092321

[#10]

@ShareholdersRMA  wrote: ``When I put 092321 it comes back as 10/05/52``

Because the F31 is formatted as Date, and the title in F27 is Estimated Complete Date.

I was wondering why you are entering 92321, in the first place.  And I was wondering why you are entering a 2152 date.

But that's your business.  I cannot help you with that.

[#11]

@ShareholdersRMA 

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.