Android Question Create A Formulae In Excel Spreadsheet

Big Dave · Apr 5, 2014

I am creating an excel spreadsheet which will eventually be emailed as an attachment. Within the spreadsheet I want to add a formulae to sum a group of fields. No matter what I do the field is always set to 4 when I view it in the email?

B4X:

    Dim strY As String
    strY = x
    SummaryCell.InitializeFormula(6, x+1, "sum(G1:G" & strY & ")")
    SummarySheet.AddCell(SummaryCell)
    Log( "sum(G1:G" & strY)

x is the end value from a for/next loop and G1 to Gx are the cells I want to sum.

B4X:

        SummaryCell.InitializeNumber(6, x, strSummTotalMiles)
        SummaryCell.SetCellFormat(SummaryCellFormat1)
        SummarySheet.AddCell(SummaryCell)

The above code puts the values in the "G" column

Not sure what I am doing wrong or even if this formulae can be used?

Thanks.

Erel · Apr 6, 2014

InitializeFormula should work.

Try to create a very simple project that only adds a few cells and sets the formula. If it doesn't work for you then please upload the project.

Big Dave · Apr 6, 2014

Erel

Still no joy on this. Still puts "4" in the sum cell.

I have attached a simple program as suggested. You will need to set the email address and postcode to something appropriate to you.
Additional Info.. When I download the spreadsheet the cell contains "#N/A" and gives an error message saying "A value is not available to the formula or function" . Up until now I have been looking at it directly via the email.

Many thanks.

Erel · Apr 6, 2014

This works:

B4X:

sb.Append("SUM(")
   For i = 1 To 5
     If i > 1 Then sb.Append(",")
     sb.Append("Test!G").Append(i)
   Next
   sb.Append(")")
   tc.InitializeFormula(6, 6, sb.ToString)

See the discussion here: http://stackoverflow.com/questions/...mula-like-sum-avg-stdev-returns-value-when-it

Big Dave · Apr 8, 2014

Erel

Applied the changes you have suggested and finally got the correct totals being reported... sort of...

As the spreadsheet is sent as an attachment to an email it is quite possible for the spreadsheet to be viewed online, certainly for Outlook users anyway. When viewed online the cell still contains "4" but if you download the spreadsheet and look at it directly via Excel the cell contains the correct calculated value. So to ensure the user will always see the correct value I will have to do all the calculations within the program. Not a major problem but it would have been nice for Excel to do some of the work.

Thank you for all your help.

David

Android Question Create A Formulae In Excel Spreadsheet

Big Dave

Member

Erel

B4X founder

Big Dave

Member

Attachments

Erel

B4X founder

Big Dave

Member

Similar Threads

Android Question Create A Formulae In Excel Spreadsheet

Big Dave

Member

Erel

B4X founder

Big Dave

Member

Attachments

Erel

B4X founder

Big Dave

Member

Similar Threads

Privacy & Transparency

Privacy & Transparency