Help with formula ???
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Help with formula ???

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I'm new to this board and hope I can find the help I need to solve some basic problems I have with understanding Excel.

    I'm working on a project comparing proposed cost vs current cost. I would like to enter a logic formula as follows.

    If the units in a column are less than 100 the rate of $193.00 would display. If the units are between 100-300 than a rate of 194.00 would display. This same logic would follow for 300-500, 500-700 and over 700 units with increasing rate allpying to each level.

    Any help would be appreciated.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    If the units in a column are less than 100 the rate of $193.00 would display. If the units are between 100-300 than a rate of 194.00 would display. This same logic would follow for 300-500, 500-700 and over 700 units with increasing rate allpying to each level.

    This formula will do what you want:

    =IF(A1<100,193,IF(A1<300,194,IF(A1<500,195,IF(A1<700,196,197))))

    Caveat: Excel will only let you next six functions. If you have a lot of categories, you can use a lookup table:

    put in D1 to E5:
    D1: 0, D2: 100, D3: 300, D4: 500, D5: 700
    E1: 193, E2: 194, E3: 195, E4: 196, E5: 197

    Now =VLOOKUP(A1,$D$1:$E$5,2,TRUE)
    will get you what you want. You need
    to make sure that the numbers in D1:D5
    are ascending, but this method will handle
    an indefinite number of categories.



    "Interfere? Of course we should interfere! Always do what you're best at, that's what I say!" -- The Doctor, Nightmare of Eden

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
  •  

 

 
DMCA.com