Excel 2003: Updating Changing Rates ..

[Anthony904]

Hi,

I have a spreadsheet that has three columns.

Date, Data and Rate

I have some data pulled in externally and gets displayed in the spreadsheet. (Date and Data field)

The data then gets calculated according to the Rate that is in the spreadsheet. These rates can change at any time by the user.

So what I would like to see is.. when the user opens the worksheet a form would be present to ask the beginning rate...That rate gets applied to the entire column..

When a rate needs to be changed .. for a particular day. then any rates after the change will update with the new rate.

ie..

http://c.1asphost.com/EthyASC/Rates.xls

Thanks!

 

[SkipVought]

Hi,

"... the beginning rate...That rate gets applied to the entire column"

"When a rate needs to be changed .. for a particular day. then any rates after the change will update with the new rate.

So, using your posted example, when that sheet is opened the NEXT time, if the user enters 4.1 as the beginning rate then
[tt]
Date Rate
1/1/2005 4.1
1/1/2005 4.1
1/1/2005 4.1
1/1/2005 4.1
1/1/2005 4.1
1/1/2005 4.1
1/1/2005 4.1
1/1/2005 4.1
1/1/2005 4.1
1/1/2005 4.1
1/1/2005 4.1
1/2/2005 4.1
1/2/2005 4.1
1/2/2005 4.1
1/2/2005 4.1
1/2/2005 4.1
1/2/2005 4.1
[/tt]
???

What about the DATA column? "I have some data pulled in externally and gets displayed in the spreadsheet. (Date and Data field)"

This all does not make sense.

What is the purpose?

When a RATE or some other constant is used, I often use a Named Range for the value in a formula. Or it might be a lookup based on a date range.

Need some more info, pal.

Skip,
[sub]
[red][/red]
[/sub]

 

[Anthony904]

So, using your posted example, when that sheet is opened the NEXT time, if the user enters 4.1 as the beginning rate then"

yes that is correct.

"What about the DATA column?"

The user opens the spreadsheet..

Enters in some dates

1-1-05 thru 1-5-05

This pulls the data into column A and B

column A
1-1-05
1-2-05
1-3-05
1-4-05
1-5-05

Column B
12
12
12
10
5

column C (constant field)
4.1 (user enters and this runs straight down the column)
4.1
4.1
3.4 (If there is a change in rate the user would enter in the new rate.. say 3.4, 3.4 would be replaced here and 3.4 would run down the column now)
3.4

I just realized that each time the user opens the form they would have to enter in the rate... ( not sure if they want this..) I'll have to check...

Instead of entering the beginning rate.. could the row be a constant number and only change when user changes the rate..

ie..

Column C
4.1
4.1
4.1
4.1
4.1
4.1
4.1
3.4 <--User changes
3.4
3.4
3.4
3.4
3.4
3.4

Thanks for you help.

 

[SkipVought]

you never answered the "what's the purpose?" question.

How does the data get into columns A & B after the user enters a FROM - TO date range?

Skip,
[sub]
[red][/red]
[/sub]

 

[Anthony904]

Well, We have a machine that we measure the time it runs each day..

This reading gets recorded into a database (3rd party software "aspen") with the date and the hour the machine ran.

On my spreadsheet the user enters in the date and it pulls the date and hour of that particular machine (through the 3rd party add-in)

Now that I have the hr and date.. I want a column with a rate.. the rate runs down the column (since the date entered by the user is random and the fluctuation of the rate, I can't assign a specific rate for each date)

I want for the user to enter in the rate of that particular day.. so once the rate is changed in the column, the new rate would take the place of the old rate. The old rate must be kept since on that date that was the rate.

I'm sure there is another way of doing this.. but that's what I've come up with.. but I am open to suggestions..

Thanks for the response!

 

[SkipVought]

Your description bears little resemblence to the questions you have been asking.

I assume that there is only ONE machine. True?

Where are the times in your previous examples and questions?

So the user enters a date. Is it today's data, yesterday's date or some other date based on logic or is it any date at all, for instance 5/29/2004?

And for that date, the date & a time duration or is it a time stamp that is returned?

Does this get appended to existing historical data?

Since the RATE is only valid for that day, why does it get propogated to your list?

Does not each date have its own RATE?

Skip,
[sub]
[red][/red]
[/sub]

 

[Anthony904]

Skip,

I got some more information on this it seems that I was misinformed.

Hopefully this will be clearer...

How rates are applied:
Let's say that previous rate was: 1.2
An operator calls in and says that the rate has changed today 9/20/06 to 2.5 for machine 1.

So all dates previous to 9/20/06 was 1.2
and all dates including 9/20/06 will be 2.5

The 2.5 rate will stay until the operator calls in again with the new rate.

Data extraction:

The data for each machine is recorded @ 3pm each day..
So today at 3 we would have a recording for all machines 9/20/06 with the hours ran.

Say a supervisor calls and wants the user to retrieve all data for sept. 06 for machine 1.. (date varies) The user opens the excel file.. enters in the date 9-1-06 thru 9-30-06 for machine 1. This will pull date and data for machine1 and display on the excel sheet.

There are 12 total machines.

I was thinking.. could we create another worksheet and allow the user to enter in the date and new rate for that particular machine and that would be compared to the data generated.

Thanks for your help!

 

[Anthony904]

I think I may have a solution...

VLOOKUP will do the trick..

Thanks!