Results 1 to 15 of 15
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Calculating in formulae (XL97/WinNT4)

    This may be just something that it would be nice to do, but can't be done ... but I thought it had to be worth asking if anyone knew a way of doing it! I'd prefer to do it with formulae in the cells, but if the only way it can be done is via VBA, then so be it. The basic formulae I've been trying to work out, using If, And and Or, is getting so big and complicated that there's no way I can guarantee it'll be correct for any one occasion, and I *have* to be able to be sure!

    I have a volatile spreadsheet that has a grid of numbers, 9 rows high by 7 columns wide, and I need to take three figures from it in each column, going down from top to bottom:

    (1) the first number that is not zero
    (2) the first number after that, that is not zero; and
    (3) the remainder of the column added together, from immediately below (2) to the bottom of the grid (zeros are irrelevant on this one).

    Any one or more of the cells may contain zero at any time, but they'll never contain anything other than a figure or a zero, and there will always be at least three cells containing figures other than zero.

    Am I asking for the moon? I do hope not - and I'd be most grateful for any help!
    Beryl M


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculating in formulae (XL97/WinNT4)

    Beryl,

    The Excel gurus will probably come up with something much more elegant, but in the meantime, here is an example.

    The first worksheet uses array formulas with functions such as MATCH, ADDRESS and INDIRECT to calculate the sum. The second worksheet is slightly more elaborate; it allows columns to contain less than three non-zero entries. You don't need that now, but who knows...

    Note: when you close the workbook, Excel will ask you whether you want to save it, even if you haven't modified anything. This is caused by the use of INDIRECT.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Calculating in formulae (XL97/WinNT4)

    Here is another example using array formulas (confirm with ctrl-shift-enter). This uses no intermediate calcs except for the sum, which uses the other 2 values.

    Steve

  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: Calculating in formulae (XL97/WinNT4)

    Beryl,
    Just so you're spoiled for choice, here's another one! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Calculating in formulae (XL97/WinNT4)

    Thanks, Hans, especially for the version that allows for less than three non-zero entries, but since these are based around working days, with the zeroes being weekends and bank holidays, I don't think it's actually possible!

    Many thanks, anyway

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Beryl M


  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Calculating in formulae (XL97/WinNT4)

    Thanks for that, Steve, and I did start looking into it using your version, but I have to admit Rory's is so short and (relatively) simple that I've been converted!

    Thanks again <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Beryl M


  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Calculating in formulae (XL97/WinNT4)

    Hi, Rory, many thanks for that - I really was spoiled for choice! I did like yours best though - but could I ask just one question?

    I should perhaps have said this from the start, but all the columns except the first are likely to be almost full of zeroes, and won't necessarily therefore have non-zero figures in the same place as column 1 but must use the same line; therefore the calculations in those columns needs to be based on the results of the first column - ie column 2 to 7's 1st non-zero figure needs to be taken from the same row as column 1's, whether it is a zero or not, same for the 2nd non-zero figure, and the third, sum, figure needs to start at the same row in columns 2 to 7 as it does in the first column.

    This is easily done with a quick calculation for the first two based on where the figures match (but perhaps could be done better with OFFSET?), but I can't work out how to do it for the sum ...

    Would you have any helpful suggestions, please?

    Many thanks!
    Beryl M


  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculating in formulae (XL97/WinNT4)

    Beryl, I'll be the first to admit that Steve's solution is more general and Rory's solution far more elegant than mine. Still, I've taken the liberty to adapt my workbook and post it, for what it's worth.

  9. #9
    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: Calculating in formulae (XL97/WinNT4)

    Hans,
    I'm not sure any array formulae of these lengths can exactly be called elegant! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> I liked your solution as I didn't know you could use a construction like INDIRECT(A1):A12 before. Another gem to squirrel away into the recesses of my brain...
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    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: Calculating in formulae (XL97/WinNT4)

    Beryl,
    Try the attached. It does the sum by totalling the offset search range from the second step and subtracting the value returned in the second step (if that makes sense?? <img src=/S/smile.gif border=0 alt=smile width=15 height=15>)
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Calculating in formulae (XL97/WinNT4)

    Here is a simplified version (I got rid of overly generalizing with the named range).

    Steve

  12. #12
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Calculating in formulae (XL97/WinNT4)

    I'm not sure if it makes sense, but it works, so I'm happy! Ecstatic, in fact ...!

    Many thanks!

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Beryl M


  13. #13
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Calculating in formulae (XL97/WinNT4)

    <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15> Looks like I spoke too soon! What am I doing wrong here? The initial column works perfectly, and rows 1 and 3 of the offset do too (I think ...) but the second row seems to be completely screwy!
    Beryl M


  14. #14
    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: Calculating in formulae (XL97/WinNT4)

    Beryl,
    See attached which I think is correct. Your row2 name was matching M3 rather than L3, and your second row was offsetting from C4 rather than D4 in the second column.
    HTH.
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #15
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Calculating in formulae (XL97/WinNT4)

    Thank you, Rory! I knew it had to be something silly, but I'm not up on XL formulae (I have to support XL, and program in it, but don't use it much!) and I couldn't quite get my head around exactly what the formulae were doing.

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Beryl M


Posting Permissions

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