I am seraching for a formula to compare the prices of hotels putting the
dates of beginning and end of my holidays in two Excel cells. The Hotels have
different price during the summer like:

Jun-20/Jul-25 Jul-25/Aug-1 Aug-1/Aug-31 Aug-31/sep-30
Hotel A $ 50 $ 90 $ 150 $ 40
Jun-20/Jul-20 Jul-20/Aug-10 Aug-10/Aug-20 Aug-20/sep-15
Hotel B $ 45 $ 99 $ 140 $ 60

What are the costs from i.e. Jun-25 until Aug-10?
Thank you for who wil help me.
--
dilettante

I think you will have to resort to VBA to solve this (but no doubt some
formula expert will prove me wrong!).

"dilettante" wrote:

I am seraching for a formula to compare the prices of hotels putting the
dates of beginning and end of my holidays in two Excel cells. The Hotels have
different price during the summer like:

Jun-20/Jul-25 Jul-25/Aug-1 Aug-1/Aug-31 Aug-31/sep-30
Hotel A $ 50 $ 90 $ 150 $ 40
Jun-20/Jul-20 Jul-20/Aug-10 Aug-10/Aug-20 Aug-20/sep-15
Hotel B $ 45 $ 99 $ 140 $ 60

What are the costs from i.e. Jun-25 until Aug-10?
Thank you for who wil help me.
--
dilettante

If you use dates as if they were amounts, then this problem is described with solutions he

http://www.mcgimpsey.com/excel/variablerate.html

Subtract the price for the start date from the price on the end date.

In your case, for Hotel A, I got 3480: 30 days at 50, 7 at 90 and 9 at 150
The formulas gave me 3730 - 250, again 3480.

Instead of the formulas shown in the link above, you can also use a UDF; text at the end of the message.
If you're new to UDF's there is also an instruction on how to use them.


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

' ================================================== =======
Function PercPerSegment(Amount As Double, Table As Range) As Double
' Niek Otten, March 31, 2006

' Progressive pricing
' First argument is the quantity to be priced
' or the amount to be taxed
' Second argument is the Price or Tax% table (vertical)
' Make sure both ends of the table are correct;
' usually you start with zero and the corresponding price or %
' Any value should be found within the limits of the table, so
' if the top slice is infinite, then use
' something like 99999999999999999 as threshold
' and =NA() as corresponding value

Dim StillLeft As Double
Dim AmountThisSlice As Double
Dim SumSoFar As Double
Dim Counter As Long

StillLeft = Amount

For Counter = 1 To Table.Rows.Count - 1
AmountThisSlice = Application.Min(StillLeft, Table(Counter + 1, 1) _
- Table(Counter, 1))
SumSoFar = SumSoFar + AmountThisSlice * Table(Counter, 2)
StillLeft = StillLeft - AmountThisSlice
Next
PercPerSegment = SumSoFar
End Function
' ================================================== =======

================================================
Pasting a User Defined Function (UDF)
Niek Otten, March 31, 2006

If you find a VBA function on the Internet or somebody mails you one, and you don't know how to implement it, follow these
steps:

Select all the text of the function.
CTRL+C (that is, press and hold down the CTRL key, press C, release both). This a shortcut for Copy.
Go to Excel. Press ALT+F11 (same method: press and hold the ALT key, press the F11 key and release both). You are now in the
Visual Basic Editor (VBE).
From the menu bar, choose InsertModule. There should now be a blank module sheet in front of you. Click in it and then
press CTRL+V (same method.).
This a shortcut for Paste. You should now see the text of the function in the Module.
Press ALT+F11 again to return to your Excel worksheet.
You should now be able to use the function as if it were a built-in function of Excel, like =SUM(..)
================================================


"dilettante" wrote in message
...
|I am seraching for a formula to compare the prices of hotels putting the
| dates of beginning and end of my holidays in two Excel cells. The Hotels have
| different price during the summer like:
|
| Jun-20/Jul-25 Jul-25/Aug-1 Aug-1/Aug-31 Aug-31/sep-30
| Hotel A $ 50 $ 90 $ 150 $ 40
| Jun-20/Jul-20 Jul-20/Aug-10 Aug-10/Aug-20 Aug-20/sep-15
| Hotel B $ 45 $ 99 $ 140 $ 60
|
| What are the costs from i.e. Jun-25 until Aug-10?
| Thank you for who wil help me.
| --
| dilettante

Dear Niek Otten,
thank you very much, your suggestions look helpful.
I will try them and confirm if they works.
Regards
--
dilettante


"Niek Otten" wrote:

If you use dates as if they were amounts, then this problem is described with solutions he

http://www.mcgimpsey.com/excel/variablerate.html

Subtract the price for the start date from the price on the end date.

In your case, for Hotel A, I got 3480: 30 days at 50, 7 at 90 and 9 at 150
The formulas gave me 3730 - 250, again 3480.

Instead of the formulas shown in the link above, you can also use a UDF; text at the end of the message.
If you're new to UDF's there is also an instruction on how to use them.


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

' ================================================== =======
Function PercPerSegment(Amount As Double, Table As Range) As Double
' Niek Otten, March 31, 2006

' Progressive pricing
' First argument is the quantity to be priced
' or the amount to be taxed
' Second argument is the Price or Tax% table (vertical)
' Make sure both ends of the table are correct;
' usually you start with zero and the corresponding price or %
' Any value should be found within the limits of the table, so
' if the top slice is infinite, then use
' something like 99999999999999999 as threshold
' and =NA() as corresponding value

