+ Reply to Thread
Results 1 to 6 of 6

Nested IF or Vlookup?

  1. #1
    Registered User
    Join Date
    12-14-2011
    Location
    Florida / Dallas
    MS-Off Ver
    excel 20120
    Posts
    5

    Nested IF or Vlookup?

    I am trying to take a number and fit it in a group and multiply all preceding groups and the existing group by a multiplier and all other numbers in the table be 0.

    For instance if my number is 313 then I will have 150 in the summary field for that row and 350 in the next and then I will take the minimum of 201 which is 112 and multiply it by 1.35 and the next two results will be zero.

    6-19-2013 7-31-55 AM.jpg

    this is the formula I was using where column H is the first column you see.

    Please Login or Register  to view this content.
    Any help would be appreciated as I don't do this often and I had no success with the Vlookup but I think it might be able to work with that formula as well.

    Jeremy

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Nested IF or Vlookup?

    Try

    =SUMPRODUCT(--(A1>{0,75,200,500,1000,9999}), (A1-{0,75,200,500,1000,9999}), {0,2,-0.25,-0.40,-0.35,-0.75})

    http://www.mcgimpsey.com/excel/variablerate.html
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Registered User
    Join Date
    12-14-2011
    Location
    Florida / Dallas
    MS-Off Ver
    excel 20120
    Posts
    5

    Re: Nested IF or Vlookup?

    I can't seem to make this work. I am attaching a sample of the file so you see my problem. I want to compare $c$9 to Column H and Column I. If the number is higher than column j then I want to multiply the amount of $C$9 by the value in column Q by K. If the number falls in the range of H to I then I want to multiply the amount over H by column K. All other rows should return a zero.

    Hope this gets me a bit closer to a solution.

    savings calc.xlsx

  4. #4
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Nested IF or Vlookup?

    In N3

    =MIN(J3,$C$9)*($C$9>=H3)*K3

    Copy down

  5. #5
    Registered User
    Join Date
    06-20-2013
    Location
    Northern CA
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Nested IF or Vlookup?

    Jeremy, try the following formula: =IF(AND(C7/12>=$H$3,C7/12<=$I$3),C7/12*$K$3,IF(AND(C7/12>=$H$4,C7/12<=$I$4),C7/12*K4,"")) IF this works, you will need to continue inserting "if"s were the ,"" starts.

  6. #6
    Registered User
    Join Date
    12-14-2011
    Location
    Florida / Dallas
    MS-Off Ver
    excel 20120
    Posts
    5

    Re: Nested IF or Vlookup?

    This got me close and with a modification it did the job, Thanks

+ 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