Averaging

[riskier4ra]

Hi, anyone have an idea on how I can get these formulas to work as one.

I am trying to get an average on stock price. I found an excel sheet that was using the following formula
=IF(OR(D22="err",D23="err"),"err",D22/D23)

D22 = Total Cost = =IF(OR(D8="err",D12="err",D16="err",D20="err"),"err",SUM(D8,D12,D16,D20))
D33 = Total Shares = =IF(OR(C8<0,C12<0,C16<0,C20<0),"err",C6+C10+C14+C18)

I also have my own that works using this formula
IFERROR(ABS(AVERAGEIF(E18:G27,E7,G18:G27)),0)

In E:18:G27 = Symbol, #Shares , Buy Price
E7 = Symbol

The reason I am trying to figure out how to merge them is because the 1st one actually gives a correct average when adding new buys.

Mine doesnt seem to work right.

To use the 1st option I would have to reformat my page. Not a good solution for me. I simply want the formula to look at E18:G27, find the symbols = E7, its #of shares, sum up the buy price of what is found, then divide the total cost against the total shares. Is this possible?

Thanks - Risk

 

[AlanY]

how about this?

Book1
	E	F	G
7	#of		12.5
17
18			10
19			10
20	#of		10
21	#of		10
22			10
23			10
24	#of		20
25			10
26			10
27	#of		10
Sheet1

Cell Formulas
Range	Formula
G7	=AVERAGEIF(E18:E27,E7,G18:G27)

 

[riskier4ra]

No that doesnt work.

Gives me the same result.

I need it to look at the range, find the symbol I select in a drop down, sum up the total buy price, sum up the total number of shares, then divide them to give me the average.

 

[AlanY]

can you post a small sample of your data here?

 

[riskier4ra]

can you post a small sample of your data here?

I will recreate some of it in another sheet with generic data. Give me a few.

 

[riskier4ra]

can you post a small sample of your data here?

Hmm. I could have sworn you could attach files on here.... Am I missing something or do they no longer allow attachments?

 

[AlanY]

unfortunately, you can't attach files here. you can share them on goggle drive or similar