if / or formulas

[jamesg-fid]

i build a spreadhseet daily of portfolio positions and the percentage that
each stock takes of the total portfolio value......the individual positions
are cut and paste from another system striaght to excel.....at the end of
this vertical list of positions I add a 'total' amount. Obviously the number
of positions changes on a daily basis.
what I also want to add is a table that says if any one single stock is over
three percent of the portfolio value, so i need formula(e) that looks down
the list and tells me if the number is greater than three. However the range
of cells will change on a daily basis and I dont want to manually enter
=or(b3>3,orb4?3,b5>3,b6>3.....) and change it on a daily basis (also the risk
of manual error)

i have toyed with the idea of =if(cell="total",0,1), this way a column
comes up with 1 next to all the postions, and a 0 next to the total amount,
then use
=match(0,column range,1) - this then tells me the number of cells there are
with stock positions in to look at to see if any number is greater than
3.....however what formula can i use that looks at each cell in this range
individually that tells me if any one of the cells is higher than three ?

thanks
James

[Bob Phillips]

James,

How about conditional formatting.

By selecting all the cells, and use a formula of =(B3/SUM(B:B))>0.03

--

HTH

RP
(remove nothere from the email address if mailing direct)


"jamesg-fid" <[email protected]> wrote in message
news:[email protected]...
> i build a spreadhseet daily of portfolio positions and the percentage that
> each stock takes of the total portfolio value......the individual
positions
> are cut and paste from another system striaght to excel.....at the end of
> this vertical list of positions I add a 'total' amount. Obviously the
number
> of positions changes on a daily basis.
> what I also want to add is a table that says if any one single stock is
over
> three percent of the portfolio value, so i need formula(e) that looks
down
> the list and tells me if the number is greater than three. However the
range
> of cells will change on a daily basis and I dont want to manually enter
> =or(b3>3,orb4?3,b5>3,b6>3.....) and change it on a daily basis (also the
risk
> of manual error)
>
> i have toyed with the idea of =if(cell="total",0,1), this way a column
> comes up with 1 next to all the postions, and a 0 next to the total
amount,
> then use
> =match(0,column range,1) - this then tells me the number of cells there
are
> with stock positions in to look at to see if any number is greater than
> 3.....however what formula can i use that looks at each cell in this range
> individually that tells me if any one of the cells is higher than three ?
>
> thanks
> James

[jamesg-fid]

cheers Bob - will this look at individual cell totals and return if any one
of them is greater than 3 ? or is it an average of one cell to the range of
the whole ?
thanks again

"Bob Phillips" wrote:

> James,
>
> How about conditional formatting.
>
> By selecting all the cells, and use a formula of =(B3/SUM(B:B))>0.03
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "jamesg-fid" <[email protected]> wrote in message
> news:[email protected]...
> > i build a spreadhseet daily of portfolio positions and the percentage that
> > each stock takes of the total portfolio value......the individual
> positions
> > are cut and paste from another system striaght to excel.....at the end of
> > this vertical list of positions I add a 'total' amount. Obviously the
> number
> > of positions changes on a daily basis.
> > what I also want to add is a table that says if any one single stock is
> over
> > three percent of the portfolio value, so i need formula(e) that looks
> down
> > the list and tells me if the number is greater than three. However the
> range
> > of cells will change on a daily basis and I dont want to manually enter
> > =or(b3>3,orb4?3,b5>3,b6>3.....) and change it on a daily basis (also the
> risk
> > of manual error)
> >
> > i have toyed with the idea of =if(cell="total",0,1), this way a column
> > comes up with 1 next to all the postions, and a 0 next to the total
> amount,
> > then use
> > =match(0,column range,1) - this then tells me the number of cells there
> are
> > with stock positions in to look at to see if any number is greater than
> > 3.....however what formula can i use that looks at each cell in this range
> > individually that tells me if any one of the cells is higher than three ?
> >
> > thanks
> > James
>
>
>

[Bob Phillips]

James,

The way I had it, I looks to see whether any cell is greater than 3% of the
total. If you want any greater than 3, it is just =B3>3.

BTW, when doing CF, select all cells to apply it to, but use a cell
reference in the formula of the active cell.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"jamesg-fid" <[email protected]> wrote in message
news:[email protected]...
> cheers Bob - will this look at individual cell totals and return if any
one
> of them is greater than 3 ? or is it an average of one cell to the range
of
> the whole ?
> thanks again
>
> "Bob Phillips" wrote:
>
> > James,
> >
> > How about conditional formatting.
> >
> > By selecting all the cells, and use a formula of =(B3/SUM(B:B))>0.03
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "jamesg-fid" <[email protected]> wrote in message
> > news:[email protected]...
> > > i build a spreadhseet daily of portfolio positions and the percentage
that
> > > each stock takes of the total portfolio value......the individual
> > positions
> > > are cut and paste from another system striaght to excel.....at the end
of
> > > this vertical list of positions I add a 'total' amount. Obviously the
> > number
> > > of positions changes on a daily basis.
> > > what I also want to add is a table that says if any one single stock
is
> > over
> > > three percent of the portfolio value, so i need formula(e) that looks
> > down
> > > the list and tells me if the number is greater than three. However
the
> > range
> > > of cells will change on a daily basis and I dont want to manually
enter
> > > =or(b3>3,orb4?3,b5>3,b6>3.....) and change it on a daily basis (also
the
> > risk
> > > of manual error)
> > >
> > > i have toyed with the idea of =if(cell="total",0,1), this way a
column
> > > comes up with 1 next to all the postions, and a 0 next to the total
> > amount,
> > > then use
> > > =match(0,column range,1) - this then tells me the number of cells
there
> > are
> > > with stock positions in to look at to see if any number is greater
than
> > > 3.....however what formula can i use that looks at each cell in this
range
> > > individually that tells me if any one of the cells is higher than
three ?
> > >
> > > thanks
> > > James
> >
> >
> >

