+ Reply to Thread
Results 1 to 7 of 7

If cell = certain number, output new value in next column

  1. #1
    Registered User
    Join Date
    04-04-2020
    Location
    North Carolina
    MS-Off Ver
    2016
    Posts
    6

    If cell = certain number, output new value in next column

    Ok so Basically I am creating a scorecard and I am looking for a formula that will look at the value of a cell in Column D and if that value in Column D = a certain number then I want it to give a new number in Column E as a result. So if D4 = 0 then I want E4 to = 28; If D4 = 1 or 2 I want E4 to = 14, and if D4 = anything greater than 2 I want E4 to = 0.

    Is this possible? How can I do this?
    Last edited by AliGW; 05-23-2020 at 11:25 AM. Reason: Irrelevant section of title removed: this is a HELP forum!!!

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    37,686

    Re: If cell = certain number, output new value in next column

    Yes, and probably best done with a lookup table. Exactly how will require a sample workbook showing us your layout.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    05-23-2020
    Location
    Guwahati, Assam, India
    MS-Off Ver
    2019
    Posts
    16

    Re: If cell = certain number, output new value in next column

    You can use this formula

    =IF(D4=0,24,IF(D4=1,14,IF(D4=2,14,"")))
    Asha Kanta Sharma
    Guwahati, India

  4. #4
    Valued Forum Contributor
    Join Date
    04-01-2015
    Location
    The Netherlands
    MS-Off Ver
    2003/2007/2010/2016/office 365
    Posts
    415

    Re: If cell = certain number, output new value in next column

    Deleted
    Misread
    Last edited by Vraag en antwoord; 05-23-2020 at 11:46 AM.

  5. #5
    Registered User
    Join Date
    04-04-2020
    Location
    North Carolina
    MS-Off Ver
    2016
    Posts
    6

    Re: If cell = certain number, output new value in next column

    That worked great! Thanks. I was using something similar but missed a few.

    Would this work similar if I wanted to do a range in the formula. For example IF D4 is between 0-3 then E4 = 10?

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    37,686

    Re: If cell = certain number, output new value in next column

    As I said, it would bee better with a lookup table, as it will be easier to maintain and update going forward.

  7. #7
    Registered User
    Join Date
    05-08-2020
    Location
    Lublin, Poland
    MS-Off Ver
    2007, 2010, 2019
    Posts
    14

    Re: If cell = certain number, output new value in next column

    Quote Originally Posted by supersoccrsqueak View Post
    That worked great! Thanks. I was using something similar but missed a few.

    Would this work similar if I wanted to do a range in the formula. For example IF D4 is between 0-3 then E4 = 10?
    Something like this
    IF(AND(D4>=0,D4<=3),10,"something else")

    But an empty cell is considered to be equal to zero in numerical comparisons, so the above formula will return 10 also for D4 being empty. If you want to exclude empty cells, try
    =IF(AND(NOT(ISBLANK(D4)),D4>=0,D4<=3),10,"something else")
    Przemyslaw Kowalik, Lublin Univ. of Technology

+ 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