# Thread: Counting cell changes and totalising

1. ## Counting cell changes and totalising

Hi there, I suspect this is dead easy but I know zip about Excel...
I have written (well, kind of copied/modified !) a small Delphi program that takes the
weight from a set of scales and puts it into a user-defined cell in an Excel worksheet
using OLE. I need to have calculated the average weight and total weight. ie whenever
the weight in my user-defined cell changes I need to add the new weight to the total
and increment a counter by one so I can calculate the average. I also need to be
able to calculate the Std.Deviation. I no nothing of Excel and I tried "=B1+A1" where
A1 is my changing weight but it complained of a circular reference : fair enough. I couldn't
find anything in the help files. Could someone point me the right direction. I could
do it all within my program and then send it Excel, but that doesn't help further my
understanding of Excel, does it ?!
Cheers,

2. ## Re: Counting cell changes and totalising

Hi,
Have you entered this formula in B1 by any chance? You can't (sadly) out a formula into a cell in Excel telling it to add something to itself because it would just keep adding the value on repeatedly.
Hope that helps.

3. ## Re: Counting cell changes and totalising

Yes - I had "B1 = B1 + A1" and thus got myself a nice circular reference. My question is still : how do I count the number of times the value in a cell changes and at the same time totalize those values ?

4. ## Re: Counting cell changes and totalising

Hi Jeff,
Sadly there is no 'easy' way that I can think of other than having someone do it manually. You could change your code so that it finds the first blank cell in column A and adds the new value to that cell (so you've effectively got the entire history of all your values) or you'd need to trigger a macro to update the value in B1 and increment a counter value in say C1.
Hope that helps.

5. ## Re: Counting cell changes and totalising

Regarding the "circular reference" matter, you can still do it and have Excel to accept it:
Check the Iterations box.
Voila!

I commonly set the number of iterations to 1 and it works fine for me. Be aware that any recalc (manual or automatic) will affect your result...

6. ## Re: Counting cell changes and totalising

My [potential] customers wouldn't accept even the possibility of incorrect results. I think I shall have to create a rolling sample of, say, 100 entries and use that to do my calculations...

7. ## Re: Counting cell changes and totalising

How do you intend to handle the same value being posted twice ?
[img]/w3timages/icons/clever.gif[/img]Depending on how complex your spreadsheet is allowed to be, consider using the ON_DATA property: it will detect any data posted via a link (OLE or other) into the sheet.[img]/w3timages/icons/crazy.gif[/img]This involves either VBA or MACRO programming.

8. ## Re: Counting cell changes and totalising

My application : food packing. Each box needs to be weighed and recorded automagically. I also need to count boxes and keep a total so I can calculate averages and std dev. I can't miss boxes or have inaccurate stats - not acceptable. If I can work out how to do this in Excel then I may be able to produce something useful...

9. ## Re: Counting cell changes and totalising

What about reading it into the cell and then moving it elsewhere instead of over-writing the same data. That way you can keep a record of what was actually weighed and make sure that no box was missed. And your calculations would be on the column(s) of data instead of having to update single values.
Ruth

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•