+ Reply to Thread
Results 1 to 11 of 11

Exchange a number for a letter forumla

  1. 08-08-2009, 01:21 PM #1
    jasper1106
    jasper1106 is offline
    Registered User
    Join Date
    08-08-2009
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    7

    Exchange a number for a letter forumla

    Hi guys,

    This has been driving me crazy and after several hours of following tutorials I am at my wits end. Can one of you genius Excel folk help me out?

    I have a sheet which calculates payment amounts.

    Column titles:
    Hours | Rate of Pay | Total

    In the hours column usually the entries consist of numbers and everything works fine. However when an employee is on holiday they are still paid.

    What I want to do is be able to enter the letter "H" for one of the entries in the hours column. The sheet to translate this as 2 hours.

    H=2 x rate of pay = total

    I cannot for the life of me get the correct formula to in order to achieve this. I don't particularly want to use a macro for this and others have suggested the "COUNTIF" function.

    Anyone any ideas?
    Sorry if I haven't explained this too well!
    Last edited by jasper1106; 08-08-2009 at 01:33 PM.
    Reply With QuoteRegister To Reply
  2. 08-08-2009, 01:26 PM #2
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Exchange a number for a letter forumla

    Posting a sample file is likely to help but Total would be along lines of:

    C2: =SUM(A2,2*(A2="H"))*B2
    My Recommended Reading:

    Volatility
    Sumproduct & Arrays
    Pivot Intro
    Email from XL - VBA & Outlook VBA
    Function Dictionary & Function Translations
    Dynamic Named Ranges
    Reply With QuoteRegister To Reply
  3. 08-08-2009, 01:55 PM #3
    jasper1106
    jasper1106 is offline
    Registered User
    Join Date
    08-08-2009
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Exchange a number for a letter forumla

    Hi I have tried that formula you suggested but seem to get an error code?
    I have attached a template, basically if the letter H is entered in any of the cells Mon-Friday I want it to count as a value of 2 (which will then hopefully transfer into the totals columns) At the moment I have done all the formula for if Mon-Friday was a basic of 2 each day but can't work out how to keep the basic of 2 hours if "H" is entered. Not sure if I am explaining this very well.

    Any suggestions will be greatly appreciated.

    Book1.xls
    Reply With QuoteRegister To Reply
  4. 08-08-2009, 01:59 PM #4
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Exchange a number for a letter forumla

    Perhaps, based on your file layout:

    H11: =SUM($C11:$G11)+(COUNTIF($C11:$G11,"H")*2)
    copied down accordingly

    or in more convoluted fashion

    H11: =SUMPRODUCT(0+SUBSTITUTE(0&C11:G11,"0H",2))

    I would opt for the former...
    Last edited by DonkeyOte; 08-08-2009 at 02:01 PM.
    Reply With QuoteRegister To Reply
  5. 08-08-2009, 02:11 PM #5
    jasper1106
    jasper1106 is offline
    Registered User
    Join Date
    08-08-2009
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Exchange a number for a letter forumla

    Ok, I think this may be going a bit beyond me now, you will have to bear with me... so if I use as you suggest: H11: =SUMPRODUCT(0+SUBSTITUTE(0&C11:G11,"0H",2)) how/where are you suggesting I enter this? Sorry I am a little confused... do I need to go into the Conditional Format menu?
    Reply With QuoteRegister To Reply
  6. 08-08-2009, 02:12 PM #6
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Exchange a number for a letter forumla

    I would advise you use the first formula in this instance and insert into the cell denoted (H11)
    Reply With QuoteRegister To Reply
  7. 08-08-2009, 02:15 PM #7
    jasper1106
    jasper1106 is offline
    Registered User
    Join Date
    08-08-2009
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Exchange a number for a letter forumla

    Oh wonderful!!! It worked... I was being rather thick and just opened the wrong template so the cell locations were wrong. I have obviously been staring at the computer for far too long. I have used the second one you suggested... it is brilliant... thankyou so much for your help... that has truly been bugging me.

    Many Thanks,

    Jasper1106
    Reply With QuoteRegister To Reply
  8. 08-08-2009, 02:30 PM #8
    jasper1106
    jasper1106 is offline
    Registered User
    Join Date
    08-08-2009
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Exchange a number for a letter forumla

    I am back again... that all works wonderfully, apart from e.g if I place H in Week 1 in Monday (ie C11) it changes the TOTAL HOURS cell (H11) but doesn't change the TOTAL HOURS in cell (C19) to 2 hours it stays as 0, is there anyway this can change to 2 aswell?
    Reply With QuoteRegister To Reply
  9. 08-08-2009, 03:55 PM #9
    jasper1106
    jasper1106 is offline
    Registered User
    Join Date
    08-08-2009
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Exchange a number for a letter forumla

    Hi ya,

    I am still having problems with the bottom row of totals (row 19)
    If I place "H" in Mon (cell C11) it changes the TOTAL HOURS in (cell H11) but doesn't change the TOTAL HOURS in (cell C19) to 2 hours? the value stays as 0, is there anyway this can change to 2 aswell?

    Any help would be greatly appreciated.

    Jasper1106
    Reply With QuoteRegister To Reply
  10. 08-08-2009, 04:54 PM #10
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Exchange a number for a letter forumla

    A case of transposing the logic (ranges), ie

    C19: =SUM(C$11:C$18)+(COUNTIF(C$11:C$18,"H")*2)
    copied across

    or

    C19: =SUMPRODUCT(0+SUBSTITUTE(0&C$11:C$18,"0H",2))
    copied across
    Reply With QuoteRegister To Reply
  11. 08-08-2009, 04:59 PM #11
    jasper1106
    jasper1106 is offline
    Registered User
    Join Date
    08-08-2009
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Exchange a number for a letter forumla

    Great! Thankyou... not sure what I was doing wrong there I think I missed a ( out somewhere. Thank-you very much for all your help today.. it is much appreciated.

    Jasper1106
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1