Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Mar 2004
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    New Lounger
    Join Date
    Mar 2004
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Tristan
    Guest

    Re: Counting cell changes and totalising

    Regarding the "circular reference" matter, you can still do it and have Excel to accept it:
    Go to menu Tools/Options(tab:Calculation)
    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. #6
    New Lounger
    Join Date
    Mar 2004
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #7
    Tristan
    Guest

    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. #8
    New Lounger
    Join Date
    Mar 2004
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #9
    Star Lounger
    Join Date
    Jan 2001
    Location
    Newcastle, New South Wales, Australia
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •