+ Reply to Thread
Results 1 to 11 of 11

IF function with multiple variables

  1. #1
    Registered User
    Join Date
    07-10-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    4

    IF function with multiple variables

    Hi guys

    I am a complete newbie to this forum so please go easy.

    I have created a drop down list on my spreadsheet with the the following: Yearly, Quarterly, Monthly, Fortnightly, Weekly, Daily

    I have set up a cell so that when you select one of the above words, 2 numbers are multiplied and then multipled again by a 3rd number with the 3rd number being the only one that changes depending on what word from the list you choose

    My problem is that for the first three words (Yearly, Quarterly, Monthly) everything works perfectly, with the correct result showing up in the cell but for the rest I get $0.00 but it shouldn't be

    Here is the formula I am using:

    =IF(F6="Yearly",(D6*E6)*'Budget Calc'!C2)+IF(F6="Quarterly",(D6*E6)*'Budget Calc'!C3)+IF(F6="Monthly",(D6*E6)*'Budget Calc'!C4)+IF(F6="Fortnightly",(D6*E6)*'Budget Calc'!C5)+IF(F6="Weekly",(D6*E6)*'Budget Calc'!C6)+IF(F6="Daily",(D6*E6)*'Budget Calc'!C7)

    As you can see it is very repetitive so I am not sure why the last last 3 words dont produce the desired result.

    Any help on this would be greatly appreciated

    Thanks in advance

    Se7en

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: IF function with multiple variables

    If I copied everything correctly, maybe...

    =CHOOSE(MATCH(F6,{"Daily","Fortnightly","Monthly","Weekly","Yearly"},0),'Budget Calc'!C7,'Budget Calc'!C5,'Budget Calc'!C4,'Budget Calc'!C6,'Budget Calc'!C3)*(D6*E6)
    HTH
    Regards, Jeff

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: IF function with multiple variables

    I think I would use a table.

    On your Budget Calc sheet list the intervals in B2:B7.

    B2 = Yearly
    B3 = Quarterly
    B4 = Monthly
    B5 = Fortnightly
    B6 = Weekly
    B7 = Daily

    Then, your formula becomes:

    =(D6*E6)*SUMIF('Budget Calc'!B2:B7,F6,'Budget Calc'!C2:C7)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    06-29-2013
    Location
    Quezon City, Philippines
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: IF function with multiple variables

    Use this formula:

    =IF(F6="Yearly",((D6*E6)*'Budget Calc'!C2),IF(F6="Quarterly",((D6*E6)*'Budget Calc'!C3),IF(F6="Monthly",((D6*E6)*'Budget Calc'!C4),IF(F6="Fortnightly",((D6*E6)*'Budget Calc'!C5),IF(F6="Weekly",((D6*E6)*'Budget Calc'!C6),IF(F6="Daily",((D6*E6)*'Budget Calc'!C7),""))))))

    This will certainly give out the desired values. I have attached a sample file using nested
    IF function.
    Attached Files Attached Files

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: IF function with multiple variables

    Or, this...

    =(D6*E6)*VLOOKUP(F6,'Budget Calc'!B2:C7,2,0)

  6. #6
    Registered User
    Join Date
    07-10-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: IF function with multiple variables

    Thanks heaps for your responses guys but I am still having trouble with it. Im not sure if your still out there Krisnarag but using your formula I managed to almost get it, with all but 2 options working. When I select fortnightly or weekly I still get no result. I downloaded your example spreadsheet and saw it worked perfectly for all drop down options.

    I have re-worked the formula 100 times and I just can't figure out what it could be, its exactly the same as yours Krisnarag but its just not working for all of the options.

    Any ideas as to what is going on??

    Thanks

    Seven

  7. #7
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: IF function with multiple variables

    Can you post the file (or a SMALL sample file) that shows the formula you're trying that isn't working?

  8. #8
    Registered User
    Join Date
    07-10-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: IF function with multiple variables

    Hi Tony

    Thanks for your reply.

    The file is attached below, any help on this would be fantastic.

    Workbook3.xlsx

    Its just the first 2 tabs that I need assistance with; budget template and budget calc.

    As you will see the yearly figure to the right adjusts correctly for all the different drop down options on the left except for weekly and fortnightly.

    Thanks

    se7en

  9. #9
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: IF function with multiple variables

    Here's what I did/found...

    On the Budget Calc sheet in cells D5 (Fortnightly) and D6 (Weekly) there were trailing space characters such that the entries were:

    Fortnightly[space]
    Weekly[space]

    I got rid of those spaces. Then I gave the range Budget Calc D2:D7 the defined name of Period.

    Back on the Budget (Template) sheet in cell F6 I setup a data validation drop down list and used the named range Period as the list source.

    Your formula in cell T6 now returns the correct result:

    =IF(F6="Yearly",((D6*E6)*'Budget Calc'!C2),IF(F6="Quarterly",((D6*E6)*'Budget Calc'!C3),IF(F6="Monthly",((D6*E6)*'Budget Calc'!C4),IF(F6="Fortnightly",((D6*E6)*'Budget Calc'!C5),IF(F6="Weekly",((D6*E6)*'Budget Calc'!C6),IF(F6="Daily",((D6*E6)*'Budget Calc'!C7),""))))))

    I put my formula in cell U6 and it also returns the correct result:

    =(D6*E6)*SUMIF('Budget Calc'!D2:D7,F6,'Budget Calc'!C2:C7)

    Here's your file with these changes implemented:

    Workbook3(1).xlsx

  10. #10
    Registered User
    Join Date
    07-10-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: IF function with multiple variables

    Perfect! thank you so much Tony, this is a huge help!! Champion!

    Cheers

  11. #11
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: IF function with multiple variables

    You're welcome. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

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