Monday 13 September 2010

Any Excel experts reading please?

The spreadsheet I'm using to track this endeavour has an annoying anomaly, and I can't figure out how to work around it:

The column entitled Goal Target calculates the next day's target for me. The two cells Sum and Average at the top calculate those two sums - what I need is a way of doing that WITHOUT counting the bottom cell - as that is obviously a matter for tomorrow and not today! I've mucked about with Offset, CountA and all kinds of other formulae and it's doing my head in! I'm sure I could come up with a VBA function to do it but that seems like cracking a nut with a sledgehammer! Any help gratefully received...
Posted by

7 comments:

  1. Anonymous14 September 2010 at 09:17

    Hi,
    Maybe if you showed your formulae it would help
    at a glance would think that an if formula would help.

    Rgds

    Dave

    ReplyDelete
  2. gundulf15 September 2010 at 20:11

    Fair comment - at the moment it's simply Sum(a5:a150) or whatever. I've tried SUM(A5:COUNT(A5:A150)-1) - throws an error - and loads of others... there must be a way of finding the bottom cell of a range and moving up one or x cells but I can't figure it out...

    ReplyDelete
  3. Anonymous15 September 2010 at 23:00

    Hi Gun,
    since the cell, next to the cell which value you don't want to include in calculation, is always empty, you can use the following (assuming your data is in columns A and B):
    1. For Sum =SUMIF(B1:B150;">0";A1:A150)
    2. For Avg =SUMIF(B1:B150;">0";A1:A150)/COUNT(B1:B150)
    See you in the room.
    Cheers,
    Samo

    ReplyDelete
  4. gundulf16 September 2010 at 00:55

    Samo, you are a star - what a simple, elegant solution! It would seem I was somewhat over-complicating things - as usual! Many thanks - played around with sumif and countif as well...... :-(

    ReplyDelete
  5. JACK2 December 2021 at 06:37

    Great job for publishing such a beneficial web site. Your web log isn’t only useful but it is additionally really creative too. ยูฟ่า800

    ReplyDelete
  6. JACK2 December 2021 at 06:43

    Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I'll be subscribing to your feed and I hope you post again soon. Big thanks for the useful info. สมัครเว็บบอลออนไลน์

    ReplyDelete
  7. JACK22 January 2022 at 15:47

    on your|somebody who actually|the most effective|it is best to|simply would like to|actually answered my problem|There are some interesting แทงบอลauto

    ReplyDelete

Subscribe to: Post Comments (Atom)