1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

excel 2003 IF AND functions choose?

Discussion in 'Business Applications' started by CaliMac10, Jun 25, 2010.

Thread Status:
Not open for further replies.
  1. CaliMac10

    CaliMac10 Thread Starter

    Joined:
    Jun 25, 2010
    Messages:
    1
    Hi im trying to allocate costs for a large number of customers where they are charged by lot size. here is some of sq.ft data and rate data from the Sheet2 tab

    Sq. Ft.
    7560
    7200
    14842
    11277
    7216
    8860
    15211
    16506
    30999
    23723
    7626
    20915
    10530

    Sq. Ft.Fixed
    <10000 $3.15
    <15000 $3.20
    <20000 $4.26
    <25000 $4.33
    <30000 $5.38
    <35000 $5.42
    <40000 $6.48

    so 9000 sq.ft. returns $3.15, 23000 sq.ft. returns $4.33 etc...

    I have the list in a seperate tab with everyones given square footage. I have used the IF AND function to determine if that customer is in one particular range, say between 10,000 and 15,000, but i am lost at how to combine formulas so that excel can choose which range the given customer is in so that it can return the correct fixed amount.

    these are my functions, but how do i combine these so that it can be in one column instead of required 7 different columns for every sq.ft. range and rate formula?

    =IF(D3<Sheet2!$A$2,Sheet2!$B$2,0)
    =IF(AND(D3>Sheet2!$A$2,D3<Sheet2!$A$3),Sheet2!$B$3)
    =IF(AND(D3>Sheet2!$A$3,D3<Sheet2!$A$4),Sheet2!$B$4)
    =IF(AND(D3>Sheet2!$A$4,D3<Sheet2!$A$5),Sheet2!$B$5)
    =IF(AND(D3>Sheet2!$A$5,D3<Sheet2!$A$6),Sheet2!$B$6)
    =IF(AND(D3>Sheet2!$A$6,D3<Sheet2!$A$7),Sheet2!$B$7)
    =IF(AND(D3>Sheet2!$A$7,D3<Sheet2!$A$8),Sheet2!$B$8)
     
  2. cwwozniak

    cwwozniak Trusted Advisor Spam Fighter

    Joined:
    Nov 28, 2005
    Messages:
    60,881
    First Name:
    Chuck
    Hi CaliMac10, and welcome to TSG.

    You might want to use the VLOOKUP function in Excel to do what you want.
     
  3. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    I tend to agree with cwwozniak , especially if the values might change at times - much easier to change in a list than in a formula. However, this might work for you:
    =IF(D3<Sheet2!$A$2,Sheet2!$B$2,IF(AND(D3>Sheet2!$A$2,D3<Sheet2!$A$3),Sheet2!$B$3,IF(AND(D3>Sheet2!$A$3,D3<Sheet2!$A$4),Sheet2!$B$4,IF(AND(D3>Sheet2!$A$4,D3<Sheet2!$A$5),Sheet2!$B$5,IF(AND(D3>Sheet2!$A$5,D3<Sheet2!$A$6),Sheet2!$B$6,IF(AND(D3>Sheet2!$A$6,D3<Sheet2!$A$7),Sheet2!$B$7,IF(AND(D3>Sheet2!$A$7,D3<Sheet2!$A$8),Sheet2!$B$8)))))))
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/931516

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice