Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    displaying max number

    Hello all,

    the following formula works great as is. However I want to add the max allowed number accordingly to be displayed if the multiplication results goes over that.

    =if(b2>90,vlookup(t3,tables!a4:b6,2)

    years multil max
    .24 .0577 120
    5 .0769 160
    10 .0961 200

    Thanks all for the help, much appreciated

  2. #2
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    explaing more !!

    Hi all.

    I am not sure if I have explained this well enough in the previous post.

    I want to ADD a function to the existing formula, but I do not know which or if there is one, to give the result of the multiplication up to the max listed in the table by year, then display the max number only. I hope this clears up my request!

    thanks again

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    cvbs,

    I don't see any multiplication that you want to compare the max value to? Please explain further.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts
    RG,

    Sorry .... to many things going on at once ...
    =if(b2>90,vlookup(t3,tables!a4:b6,2)*A10 in the cell A10 is the running total number of hours that the person worked so far this year. So it would multiply that amount times the multiplier (based on then number of years the person has worked) and come up with a result. This formula does this as written.... what I want to do is have the max number shown if the calc. would result to a larger number. So what do I have to add to this formula to accomplish this feat???

    Thanx,

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    This should do the trick, it's long because of the second test. Note: since I don't have your workbook this is AIR code {untested}.
    =if(b2>90,If(vlookup(t3,tables!a4:b6,2)*A10>=vlook up(t3,tables!a4;b6,3),vlookup(t3,tables!a4;b6,3),v lookup(t3,tables!a4:b6,2)*A10),{place what to do if b2 <=90 here})
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts
    is the >= (and the rest of the formula) to be added to the front part??? or are you saying Start with the =vlookup and forget the portion I already have????

  7. #7
    New Lounger
    Join Date
    Jan 2012
    Location
    London
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I suspect the simplest option is something like wrap the formula you have in min:

    =MIN(100,if(b2>90,vlookup(t3,tables!a4:b6,2)*A10))

    This returns the smaller of 100 or your formula, which I think is what you want - it can't return more than 100 in this case. No promises I got all the brackets right It's counter intuitive at first glance to look at the MIN function to set a max, but it's what you want

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by cvbs View Post
    is the >= (and the rest of the formula) to be added to the front part??? or are you saying Start with the =vlookup and forget the portion I already have????
    Copy the whole formula as written.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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