+ Post New Thread
Results 1 to 5 of 5
General Chat Thread, Excel help required in General; Annual Bonus Rate 3% 3% 100,000.00 4% 115,000.00 5% 125,000.00 6% 140,000.00 Annual Banked Target 150,000 7% 150,000.00 When someone ...
  1. #1
    JoeBloggs's Avatar
    Join Date
    Jun 2010
    Location
    Leeds
    Posts
    544
    Thank Post
    160
    Thanked 75 Times in 52 Posts
    Rep Power
    35

    Excel help required

    Annual Bonus Rate 3% 3% 100,000.00
    4% 115,000.00
    5% 125,000.00
    6% 140,000.00
    Annual Banked Target 150,000 7% 150,000.00


    When someone puts a figure in box B5 (banked target) I want it to change the percentage to whatever it should be according to the right. So less than 100k = 0%, 100k-114,999.99 = 3% and so on
    Attached Files Attached Files

  2. #2

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,403
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    168
    Try this =IF(B5>=100000,VLOOKUP(B5,D1:E5,2,TRUE),0)

  3. Thanks to CESIL from:

    JoeBloggs (21st December 2011)

  4. #3

    Steve21's Avatar
    Join Date
    Feb 2011
    Location
    Swindon
    Posts
    2,686
    Thank Post
    334
    Thanked 513 Times in 481 Posts
    Rep Power
    178
    If you don't mind it being a bit long-winded

    =IF(B5>=F5,E5,IF(B5>=F4,E4,IF(B5>=F3,E3,IF(B5>=F2, E2,IF(B5>=F1,E1,0)))))

    But could do it fancy with look-ups etc

    Steve

  5. Thanks to Steve21 from:

    JoeBloggs (21st December 2011)

  6. #4


    Join Date
    May 2009
    Posts
    2,878
    Thank Post
    258
    Thanked 767 Times in 582 Posts
    Rep Power
    269
    Use vlookup. Put the %'s in column G (at the other side of the figures). The % can then be looked up by putting =vlookup(B5,F1:G5,2,TRUE) in cell B1. The TRUE param tells vlookup to make an inexact match, it will match to the closest figure below whatever you put in B5. So 114,000 will match to 3%, anything above 150K will match to G5 and anything below 100K will return N/A.

  7. Thanks to pcstru from:

    JoeBloggs (21st December 2011)

  8. #5
    JoeBloggs's Avatar
    Join Date
    Jun 2010
    Location
    Leeds
    Posts
    544
    Thank Post
    160
    Thanked 75 Times in 52 Posts
    Rep Power
    35
    thanks guys got it working

SHARE:
+ Post New Thread

Similar Threads

  1. [MS Office - 2003] Excel expert required - Help please
    By elsiegee40 in forum Office Software
    Replies: 14
    Last Post: 17th May 2010, 08:23 AM
  2. help required for my two sons new pcs
    By edie209 in forum General Chat
    Replies: 32
    Last Post: 17th December 2007, 10:11 AM
  3. Exchange 2003 Novice - Help Required
    By triggmiester in forum How do you do....it?
    Replies: 1
    Last Post: 26th July 2007, 09:12 PM
  4. Excel "HELP"
    By mahlon in forum Windows
    Replies: 3
    Last Post: 4th May 2007, 11:15 AM
  5. help required for school charity fundraiseing Event(
    By rama1712 in forum General Chat
    Replies: 9
    Last Post: 20th April 2007, 10:22 PM

Thread Information

Users Browsing this Thread

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

Posting Permissions

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