Lookup, substitute and round function

  • Thread starter Thread starter StacyM
  • Start date Start date
S

StacyM

I need a function that will 1)look up a value from another table, 2)take out
the apostrophe 3)round it to the nearest fourth. I have the look up function
down:
=VLOOKUP(DATEVALUE("5/10/2009"),Sheet1!A7:G23,7,FALSE). This works great.
The problem is that the values that are looked up (from a linked table to the
internet) are in this format 123'4. They need to be in a currency format
like this $12.350. That is where the substitute function comes in (to take
out the apostrophe). The '4 part changes to 50 because it is fourths of a
cent so '2 should be 25 '4 should be 50 and '6 should be 75. I think these
are all the parts I need, I just don't know how to put it all together
correctly. Thank you!
 
This question brought up a bit of nostalgia.

When I was growing up, my allowance was 2 bits a week, that's 25 cents.

ANYWAY -
It would be much shorter to take the results of your lookup formula and
convert that to the format you're looking for.

Say the Vlookup formula returns the value to cell A1.

In another cell, *formatted to dollars*, use this formula:

=--(LEFT(A1,FIND("'",A1)-1)/10&RIGHT(A1)*12.5)
 
This works great, thanks for your help!

Ragdyer said:
This question brought up a bit of nostalgia.

When I was growing up, my allowance was 2 bits a week, that's 25 cents.

ANYWAY -
It would be much shorter to take the results of your lookup formula and
convert that to the format you're looking for.

Say the Vlookup formula returns the value to cell A1.

In another cell, *formatted to dollars*, use this formula:

=--(LEFT(A1,FIND("'",A1)-1)/10&RIGHT(A1)*12.5)
Click to expand...
 
Back
Top