Results 1 to 5 of 5

Thread: Using Multiple If Statements in Custom Column

  1. #1

    Using Multiple If Statements in Custom Column



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi Folks,

    I've ran into a problem that seems to require having two "If" statements within the same custom column. Here is a very simplified example of the code:

    =if [Price] = 25
    then [Price] * 3
    else [Price]


    if [Price] = 26
    then [Price] * 3
    else [Price]

    I can't figure out the syntax needed to join these two statements together. Is this even possible to have multiple "If" statements in the same custom column? I'm by no means a computer expert, so many apologies if this is annoyingly simple.

    Best,

    Andrew

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,261
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hey there, how about this?

    Code:
    =if [Price] = 25 or [Price] = 26
    then [Price] * 3
    else [Price]
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Hi Ken,

    I may have over simplified my problem. Lets try this example:

    Fund BFY Converted BFY
    AF 2018 1821
    PT 2018 18XX
    RF 2018 18XX
    WF 2018 1821

    If Fund = Af and BFY = 2018 perform the function that converts to 1821
    If Fund = PT and BFY = 2018 perform the function that converts to 18XX
    If Fund = RF and BFY = 2018 perform the function that converts to 18XX
    If Fund = WF and BFY = 2018 perform the funtion that converts to 1821

    I've made the functions that do the converting, my problem is getting Power Query to decide when to use each one. This ends up feeding into Power Pivot, so it's kinda handy if it can all stay in one column as well. Thank you for your help. It is greatly appreciated. Had I not read your book, I would not even being attempting these types of things. I highly recommend all excel users read M is for Data Monkey, it will fundamentally change the way you use excel.

    Best,

    Andrew

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,261
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hi Andrew,

    I'm not sure how your functions are set up, but providing they each take one parameter, you could try something like this:

    Code:
    if [Fund] = [Af] and [BFY] = 2018
        then fx1821(var1)
    else if [Fund] = [PT] and [BFY] = 2018
        then fx18XX(var1)
    else if [Fund] = [RF] and [BFY] = 2018
        then fx18XX(var1)
    else if [Fund] = [WF] and [BFY] = 2018 
        then fx1821(var1)
    else
        "none of tests were met"
    (Written in browser and untested, but hopefully it gives you the structure you need)
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,261
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Truth be told, looking at it afterwards, you could probably also get away with testing if [BFY]=2018, and then nesting a test within that:

    Code:
    if [BFY] = 2018then
    	(
    	if [Fund] = [Af]
    		then fx1821(var1)
    	else if [Fund] = [PT]
    		then fx18XX(var1)
    	else if [Fund] = [RF]
    		then fx18XX(var1)
    	else if [Fund] = [WF]
    		then fx1821(var1)
    	else
    		"Fund test not met"
    	)
    else
    	"BFY is not 2018"
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Tags for this Thread

Posting Permissions

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