how do I get the address of a cell to calculate with it

[dilettante]

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

[Toppers]

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

[Toppers]

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

[Niek Otten]

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

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 Insert>Module. 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" <pg.baldassini@libero-CancellaTesto&Segni-.it> wrote in message
news:[email protected]...
|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

[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 here:
>
> 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 Insert>Module. 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" <pg.baldassini@libero-CancellaTesto&Segni-.it> wrote in message
> news:[email protected]...
> |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
>
>
>

[Niek Otten]

<and confirm if they works>

Please do!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"dilettante" <pg.baldassini@libero-CancellaTesto&Segni-.it> wrote in message
news:[email protected]...
| 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 here:
| >
| > 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 Insert>Module. 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" <pg.baldassini@libero-CancellaTesto&Segni-.it> wrote in message
| > news:[email protected]...
| > |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
| >
| >
| >