+ Reply to Thread
Results 1 to 4 of 4

Can't calculate values of fields with a $.

  1. #1
    Ufomonster
    Guest

    Can't calculate values of fields with a $.

    I need to paste a column of home sales prices, and a column of home square
    footages, and calculate based on those two values. (sales price/square
    footage)

    BUT...the pasted list is in this format: "$250,000." Whenever I make the
    calculations, I get a "VALUE!" error. This is because it doesn't understand
    "$250,000" has a value. How can I get it to understand that "$250,000" has a
    value of 250,000?

  2. #2
    TC
    Guest

    RE: Can't calculate values of fields with a $.

    Highlight the column you want for Sales Price. Right click- Then left click
    on "Format Cells"- Under number tab click "Currency". Don't type the $ key
    just the price.

    TC

    "Ufomonster" wrote:

    > I need to paste a column of home sales prices, and a column of home square
    > footages, and calculate based on those two values. (sales price/square
    > footage)
    >
    > BUT...the pasted list is in this format: "$250,000." Whenever I make the
    > calculations, I get a "VALUE!" error. This is because it doesn't understand
    > "$250,000" has a value. How can I get it to understand that "$250,000" has a
    > value of 250,000?


  3. #3
    Frank
    Guest

    RE: Can't calculate values of fields with a $.

    I should clarify that I'm not typing in the fields at all, I'm copying from a
    database and pasting into Excel. The dollar signs are in the data that I'm
    copying. Removing them one by one would be very time-consuminig.

    "TC" wrote:

    > Highlight the column you want for Sales Price. Right click- Then left click
    > on "Format Cells"- Under number tab click "Currency". Don't type the $ key
    > just the price.
    >
    > TC
    >
    > "Ufomonster" wrote:
    >
    > > I need to paste a column of home sales prices, and a column of home square
    > > footages, and calculate based on those two values. (sales price/square
    > > footage)
    > >
    > > BUT...the pasted list is in this format: "$250,000." Whenever I make the
    > > calculations, I get a "VALUE!" error. This is because it doesn't understand
    > > "$250,000" has a value. How can I get it to understand that "$250,000" has a
    > > value of 250,000?


  4. #4
    Frank
    Guest

    RE: Can't calculate values of fields with a $.

    Hey everyone, I figured it out. The database I was copying from had a space
    after the sales price. ( "$250,000 " ) So, I used this formula to remove the
    contemptable extra space:

    =(MID(A1,1,LEN(A1)-1))

    (assuming my data is in A1)

    which removes the last (rightmost) character from what I pasted.

    Hope it helps you out if you run into this.




    "Ufomonster" wrote:

    > I need to paste a column of home sales prices, and a column of home square
    > footages, and calculate based on those two values. (sales price/square
    > footage)
    >
    > BUT...the pasted list is in this format: "$250,000." Whenever I make the
    > calculations, I get a "VALUE!" error. This is because it doesn't understand
    > "$250,000" has a value. How can I get it to understand that "$250,000" has a
    > value of 250,000?


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1