[jamesg-fid]

is there a way though that I can have one single cell that reads all of these
and tells me if there if any one or more of these is greater than 3% - ie a
simple "true""false" formula ?

"Bob Phillips" wrote:

> James,
>
> The way I had it, I looks to see whether any cell is greater than 3% of the
> total. If you want any greater than 3, it is just =B3>3.
>
> BTW, when doing CF, select all cells to apply it to, but use a cell
> reference in the formula of the active cell.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "jamesg-fid" <[email protected]> wrote in message
> news:[email protected]...
> > cheers Bob - will this look at individual cell totals and return if any
> one
> > of them is greater than 3 ? or is it an average of one cell to the range
> of
> > the whole ?
> > thanks again
> >
> > "Bob Phillips" wrote:
> >
> > > James,
> > >
> > > How about conditional formatting.
> > >
> > > By selecting all the cells, and use a formula of =(B3/SUM(B:B))>0.03
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "jamesg-fid" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > i build a spreadhseet daily of portfolio positions and the percentage
> that
> > > > each stock takes of the total portfolio value......the individual
> > > positions
> > > > are cut and paste from another system striaght to excel.....at the end
> of
> > > > this vertical list of positions I add a 'total' amount. Obviously the
> > > number
> > > > of positions changes on a daily basis.
> > > > what I also want to add is a table that says if any one single stock
> is
> > > over
> > > > three percent of the portfolio value, so i need formula(e) that looks
> > > down
> > > > the list and tells me if the number is greater than three. However
> the
> > > range
> > > > of cells will change on a daily basis and I dont want to manually
> enter
> > > > =or(b3>3,orb4?3,b5>3,b6>3.....) and change it on a daily basis (also
> the
> > > risk
> > > > of manual error)
> > > >
> > > > i have toyed with the idea of =if(cell="total",0,1), this way a
> column
> > > > comes up with 1 next to all the postions, and a 0 next to the total
> > > amount,
> > > > then use
> > > > =match(0,column range,1) - this then tells me the number of cells
> there
> > > are
> > > > with stock positions in to look at to see if any number is greater
> than
> > > > 3.....however what formula can i use that looks at each cell in this
> range
> > > > individually that tells me if any one of the cells is higher than
> three ?
> > > >
> > > > thanks
> > > > James
> > >
> > >
> > >
>
>
>

[Bob Phillips]

James,

This should do it

=SUMPRODUCT(--(F1:F1000/SUM(F1:F1000)>3%))>0

--

HTH

RP
(remove nothere from the email address if mailing direct)


"jamesg-fid" <[email protected]> wrote in message
news:[email protected]...
> is there a way though that I can have one single cell that reads all of
these
> and tells me if there if any one or more of these is greater than 3% - ie
a
> simple "true""false" formula ?
>
> "Bob Phillips" wrote:
>
> > James,
> >
> > The way I had it, I looks to see whether any cell is greater than 3% of
the
> > total. If you want any greater than 3, it is just =B3>3.
> >
> > BTW, when doing CF, select all cells to apply it to, but use a cell
> > reference in the formula of the active cell.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "jamesg-fid" <[email protected]> wrote in message
> > news:[email protected]...
> > > cheers Bob - will this look at individual cell totals and return if
any
> > one
> > > of them is greater than 3 ? or is it an average of one cell to the
range
> > of
> > > the whole ?
> > > thanks again
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > James,
> > > >
> > > > How about conditional formatting.
> > > >
> > > > By selecting all the cells, and use a formula of =(B3/SUM(B:B))>0.03
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "jamesg-fid" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > i build a spreadhseet daily of portfolio positions and the
percentage
> > that
> > > > > each stock takes of the total portfolio value......the individual
> > > > positions
> > > > > are cut and paste from another system striaght to excel.....at the
end
> > of
> > > > > this vertical list of positions I add a 'total' amount. Obviously
the
> > > > number
> > > > > of positions changes on a daily basis.
> > > > > what I also want to add is a table that says if any one single
stock
> > is
> > > > over
> > > > > three percent of the portfolio value, so i need formula(e) that
looks
> > > > down
> > > > > the list and tells me if the number is greater than three.
However
> > the
> > > > range
> > > > > of cells will change on a daily basis and I dont want to manually
> > enter
> > > > > =or(b3>3,orb4?3,b5>3,b6>3.....) and change it on a daily basis
(also
> > the
> > > > risk
> > > > > of manual error)
> > > > >
> > > > > i have toyed with the idea of =if(cell="total",0,1), this way a
> > column
> > > > > comes up with 1 next to all the postions, and a 0 next to the
total
> > > > amount,
> > > > > then use
> > > > > =match(0,column range,1) - this then tells me the number of cells
> > there
> > > > are
> > > > > with stock positions in to look at to see if any number is greater
> > than
> > > > > 3.....however what formula can i use that looks at each cell in
this
> > range
> > > > > individually that tells me if any one of the cells is higher than
> > three ?
> > > > >
> > > > > thanks
> > > > > James
> > > >
> > > >
> > > >
> >
> >
> >