Page 2 of 2 FirstFirst 1 2
Results 11 to 13 of 13

Thread: Stuck on a formula - working on a comp plan

  1. #11
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,858
    Articles
    0
    Excel Version
    O365


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

    How about this

    =VLOOKUP(C5/C11,{0,0;0.85,0.005;0.9,0.006;0.95,0.007;1,0.01;1.05,0.015;1.1,0.02},2,TRUE )

  2. #12
    Sooooo close! You did it except you needed to have C11/C5 not C5/C11!

    Once I did that the formula worked great. I don't understand the VLOOKUP function but I will try to read up on it. I never would have thought of that.

    Thank you very much for your help!

  3. #13
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,858
    Articles
    0
    Excel Version
    O365
    The bit in curly brackets is essentially a two-column table, the lower threshold in column 1, the percentage multiple in column 2 (unfortunately you cannot use percentages such as 85%, you have to use fractions of 1), and the VLOOKUP just looks up the result of C11/C5 in column 1, and returns the equivalent value in column 2.

Page 2 of 2 FirstFirst 1 2

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
  •