Home |
Search |
Today's Posts |
#1
|
|||
|
|||
if / or formulas
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 |
#2
|
|||
|
|||
if / or formulas
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 |
#3
|
|||
|
|||
if / or formulas
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
if / or formulas
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 =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 |
#6
|
|||
|
|||
if / or formulas
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" wrote in message ... 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 =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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
paste formulas between workbooks without workbook link | Excel Discussion (Misc queries) | |||
Array Formulas take waaaay too long... | Excel Worksheet Functions | |||
Help, Urgent Excel Formulas are not calculating | Excel Discussion (Misc queries) | |||
Problem with named formula's | Excel Worksheet Functions | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |