Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Formula for number format (Excel 2002)

    I have a large database that has people's salaries in one column. Some people are salary, and some are hourly. I'd like to have a formula that shows the dollar amount for the hourly people with 2 decimals (for example $15.42) and the salary people with 0 decimals (for example $56,250). Sounds simple... in the past I simply formatted it by had using a filter, however I'd like to just add it to the cell formula. I tried this formula =IF(C5<100,TEXT(C5,"$#,##0.00"),TEXT(C5,"$#,##0")) and it works, however it's text, and I need it to be a number format. I've also tried this formula =IF(M2<100,ROUND(M2,2),ROUND(M2,0)) and it works, however it doesn't show the dollar sign or the commas in the numbers, and I need that because this data eventually gets fed into personal data sheets that get distributed. With conditional formatting, I only saw an option for color & fonts & patterns and NOT for the "number format"... maybe I'm missing something. Any ideas?
    Thanks!
    Lana

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula for number format (Excel 2002)

    <P ID="edit" class=small>(Edited by sdckapr on 17-Dec-07 19:03. Added PS)</P>How about a custom number format?

    Format Cells - number (tab)
    Custom
    [<100]$#,##0.00;$#,##0

    Steve
    PS
    [<100]$0.00;$#,##0

    would probably work as well, since you won't need the thousands separator if the number is <100...

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Formula for number format (Excel 2002)

    Perfect... thanks so much Steve!!
    Lana

Posting Permissions

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