Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Clever formula

  1. #1
    New Member
    Join Date
    Nov 2014
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Clever formula

    Hello

    Can anyone help me write a single formula that will solve for the total fee to be deducted given this fee card:

    First 100 - 1.25%
    Next 200 - 1.00%
    Next 400 - 0.75%
    Balance above 700 - 0.50%


    I have only been able to do it so far using a range of cells.

    Many thanks
    Tom

  2. #2
    Board Regular
    Join Date
    Apr 2004
    Posts
    7,895
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Clever formula

    something along the lines of

    =if(a1<101,(a1*1.25)/100,if(a1<301,(a1*1.00)/100,if(a1<701,(a1*0.75)/100,(a1*0.5)/100))))
    Yesterday I felt on top of the world. Today its falling in on me.

  3. #3
    Board Regular
    Join Date
    Apr 2003
    Location
    England
    Posts
    5,872
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Clever formula

    Or maybe
    =A1*LOOKUP(A1,{100,200,400,700},{0.0125,0.01,0.0075,0.005})
    To post screen shots download the MrExcel HTML Maker
    Look here for other options Recommended Add-ins and Links

  4. #4
    Board Regular Kaper's Avatar
    Join Date
    Mar 2014
    Location
    Warsaw, Poland
    Posts
    227
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Clever formula

    I'd probably say:
    Code:
    =if(a1<=100,a1*1.25%,if(a1<=300,(a1-100)*1%+1.25,if(a1<=700,(a1-300)*0.75%+3.25,(a1-700*0.5%+6.25))))
    Help us to help you - see also: posting guidelines, follow forum rules and terms of use. Try searching for your answer first, see how

  5. #5
    Board Regular
    Join Date
    Apr 2003
    Location
    England
    Posts
    5,872
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Clever formula

    Think i made a mistake!

    =A1*LOOKUP(A1,{0,101,301,701},{0.0125,0.01,0.0075,0.005})
    To post screen shots download the MrExcel HTML Maker
    Look here for other options Recommended Add-ins and Links

  6. #6
    New Member
    Join Date
    Nov 2014
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Clever formula

    Quote Originally Posted by SteveO59L View Post
    something along the lines of

    =if(a1<101,(a1*1.25)/100,if(a1<301,(a1*1.00)/100,if(a1<701,(a1*0.75)/100,(a1*0.5)/100))))

    Thanks
    Not sure that this takes into account the Income tax style tiers.

    I have done this but it is returning #VALUE!

    PHP Code:
    =IF(A6>=C3,((A6-C3)*0.005)+62500),IF(A6>=C2,((A6-C2)*0.0075)+32500),IF(A6>=C1,((A6-C1)*0.01)+12500),IF(A6<=C1,A6*0.0125,"ERROR"
    That is with different numbers.


    Thanks a lot

  7. #7
    Board Regular
    Join Date
    Apr 2003
    Location
    England
    Posts
    5,872
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Clever formula

    Did you try
    =A6*LOOKUP(A6,{0,101,301,701},{0.0125,0.01,0.0075,0.005})
    To post screen shots download the MrExcel HTML Maker
    Look here for other options Recommended Add-ins and Links

  8. #8
    Board Regular
    Join Date
    Apr 2003
    Location
    England
    Posts
    5,872
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Clever formula

    Or with a table in D1:E4

    =A6*LOOKUP(A6,D1:D4,E1:E4)

    Code:
    0 1.25%
    101 1.00%
    301 1%
    701 0.50%
    To post screen shots download the MrExcel HTML Maker
    Look here for other options Recommended Add-ins and Links

  9. #9
    New Member
    Join Date
    Nov 2014
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Clever formula

    Quote Originally Posted by gaz_chops View Post
    Or with a table in D1:E4

    =A6*LOOKUP(A6,D1:D4,E1:E4)

    Code:
    1.25%
    101 1.00%
    301 1%
    701 0.50%
    Thanks

    I'm not sure that the LOOKUP takes into account the fact that the first 100 is charged at 1.25% and the next x amount is charged at 1% (up until 300), and so on.


    Thanks again

  10. #10
    Board Regular
    Join Date
    Apr 2003
    Location
    England
    Posts
    5,872
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Clever formula

    Ah got you.

    Quote Originally Posted by kellta View Post
    Thanks

    I'm not sure that the LOOKUP takes into account the fact that the first 100 is charged at 1.25% and the next x amount is charged at 1% (up until 300), and so on.


    Thanks again
    To post screen shots download the MrExcel HTML Maker
    Look here for other options Recommended Add-ins and Links

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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