Help for formulas in spreadsheet

[SS]

Let me first explain my problem. I have a booking form used by renters who
rent my holiday villa, they book usually 1 or 2 weeks.
The cost is dependent on the month so there are 4 price bands. So they could
cross a price band ie have a different price for each week.
I have used this forum in the past to get a couple of formulas to do most of
the calculations, but as i don`t really understand the formulas when changes
are required its a no go area for me other than the yearly price increase, I
just change the figure (£s) behind the £s sign. All my changes are linked
to dates and prices. The rest of the stuff ie adding subtracting I can do
ok.

Is there an easier way to do this so that I could make any required changes.

example, and this is a laymans view on this.

I have a calendar say on another page days and month
When a date is entered in the booking form then something like...If = a date
in the calendar then print this price in this cell.

I think I am probably asking too much here but just in case anyone has a
different slant or view on this then no harm in asking.
If you think this is a ridiculous idea then feel free to say so.

thanks

 

[SS]

Hi SS,

This is your business, so it is in your best interests to have a handle on
the business process. You more than anyone should understand the pricing
model. Having said that I don't feel like I understand what you are asking
for after reading your post.

I get the bit about variable pricing. Rate X is in effect on days X1-Xn.
Rate Y is in effect on days Y1-Yn. There are other rates as well.

When you say you "have a booking form used by renters" surely you do not
mean the renters open up your excel workbook and click around on some VBA
form? So what do you mean by this?

IOW, how can we tie your business rules to the situation at hand? And
while we're at it, let's make sure you have a firm grip on the pricing
model and how to maintain it. Maybe you could share what you have now and
what aspects of that you are not comfortable with.

The form is sent as an excel document, they fill in the dates and the rent
is worked out for them, and then print/post back to me, reason being I need
a signature from them.
I use this formula, which due to some alterations is not quite correct, but
works if dates are in the same month.

=IF(DATE(K7,J7,I7)-DATE(G7,F7,E7)>=6,295,0)

If I know the dates they are booking in advance I can manually change the
`295` to read the proper value for the proper month.
An example of how it worked before I messed with it is
say date was june, then the value would be say `275` if one week june and
one week july then charge `295` for july and then add the 2 together for a
total for the 2 weeks.

 

[FloMM2]

SS,
This is what I came up with:
Start Date
Year in Cell E7, Month in Cell F7, Day in Cell G7
End Date
Year in Cell H7, Month in Cell I7, Day in Cell J7
Formula in Cell K7:
"=IF((F7)>=6,295,275)"

This looks at the first week, if it starts in June (6) or later charge 295.
If first week is before June (less than 6) charge 275.

Formula in Cell K9:
"=SUM(K7,K8)"
This will allow Cell E8, F8, G8 for start of second week and Cell H8, I8, J8
for end of second week.
Does this help?

 

[SS]

Ok I can see & understand what you have done and it kind of works.
A couple of issues though...

1. If they only book one week then it also puts a price in for the second
week
2. If say they booked on the last day of May (lower price) so most of the
week is in June ie at the higher price, then they calculation is triggered
by the one day of the previous month so they would be charged the lower
price whereas I would charge them the higher price as the bulk of the days
are in the higher priced month.

Whilst I have very very basic understanding, you can see where this is now
starting to get complicated (for me that is) hence trying to find a simpler
way of doing this. Once I have this set I probably wont need to change again
until May or so of next year and by that time any memory of how it was done
will be long gone.

If it is easier to understand my ramblings then I could send you the sheet.

thanks

 

[herochenna]

Visit www.ExcelGoodies.Com