Click here to Skip to main content
15,849,829 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Help... I want to calculate the average of certain cells on selected row and put it to the Average column. Thanks

What I have tried:

try
{
dataGridView1.Rows[5].Cells[0].Value.ToString();
dataGridView1.Rows[5].Cells[1].Value.ToString();
dataGridView1.Rows[5].Cells[2].Value.ToString();
dataGridView1.Rows[5].Cells[3].Value.ToString();
dataGridView1.Rows[5].Cells[4].Value.ToString();
dataGridView1.Rows[5].Cells[5].Value.ToString();
dataGridView1.Rows[5].Cells[6].Value.ToString();



foreach (DataGridViewRow item in dataGridView1.Rows)
{

dataGridView1.Rows[5].Cells[7].Value =
(Double.Parse(dataGridView1.Rows[5].Cells[0].Value.ToString()) +
Double.Parse(dataGridView1.Rows[5].Cells[1].Value.ToString()) +
Double.Parse(dataGridView1.Rows[5].Cells[2].Value.ToString()) +
Double.Parse(dataGridView1.Rows[5].Cells[3].Value.ToString()) +
Double.Parse(dataGridView1.Rows[5].Cells[4].Value.ToString()) +
Double.Parse(dataGridView1.Rows[5].Cells[5].Value.ToString()) +
Double.Parse(dataGridView1.Rows[5].Cells[6].Value.ToString())).ToString();



}

}
Posted
Updated 14-Jul-19 22:30pm
Comments
EntryLevelGuy! 15-Jul-19 0:01am    
This is what i've tried, it's in manual way. Though it works, i need to compute a table with 2000+ rows and 100+ columns. This below, only calculate a single row and 8 column. When i use this, the table was filtered and only showed what i write on "Select column1, column2 ... from". This does not work when the whole table was shown.
BillWoodruff 15-Jul-19 2:10am    
Is the all the Excel data imported into C# as a DataTable ? Or are you importing selected Rows ? Show the code that gets the data.
EntryLevelGuy! 15-Jul-19 2:49am    
Yes, i imported the data from excel and display it on datagridview. But for now, i filtered it and select only some of the column that needed to be computed cause there are also id, name etc. column and since its too many.
try {


string constr = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=" + materialSingleLineTextField1.Text + ";Extended Properties = \"Excel 12.0;HDR =YES;IMEX = 1;\"";
OleDbConnection con = new OleDbConnection(constr);
OleDbDataAdapter sda = new OleDbDataAdapter("Select F12, F13, F102 from [" + comboBox1.SelectedValue + "]", con);
DataTable dt = new DataTable();
sda.Fill(dt);
this.dataGridView1.DataSource = dt.DefaultView;

foreach (DataRow row in dt.Rows)
{
dataGridView1.DataSource = dt;
}

}
catch (Exception)
{
Form3 f3 = new Form3();
f3.Show();
}

this is the code i used. i also used openfiledialog to select xlsx file

That isn't an average - it's a sum. The average is calculated by summing the other rows and dividing the sum by the number of elements you added together.
In addition, that doesn't do anything particularly useful because it always sums the same row - including the location that you stick the result in each time - and the first set of lines before the foreach line do nothing at all ...

If you want to show an average for each row, then either use the DataGridView.RowPrePaint Event[^] or add a formula to your Excel file so it is calculated by the engine when the spreadsheet is accessed.

And you shouldn't be using and modifying Row values, you should be using and modifying the underlying data behind them in the DataSource directly.

To be honest, that looks like you threw it together in a hurry, and just hoped it would magically do what you wanted - and development does not work like that at all!
 
Share this answer
 
Comments
BillWoodruff 15-Jul-19 2:03am    
I wonder, if in using a Paint event this way, you would lose control over when the average was calculated ... since a Paint Event is going to get called whenever the drawing area is invalidated.

But, as Niels Bohr said of a theory of Pauli's: “We are all agreed that your theory is crazy. The question that divides us is whether it is crazy enough to have a chance of being correct.”
OriginalGriff 15-Jul-19 2:15am    
The whole idea is that the event is called before each time a row is painted, so what is displayed is always correct - provided that the grid is invalidated after changes (as it would need to be in any case) - but the average is only calculated for rows that need it. Given that he has a ludicrous number of rows and columns for any display control, that could save considerable wasted processor time.
BillWoodruff 15-Jul-19 2:42am    
I do not know how exactly Excel handles painting for Rows: if you know for a fact there would be no unnecessary multiple repainting ... great !
OriginalGriff 15-Jul-19 2:51am    
The code he shows is C# code, using a DataGridView - it accesses it's data from an Excel file via ODBC so Excel painting is not used. Hence the "add a formula" bit.
BillWoodruff 15-Jul-19 3:32am    
ahhh ... I see ... I should have said: I do not know how exactly the DGV handles painting for Rows: if you know for a fact there would be no unnecessary multiple repainting ... great !
There are some previous answers of mine I think you may find useful:

0 the DataTable.Compute method: [^] gives you a somewhat Excel-like syntax to calculate in the DataTable.

1a has a variety of Extension Methods that could be easily changed to calculate 'Average: [^]

1b [^]

2 using the 'Field extension method to filter rows by 'Field: [^] note: requires you add a reference to System.Data.DataSetExtensions.dll

I'd say your best shot is using DataTable.Compute.

After any calculation/change in the DataTable, be sure and reset the DataGridView 'DataSource property.
 
Share this answer
 
Comments
EntryLevelGuy! 15-Jul-19 4:10am    
Thank you very much! Gonna try that and i'll comment here after. Hope it works for me :))

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900