+ Reply to Thread
Results 1 to 2 of 2

How to use letters as a numerical input in a formula, IF or Lookup? I'm stumped.

  1. #1
    Registered User
    Join Date
    07-19-2012
    Location
    Chicago
    MS-Off Ver
    2007
    Posts
    2

    How to use letters as a numerical input in a formula, IF or Lookup? I'm stumped.

    I am trying to get a spreadsheet to work how I want it to, and despite doing quite a bit of internet searching, I still cant figure this out. Please see attachment.

    Amount applied this week is calculated by taking contract amount, multiplied by amount applied, then multiplying again by % completed this period. The way I format this is that under job type "ER" will always be a 60% value for what is shown in %Applied, and "TD" is 40%, and SERV is 100%. I'd like to be able to compute the amount applied this week by only needing the "job type" column and having the sheet know to multiply those percentage values.

    I'll try to clarify if this is confusing.
    Attached Files Attached Files

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How to use letters as a numerical input in a formula, IF or Lookup? I'm stumped.

    to get the percentage from the letters
    =LOOKUP(A1,{"ER","SERV","TD"},{0.6,1,0.4})
    WHER A1 HAS THE VALUE ER,SERV OR TD
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

+ 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