+ Reply to Thread
Results 1 to 9 of 9

conditional formula in excel 2007

  1. #1
    Registered User
    Join Date
    11-19-2009
    Location
    chennai India
    MS-Off Ver
    Excel 2007
    Posts
    4

    conditional formula in excel 2007

    Hi,
    Can anyone help me how to assign conditional formula in excel 2007. Its like i've variables viz., 1.2, 1.5, 0.7, 0.9, 1.1 & 0.8 in column A (A1-A6) and in column B (B1-B6) i should get the same values if it is <1 and if it is 1 or >1 it should come as 1. Finally the values will be 1.0, 1.0, 0.7, 0.9, 1.0 & 0.8.

  2. #2
    Valued Forum Contributor khamilton's Avatar
    Join Date
    10-08-2009
    Location
    IL
    MS-Off Ver
    Excel 2007
    Posts
    345

    Re: conditional formula in excel 2007

    =if(a1>=1,"1.0",a1)
    put this formula in B1 then drag it across

  3. #3
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: conditional formula in excel 2007

    =if(a1>=1,"1.0",a1)
    put this formula in B1 then drag it across
    That formula will return text and not numeric values
    If you want numeric values,then use:

    =if(a1>=1,1,a1)

    and format the cell to the desired number format.
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: conditional formula in excel 2007

    FWIW MIN is the more efficient approach in these instances (not that you'd notice!)

    =MIN(A1,1)

  5. #5
    Registered User
    Join Date
    11-19-2009
    Location
    chennai India
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: conditional formula in excel 2007

    Dear Khamilton,
    thank you very much. I am getting the things as i requested, but the resulted value of 1 is like text and it is not responding as number... I wanted to sum up all the values in column B... but i could not. Please let me know is there any way to convert this 1 into a numerical one.

  6. #6
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: conditional formula in excel 2007

    Dear Khamilton,
    thank you very much. I am getting the things as i requested, but the resulted value of 1 is like text and it is not responding as number... I wanted to sum up all the values in column B... but i could not. Please let me know is there any way to convert this 1 into a numerical one.
    Yes. Use the formula posted by DonkeyOte or the one I gave you.

  7. #7
    Registered User
    Join Date
    11-19-2009
    Location
    chennai India
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: conditional formula in excel 2007

    Dear all,
    Thank you very much for your prompt reply... My doubt is cleared now. thanks
    Quote Originally Posted by Palmetto View Post
    That formula will return text and not numeric values
    If you want numeric values,then use:

    =if(a1>=1,1,a1)

    and format the cell to the desired number format.

  8. #8
    Registered User
    Join Date
    11-19-2009
    Location
    chennai India
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: conditional formula in excel 2007

    Hi,
    It very simple and easy thank you.
    Quote Originally Posted by DonkeyOte View Post
    FWIW MIN is the more efficient approach in these instances (not that you'd notice!)

    =MIN(A1,1)

  9. #9
    Valued Forum Contributor khamilton's Avatar
    Join Date
    10-08-2009
    Location
    IL
    MS-Off Ver
    Excel 2007
    Posts
    345

    Re: conditional formula in excel 2007

    Hot dog I learned something new again!!!(the min function)
    And Thanks Palmetto for the tip on the text!!

+ 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