Help... Calculate the average of certain cells on selected row.

Question

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 14-Jul-19 17:59pm

EntryLevelGuy!

Updated 14-Jul-19 21:30pm

Add a Solution

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

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Accepted Answer · 2019-07-14T18:55:00

Solution 1

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!

Posted 14-Jul-19 18:55pm

OriginalGriff

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 !

OriginalGriff 15-Jul-19 3:40am

Every time a row needs to be painted, the PrePaint event is called a single time - which is perfect for column averages and such like as it gives a "snap shot" exactly when it's needed instead of "precalculating" every row value.
There is also the CellPainting event which is called for each cell in the row, and is very handy for coloring individual cells to indicate positive / negative for example.
https://www.codeproject.com/Tips/1240904/Colouring-DataGridView-Cells-According-to-their-Co

BillWoodruff 15-Jul-19 4:14am

Now, I am really glad I responded to your solution, since your reply has educated me about an aspect of the DGV I was not aware of. thanks ! Voted #5.

OriginalGriff 15-Jul-19 4:43am

More than welcome, my friend!

BillWoodruff · Accepted Answer · 2019-07-14T21:31:00

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.