Extracting values from formulas

[servboss02]

I have a spreadsheet that tracks the value of my stock portfolio. Calendar
quarters are across the columns, the investments are down the rows. Every
quarter I have entered a formula in every row down that quarter's column that
looks like this:

=100*15.523

which means 100 shares at $15.523. This gives me a quarterly time series of
the value of my investments as I head left to right on a row.

But now I would like to create two time series - the number of shares that I
owned and the price at the end of the quarter. The information is all there,
I just want a formula for a row that extract the shares from the formula and
a different formula that would extract the share price.

So, for example, I might have a simple sheet that looks like this:
Q1 Q2
Fund1 1552.30 1816.87

The formulas would look like this:
Q1 Q2
Fund1 =100*15.523 =110*16.517

I'd like the spreadsheet to look like this:
Q1 Q2
Fund1 1552.30 1816.87
Shares 100.00 110.00
Price 15.523 16.517


Help???

 

[T. Valko]

Why don't you just set up your table like this and manually enter the price
and shares? Then you can calculate the Fund value by using a cells
references:

=B3*B4

Q1 Q2
Fund1 1552.30 1816.87
Shares 100.00 110.00
Price 15.523 16.517

Otherwise, you'll need some VBA code to get the shares and then you can use
that info to calculate the price.

 

[Spiky]

I have a spreadsheet that tracks the value of my stock portfolio. Calendar
quarters are across the columns, the investments are down the rows. Every
quarter I have entered a formula in every row down that quarter's column that
looks like this:

=100*15.523

which means 100 shares at $15.523. This gives me a quarterly time series of
the value of my investments as I head left to right on a row.

But now I would like to create two time series - the number of shares that I
owned and the price at the end of the quarter. The information is all there,
I just want a formula for a row that extract the shares from the formula and
a different formula that would extract the share price.

So, for example, I might have a simple sheet that looks like this:
Q1 Q2
Fund1 1552.30 1816.87

The formulas would look like this:
Q1 Q2
Fund1 =100*15.523 =110*16.517

I'd like the spreadsheet to look like this:
Q1 Q2
Fund1 1552.30 1816.87
Shares 100.00 110.00
Price 15.523 16.517

Help???

Well, I agree with Biff that you should separate these values first,
not with a formula. But there is a way without creating new VBA. You
would have to install Morefunc from http://xcell05.free.fr/english/.

Assuming D5 is your formula, and assuming the shares are always first,
price second:
=MID(FORMULATEXT(D5),FIND("*",FORMULATEXT(D5))+1,LEN(FORMULATEXT(D5))-
FIND("*",FORMULATEXT(D5)))

 

[T. Valko]

Well, I agree with Biff that you should separate these values first,
not with a formula. But there is a way without creating new VBA. You
would have to install Morefunc from http://xcell05.free.fr/english/.

Assuming D5 is your formula, and assuming the shares are always first,
price second:
=MID(FORMULATEXT(D5),FIND("*",FORMULATEXT(D5))+1,LEN(FORMULATEXT(D5))-
FIND("*",FORMULATEXT(D5)))

That's an excellent add-in. I highly recommend it and have it installed on
my machine.

Determining whether a cell contains a formula and then extracting that
formula as a TEXT string is fairly easy with VBA. It requires just a few
lines of code as a user defined function.

Function GetFormula(cell_ref As Range) As String
If cell_ref.HasFormula Then
GetFormula = cell_ref.Formula
End If
End Function

Insalled as a general module.

Then, to extract the shares:

=MID(GetFormula(D5),2,FIND("*",GetFormula(D5))-2)+0

The price would then be the fund value divided by the result of above
formula.