if / or formulas
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 =B33.
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" wrote in message
...
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" wrote in message
...
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(b33,orb4?3,b53,b63.....) 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
|