+ Post New Thread
Results 1 to 5 of 5
General Chat Thread, Excel help required in General; ...
  1. #1
    JoeBloggs's Avatar
    Join Date
    Jun 2010
    Location
    Manchester
    Posts
    558
    Thank Post
    170
    Thanked 78 Times in 55 Posts
    Rep Power
    36

    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,405
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    169
    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,824
    Thank Post
    372
    Thanked 544 Times in 507 Posts
    Rep Power
    184
    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
    3,395
    Thank Post
    301
    Thanked 917 Times in 684 Posts
    Rep Power
    346
    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
    Manchester
    Posts
    558
    Thank Post
    170
    Thanked 78 Times in 55 Posts
    Rep Power
    36
    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, 09:23 AM
  2. help required for my two sons new pcs
    By edie209 in forum General Chat
    Replies: 32
    Last Post: 17th December 2007, 11:11 AM
  3. Exchange 2003 Novice - Help Required
    By triggmiester in forum How do you do....it?
    Replies: 1
    Last Post: 26th July 2007, 10:12 PM
  4. Excel "HELP"
    By mahlon in forum Windows
    Replies: 3
    Last Post: 4th May 2007, 12:15 PM
  5. help required for school charity fundraiseing Event(
    By rama1712 in forum General Chat
    Replies: 9
    Last Post: 20th April 2007, 11: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
  •