Prev: Gathering Data using excel
Next: index/if function
From: Brad on 8 Mar 2010 09:56
Can the offset be used in the below formula for the information in column "K"?

In column L have the the following formula's
L46 =MAX($I46*1000-$K$46*$J46,0)
L47 =MAX($I47*1000-$K$46*$J47,0)
....
L99 =MAX($I99*1000-$K$46*$J99,0)

What I'd like to do is to copy the fomula into columns M, N, O, ...

M47 =MAX($I47*1000-$K$47*$J47,0)
M48 =MAX($I48*1000-$K$47*$J48,0)
M49 =MAX($I49*1000-$K$47*$J49,0)
....

N48 =MAX($I48*1000-$K$48*$J48,0)
N49 =MAX($I49*1000-$K$48*$J49,0)
....

O50 =MAX($I50*1000-$K$50*$J50,0)
O51 =MAX($I51*1000-$K$50*$J51,0)
....


From: Bob Phillips on 8 Mar 2010 10:43
How about

N48: =MAX($I48*1000-OFFSET($K$47,COLUMN()-COLUMN(M1),0)*$J48,0)

--

HTH

Bob

"Brad" <Brad(a)discussions.microsoft.com> wrote in message
news:338064BD-AC6E-4D70-8AD0-551D0AC523B7(a)microsoft.com...
> Can the offset be used in the below formula for the information in column
> "K"?
>
> In column L have the the following formula's
> L46 =MAX($I46*1000-$K$46*$J46,0)
> L47 =MAX($I47*1000-$K$46*$J47,0)
> ...
> L99 =MAX($I99*1000-$K$46*$J99,0)
>
> What I'd like to do is to copy the fomula into columns M, N, O, ...
>
> M47 =MAX($I47*1000-$K$47*$J47,0)
> M48 =MAX($I48*1000-$K$47*$J48,0)
> M49 =MAX($I49*1000-$K$47*$J49,0)
> ...
>
> N48 =MAX($I48*1000-$K$48*$J48,0)
> N49 =MAX($I49*1000-$K$48*$J49,0)
> ...
>
> O50 =MAX($I50*1000-$K$50*$J50,0)
> O51 =MAX($I51*1000-$K$50*$J51,0)
> ...
>
>


From: Brad on 9 Mar 2010 16:14
Thank you



"Bob Phillips" wrote:

> How about
>
> N48: =MAX($I48*1000-OFFSET($K$47,COLUMN()-COLUMN(M1),0)*$J48,0)
>
> --
>
> HTH
>
> Bob
>
> "Brad" <Brad(a)discussions.microsoft.com> wrote in message
> news:338064BD-AC6E-4D70-8AD0-551D0AC523B7(a)microsoft.com...
> > Can the offset be used in the below formula for the information in column
> > "K"?
> >
> > In column L have the the following formula's
> > L46 =MAX($I46*1000-$K$46*$J46,0)
> > L47 =MAX($I47*1000-$K$46*$J47,0)
> > ...
> > L99 =MAX($I99*1000-$K$46*$J99,0)
> >
> > What I'd like to do is to copy the fomula into columns M, N, O, ...
> >
> > M47 =MAX($I47*1000-$K$47*$J47,0)
> > M48 =MAX($I48*1000-$K$47*$J48,0)
> > M49 =MAX($I49*1000-$K$47*$J49,0)
> > ...
> >
> > N48 =MAX($I48*1000-$K$48*$J48,0)
> > N49 =MAX($I49*1000-$K$48*$J49,0)
> > ...
> >
> > O50 =MAX($I50*1000-$K$50*$J50,0)
> > O51 =MAX($I51*1000-$K$50*$J51,0)
> > ...
> >
> >
>
>
> .
>
 | 
Pages: 1
Prev: Gathering Data using excel
Next: index/if function