Friday, January 20, 2012

yogi_Compute Rental Price Based On Specified Date By Period

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user dragosxps said
How can i calculate price based on specified dates

Thank you for reading my post ( i searched before but i haven't been able to find a solution )
in Sheet1 I defined 5 periods and the price for each period + day price
in Sheet2 I defined the period for which i need a price
the price formula should do something like this
1. Identify in which period the specified range is located
2. calculate sum for total period
so in the example of spreadsheet for 01 jan - 23 jan  i have
01 jan - 05 jan - 4 days = 4*25  = 100 +
06 jan - 10 jan = 200+
11 jan - 20 jan = 300 +
21 jan - 23 jan = 2*100 = 200
Total should be = 800
The spreadsheet is public here:
Thanks once again for your time

------------------------------
the computed price of $800 as shown by dragosxps is based on 19 days rental although there are 22 days in rental period ... so in my opinion $800 is in correct. I am going to base it on 22 days rental. Also I am going to assume that rent for 5-Jan to 6-Jan is $50; rent for 10-Jan to 11-Jan is $33.33; and rent for 20-Jan to 21-Jan is $100 (based on prorating per dragosxps' table)