Dim StillLeft As Double
Dim AmountThisSlice As Double
Dim SumSoFar As Double
Dim Counter As Long

StillLeft = Amount

For Counter = 1 To Table.Rows.Count - 1
AmountThisSlice = Application.Min(StillLeft, Table(Counter + 1, 1) _
- Table(Counter, 1))
SumSoFar = SumSoFar + AmountThisSlice * Table(Counter, 2)
StillLeft = StillLeft - AmountThisSlice
Next
PercPerSegment = SumSoFar
End Function
' ================================================== =======

================================================
Pasting a User Defined Function (UDF)
Niek Otten, March 31, 2006

If you find a VBA function on the Internet or somebody mails you one, and you don't know how to implement it, follow these
steps:

Select all the text of the function.
CTRL+C (that is, press and hold down the CTRL key, press C, release both). This a shortcut for Copy.
Go to Excel. Press ALT+F11 (same method: press and hold the ALT key, press the F11 key and release both). You are now in the
Visual Basic Editor (VBE).
From the menu bar, choose InsertModule. There should now be a blank module sheet in front of you. Click in it and then
press CTRL+V (same method.).
This a shortcut for Paste. You should now see the text of the function in the Module.
Press ALT+F11 again to return to your Excel worksheet.
You should now be able to use the function as if it were a built-in function of Excel, like =SUM(..)
================================================


"dilettante" wrote in message
...
|I am seraching for a formula to compare the prices of hotels putting the
| dates of beginning and end of my holidays in two Excel cells. The Hotels have
| different price during the summer like:
|
| Jun-20/Jul-25 Jul-25/Aug-1 Aug-1/Aug-31 Aug-31/sep-30
| Hotel A $ 50 $ 90 $ 150 $ 40
| Jun-20/Jul-20 Jul-20/Aug-10 Aug-10/Aug-20 Aug-20/sep-15
| Hotel B $ 45 $ 99 $ 140 $ 60
|
| What are the costs from i.e. Jun-25 until Aug-10?
| Thank you for who wil help me.
| --
| dilettante

<and confirm if they works

Please do!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"dilettante" wrote in message
...
| Dear Niek Otten,
| thank you very much, your suggestions look helpful.
| I will try them and confirm if they works.
| Regards
| --
| dilettante
|
|
| "Niek Otten" wrote:
|
| If you use dates as if they were amounts, then this problem is described with solutions he
|
| http://www.mcgimpsey.com/excel/variablerate.html
|
| Subtract the price for the start date from the price on the end date.
|
| In your case, for Hotel A, I got 3480: 30 days at 50, 7 at 90 and 9 at 150
| The formulas gave me 3730 - 250, again 3480.
|
| Instead of the formulas shown in the link above, you can also use a UDF; text at the end of the message.
| If you're new to UDF's there is also an instruction on how to use them.
|
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| ' ================================================== =======
| Function PercPerSegment(Amount As Double, Table As Range) As Double
| ' Niek Otten, March 31, 2006
|
| ' Progressive pricing
| ' First argument is the quantity to be priced
| ' or the amount to be taxed
| ' Second argument is the Price or Tax% table (vertical)
| ' Make sure both ends of the table are correct;
| ' usually you start with zero and the corresponding price or %
| ' Any value should be found within the limits of the table, so
| ' if the top slice is infinite, then use
| ' something like 99999999999999999 as threshold
| ' and =NA() as corresponding value
|
| Dim StillLeft As Double
| Dim AmountThisSlice As Double
| Dim SumSoFar As Double
| Dim Counter As Long
|
| StillLeft = Amount
|
| For Counter = 1 To Table.Rows.Count - 1
| AmountThisSlice = Application.Min(StillLeft, Table(Counter + 1, 1) _
| - Table(Counter, 1))
| SumSoFar = SumSoFar + AmountThisSlice * Table(Counter, 2)
| StillLeft = StillLeft - AmountThisSlice
| Next
| PercPerSegment = SumSoFar
| End Function
| ' ================================================== =======
|
| ================================================
| Pasting a User Defined Function (UDF)
| Niek Otten, March 31, 2006
|
| If you find a VBA function on the Internet or somebody mails you one, and you don't know how to implement it, follow
these
| steps:
|
| Select all the text of the function.
| CTRL+C (that is, press and hold down the CTRL key, press C, release both). This a shortcut for Copy.
| Go to Excel. Press ALT+F11 (same method: press and hold the ALT key, press the F11 key and release both). You are now in
the
| Visual Basic Editor (VBE).
| From the menu bar, choose InsertModule. There should now be a blank module sheet in front of you. Click in it and then
| press CTRL+V (same method.).
| This a shortcut for Paste. You should now see the text of the function in the Module.
| Press ALT+F11 again to return to your Excel worksheet.
| You should now be able to use the function as if it were a built-in function of Excel, like =SUM(..)
| ================================================
|
|
| "dilettante" wrote in message
| ...
| |I am seraching for a formula to compare the prices of hotels putting the
| | dates of beginning and end of my holidays in two Excel cells. The Hotels have
| | different price during the summer like:
| |
| | Jun-20/Jul-25 Jul-25/Aug-1 Aug-1/Aug-31 Aug-31/sep-30
| | Hotel A $ 50 $ 90 $ 150 $ 40
| | Jun-20/Jul-20 Jul-20/Aug-10 Aug-10/Aug-20 Aug-20/sep-15
| | Hotel B $ 45 $ 99 $ 140 $ 60
| |
| | What are the costs from i.e. Jun-25 until Aug-10?
| | Thank you for who wil help me.
| | --
| | dilettante
|
|
|