Prev: Need solution for wildcard use in array
Next: FDIST Function
From: Marcio on 27 Apr 2010 15:36
Hi,
I have a table
A B C
1 forro 120
2 ment 80 40
3 forro 50 30
4 ment 40 10
5 ment 30 10
6 forro 20 10
7 forro 10 10
8 forro 5 5



Starting from C2, the column C has a formula (=B1-B2) result = 40; (=B2-B3)
result = 30; etc...

When I apply autofilter the formula in cell C keeps the original information
(=B1-B2), and I would like to have a formula to change and shows de result as
below (=B1-B3) result = 70; (=B3-B6) result = 30; etc...
A B C
1 forro 120
3 forro 50 70
6 forro 20 30
7 forro 10 10
8 forro 5 5

The objective is to have a formula considering just the visible cells.

Thank you,
Marcio
From: T. Valko on 27 Apr 2010 20:29
This seems overly complex but it works...

Array entered in C2 and copied down as needed.

=INDEX(B$1:B$8,MATCH(SUBTOTAL(3,B$1:B1),SUBTOTAL(3,OFFSET(B$1:B$8,,,ROW(B$1:B$8)-ROW(B$1)+1)),0))-INDEX(B$1:B$8,MATCH(SUBTOTAL(3,B$1:B1)+1,SUBTOTAL(3,OFFSET(B$1:B$8,,,ROW(B$1:B$8)-ROW(B$1)+1)),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Marcio" <Marcio(a)discussions.microsoft.com> wrote in message
news:13CFD47F-ACD0-4A99-AE88-38962561CE11(a)microsoft.com...
> Hi,
> I have a table
> A B C
> 1 forro 120
> 2 ment 80 40
> 3 forro 50 30
> 4 ment 40 10
> 5 ment 30 10
> 6 forro 20 10
> 7 forro 10 10
> 8 forro 5 5
>
>
>
> Starting from C2, the column C has a formula (=B1-B2) result = 40;
> (=B2-B3)
> result = 30; etc...
>
> When I apply autofilter the formula in cell C keeps the original
> information
> (=B1-B2), and I would like to have a formula to change and shows de result
> as
> below (=B1-B3) result = 70; (=B3-B6) result = 30; etc...
> A B C
> 1 forro 120
> 3 forro 50 70
> 6 forro 20 30
> 7 forro 10 10
> 8 forro 5 5
>
> The objective is to have a formula considering just the visible cells.
>
> Thank you,
> Marcio


 | 
Pages: 1
Prev: Need solution for wildcard use in array
Next: FDIST Function