B. Clay Shannon February 2016

What is wrong with this formula that causes me to see the green triangles in the Excel spreadsheet?

I'm getting the green triangles in my Excel spreadsheet indicating that there is something wrong with the formula underlying that cell, although the data in that column's cells seems right and correctly formatted.

The code I'm using to populate that column is:

var avgWeeklyDeliveriesCell = (Excel.Range)_xlSheet.Cells[curDelPerfRow, 
    AVG_WEEKLY_DELIVERIES_COLUMN];
avgWeeklyDeliveriesCell.Value2 = string.Format("=ROUND(AVERAGE(C{0}:I{0}), 2)", 
    curDelPerfRow);
avgWeeklyDeliveriesCell.NumberFormat = "#,##0.00";

The values in that column are 3.43, 1.57, 2.00, etc.

What is the problem with this formula ("=ROUND(AVERAGE(C{0}:I{0}), 2)")? It seems to work, so why are the green triangles making their presence visible?

UPDATE

I added this code:

_xlApp.ErrorCheckingOptions.InconsistentFormula = false;
_xlApp.ErrorCheckingOptions.BackgroundChecking = false;

...but it makes no difference; I still see the little green meanies.

UPDATE 2

I know for approximately 99.97% certain that the warning is bogus (after all, the values are correct), yet I can't turn it off, as noted previously.

Why am I so sure my formula is good? Note this screamshot:

enter image description here

The msg that I see when I click the green triangle and hover over the sign with the green exclamation mark is, "The formula in this cell refers to a range that has additional numbers adjacent to it"

The formula displaying is "=ROUND(AVERAGE(C10:I10), 2)" which is what I want - an average of "Sun Orders" through "Sat Orders", but apparently due to there being another column ("Total Orders") situated between those computed and the one that complains greenly, it think

Answers


LessNoviceProgrammer February 2016

The issue is that the "Total Orders" column is a set value, not a formula. Set the "Total Orders" column to:

=SUM(C10:I10)

Sample excel image showing the inserted value vs. formula

Your warning will go away. Your average formula is correct, Excel is just looking out for you.

Post Status

Asked in February 2016
Viewed 2,211 times
Voted 8
Answered 1 times

Search




Leave an answer