Results 1 to 15 of 15

20030404, 12:06 #1
 Join Date
 Jun 2001
 Location
 Basingstoke, Hampshire, United Kingdom
 Posts
 1,845
 Thanks
 11
 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

20030404, 13:00 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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 nonzero 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.

20030404, 13:32 #3
 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 ctrlshiftenter). This uses no intermediate calcs except for the sum, which uses the other 2 values.
Steve

20030404, 14:13 #4
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,354
 Thanks
 4
 Thanked 229 Times in 210 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

20030404, 14:26 #5
 Join Date
 Jun 2001
 Location
 Basingstoke, Hampshire, United Kingdom
 Posts
 1,845
 Thanks
 11
 Thanked 0 Times in 0 Posts
Re: Calculating in formulae (XL97/WinNT4)
Thanks, Hans, especially for the version that allows for less than three nonzero 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

20030404, 14:28 #6
 Join Date
 Jun 2001
 Location
 Basingstoke, Hampshire, United Kingdom
 Posts
 1,845
 Thanks
 11
 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

20030404, 14:46 #7
 Join Date
 Jun 2001
 Location
 Basingstoke, Hampshire, United Kingdom
 Posts
 1,845
 Thanks
 11
 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 nonzero 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 nonzero figure needs to be taken from the same row as column 1's, whether it is a zero or not, same for the 2nd nonzero 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

20030404, 14:56 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20030404, 15:22 #9
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,354
 Thanks
 4
 Thanked 229 Times in 210 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

20030404, 15:32 #10
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,354
 Thanks
 4
 Thanked 229 Times in 210 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

20030404, 15:49 #11
 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

20030407, 07:20 #12
 Join Date
 Jun 2001
 Location
 Basingstoke, Hampshire, United Kingdom
 Posts
 1,845
 Thanks
 11
 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

20030407, 07:48 #13
 Join Date
 Jun 2001
 Location
 Basingstoke, Hampshire, United Kingdom
 Posts
 1,845
 Thanks
 11
 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

20030407, 08:02 #14
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,354
 Thanks
 4
 Thanked 229 Times in 210 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

20030407, 09:48 #15
 Join Date
 Jun 2001
 Location
 Basingstoke, Hampshire, United Kingdom
 Posts
 1,845
 Thanks
 11
 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