Interesting Calculation

F

flipknob

Hello,

I have a 2006 calendar in Excel that I have downloaded from the Office
Templates website. I've taken the calendar and added it to a budget
spreadsheet I'm building.

What I want to do is be able to type in "House Payment: 1200.00" on
March 2nd and "Car Payment: 400.00" on March 15th, for example, and be
able to calculate those amounts in another field.

This means that I need to be able to sum fields that contain numbers
and text. Is this possible?

Thanks!
Dale
 
R

Ron Rosenfeld

Hello,

I have a 2006 calendar in Excel that I have downloaded from the Office
Templates website. I've taken the calendar and added it to a budget
spreadsheet I'm building.

What I want to do is be able to type in "House Payment: 1200.00" on
March 2nd and "Car Payment: 400.00" on March 15th, for example, and be
able to calculate those amounts in another field.

This means that I need to be able to sum fields that contain numbers
and text. Is this possible?

Thanks!
Dale
Click to expand...

If there is always a colon and some variable number of spaces prior to (and
only prior to) the value, then:

=--MID(A1,FIND(":",A1)+1,255)

will extract the value as a number


--ron
 
R

Rajah

Dale,
I'd strongly suggest that you place the payment type in one column and
the amount in another. That would make the fields much more Excel-like,
and easier to sum.

If you really must place these in one cell, say A1, you might use this
series of formulas to extract the number:
B1: =FIND(":",A1) (This finds the colon)
C1: =LEN(A1)-B1 (This tells you how many characters are to the right of
the colon)
D1: =RIGHT(A1,C1) (This extracts the rightmost characters, probably
your amount)
E1: =VALUE(D1) (This turns the string "1200.00" into a number,
1200.00)

Of course, you can combine these all into one formula, like this:
b1: =VALUE(RIGHT(A1, LEN(A1)-FIND(":",A1)))

but I thought you might want to understand how it was derived.
 
T

Trevor Shuttleworth

And, as another option, you could use a Custom Format:

Format | Cells | Number tab | Category: Custom

Type: "House Payment: "#,##0

Then you could just have a numeric value in the field but it would look as
though the text were there too. You'd need a Custom format for each type of
payment though.

Type: "Car Payment: "#,##0

You then don't need any extra columns/cells to extract the value from the
cell. Once you've set up the Custom formats you can use them wherever you
want.

Regards

Trevor
 
Top