# Thread: Calculating in formulae (XL97/WinNT4)

1. ## 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!

2. ## 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. ## 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. ## 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>

5. ## 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>

6. ## 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>

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

Many thanks!

8. ## 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. ## 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...

10. ## 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>)

11. ## Re: Calculating in formulae (XL97/WinNT4)

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

Steve

12. ## 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>

13. ## 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!

14. ## 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.

15. ## 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>

#### Posting Permissions

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