Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    387
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I have a table representing the cost $$ associated with the # of rentals. for instance on a daily basis : rent 1 to 3 $600 each, the 4th rental drops to $400. but the first three remain at $600. the 5th rental drops to $300 but the prices of 1-3 remain at $600 and the 4th remains at $400. if a rental of 6 - 10 the daily rate for all the rentals (1-10) is $2,500.

    I am attaching a schedule to demonstrate the above.

    question is what formula could be used if a number 1-10 is entered to determine the total daily rental?

    Thank you
    Attached Files Attached Files

  2. #2
    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
    If the number of rentals is in A1 you could use:

    =MIN(3,A1)*600+(A1>3)*400+(A1>4)*300

    Steve

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Assuming your number of rentals is in cell B6

    =IF(B8>5,2500,(B8>0)*(B8*600)-(B8>3)*((B8-3)*200)-(B8>4)*((B8-4)*100))


    quick explanation (past the TRUE part of the if statement):

    rentals 1 - 6 are multiplied by 600, then for rentals 4 and 5, 200 is subtracted, and finally for rental 5 another 100 is subtracted.


    Using your current table - to make the formula more dynamic:
    =IF(B8>5,2500,(B8>0)*(B8*$D$4)-(B8>3)*((B8-3)*($D$4-$D$5))-(B8>4)*((B8-4)*($D$4-$D$5-($D$5-$D$6))))

  4. #4
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    387
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thank you both

Posting Permissions

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