Results 1 to 6 of 6
  1. #1
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    IF AND Statements (ExcelXP)

    I have written a spreadsheet for lease applications. The spreadsheet takes the amount of the sale and shows the customer what the lease amount would be. I decided to use the "Choose" function for the 12 different lease factors, and other parameters and all is working fine. As written, the sheet works perfectly using the IF AND for the varying amounts of the lease =IF(AND($F$8>4999,($F$8<10000)),($F$8),""). Now however we have decided to throw another amount into the mix and I need to incorporate another IF AND for example: =IF(AND($J$92>2,($J$92<4. What this does is eliminate a portion of the total lease amount from the time frame of the lease. For example. IF the second factor is 3 then the total amount will only show up in the 36 month area and not in any of the others(24,48,or 60 months).
    HELP!!!! Me thinks I'm in over my head!!!! Will gladly e-mail the spreadsheet if you need a better understanding.
    Thanks All
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IF AND Statements (ExcelXP)

    I think it would help if you could post a small demo workbook with an example of what you have and what you want.

  3. #3
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Re: IF AND Statements (ExcelXP)

    Ok here it is..what I need to incorporate are the airtime amounts.
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: IF AND Statements (ExcelXP)

    I would like to help, but would prefer not to have to reverse-engineer your entire spreadsheet and logic.

    What formula(s) are you trying to fix? What are you really asking for help to do?

    On cursory examination, Instead of choose, INDEX might be more appropriate. Also, it looks like you could use lookup tables and change your formulas to not be hardcoded with numbers, but look up ranges. You might be able to eliminate the IF ANDs all together.

    If you could be a little more specific in your request, I think we could help.
    Steve

  5. #5
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Re: IF AND Statements (ExcelXP)

    Thanks Steve, will think about the change from Choose to Lookup. What follows is a better explanation of what I am trying to accomplish.
    On the Second sheet in the workbook, Cell F6 is directly linked to the first sheet (although on the demo I realize that it really isn't). The customer then chooses from the different lease rate plans shown next to Cell D1. At that point, the lease factors all change Cells E15 thru E 40) and shows the customer what his payment would be for either 24, 36, 48, or 60 months. All of that functions properly.
    Now I want to incorporate the air time amount into that total. The Airtime is chosen from the different plans shown under "Select Airtime Lease Period". Now here is where it gets problematic. As is, the Lease amount is the total of Cells F6 & F7. That amount shows in the columns correctly (Cells D15 thru D40). If a customer chooses say a 24month plan from the airtime lease rates, I am trying to figure out how to NOT show the lease amounts for the 36 month, 48 month, or 60 month. Or if they choose a different plan, say 36 month then the amount would be ignored by the 24,48, or 60 month Lease rates. Hope that clarifies it a little better.
    Thanks
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: IF AND Statements (ExcelXP)

    If you are not going to show all the info for the different rates, why not only display the one line of interest and not include the whole table?

    Also, "plan Names" combo box has 12 items. Personally, I would suggest 2 comboboxes: one for the 3 "plans" and another for the 4 rates.

    Thus you choose a plan, choose a rate, choose a lease period, enter a tax, and you display one line of info:
    Volume, Terms, Amt. financed, Factor, Payment, tax, payment(with tax)

    You could even "hide" this row if you selected "no lease period".. You could even "hide" the last 2 cols if no tax was entered.

    Is this what you are after?

    Steve

Posting Permissions

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