Extracting values from formulas

  • Thread starter Thread starter servboss02
  • Start date Start date
S

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???
 
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
Click to expand...

Otherwise, you'll need some VBA code to get the shares and then you can use
that info to calculate the price.
 
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???
Click to expand...

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)))
 
Spiky said:
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)))
Click to expand...

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.
 
Back
Top