Column locations

[Pugs107]

I am creating a spreadsheet that will integrate data over a period of time (by hour). In order to do this, I need columns set at the hour points, however all of the other point times will be variable (ie.one point event may fall before or after the hour point). Is there a way to set one or more columns while the data in other columns move relative to those columns? I'm not sure how well I verbalized what I need. any help would be greatly appreciated

 

[erik.van.geit]

Hello, Pugs107,
Welcome to the Board !!!!!

I'm not sure how well I verbalized what I need

try again please
use a simple example
tell us what you start with
something like this perhaps ?
row 1 has 00:00 01:00 02:00
a "point event" is falling at 00:24
what should happen with your sheet ?
kind regards,
Erik

 

[Pugs107]

Hello Erik,
OK, lets try this...

Row one has the following times
00:30 00:45 01:00 etc.

The second point is dependant on the first (ie. time one plus :15)

Calculations will be done using data below the 01:00. If the first point was, say 00:46, this would push the second point to 01:01. This is no problem except I still need the 01:00 point for the calculation.

Is there a way to keep the 01:00 column while still allowing the other point to pass it sequentially?
Thanks for your help,
Mike

 

[erik.van.geit]

Mike,

it's still a mistery for me what you mean ?
probably something simple
care to elaborate an example
"for the calculation" : in which cell, which formula ?
perhaps you will need ROUND ?

A1 has: 00:30 (formatted as hh:mm)
B1 has: =A1+1/96 displaying as 00:45
copy this formula to the right and you will get
01:00 01:15 01:30 01:45 02:00
changing A1 will change all subsequent cells accordingly

best regards,
Erik

 

[Pugs107]

ADVERTISEMENT

I am probably making this too difficult for myself

I currently am integrating data that occurs within the hour.

A1 = 00:30
B1 = 00:45 (A1+ 1/96)
C1 = 01:00 (I need the 01:00 point for the integration calculation)

If the value of A1 changes

A1 = 00:46
B1 = 01:01
C1 = 01:00

The 01:00 would actually fall between A1 and B1

An example of the type of calculaton is

((B1-A1)*(B2-A2))+((C1-B1)*(C2-B2))

 

[erik.van.geit]

sorry, still don't have the slightest idea of what you are trying to do
just forget everything you posted, don't come with a solution at all - unless you find a way to explain it completely different

just tell what the result should be
we agree on this:
1. you got some "headers" with
00:30 00:45 etcetera
(don't care yet if these are formulas or values)
2. you have a formula in the sheet
OK, tell us:
in which cell do you have the formula ?
what's the formula and what's the resulting value ?
THEN tell what would happen next

hope we are getting further this way...

kind regards,
Erik

 

[Pugs107]

ADVERTISEMENT

I made a simple spreadsheet that could possibly help. Or it might just prove that I am out to lunch . Could I e-mail it to you?

Thanks,
-Mike

 

[erik.van.geit]

Mike,
first try to display it here
use Colo's HTML Maker utility for displaying your Excel Worksheet on the board

On the "Excel Questions" main page there is a link to HTML Maker FAQ. You'll find out how to post correctly.
On the main page of the Forum there is a link to a Test here forum and a link to About this Board - to ask questions about HTLMMaker ...
you may email it
leaving now for at least 9 hours

kind regards,
Erik

 

[Pugs107]

Example sheet.xls
	A	B	C	D	E	F
1		Event1	Event2	Event3	1 Hour
2	Time	0:00	0:30	0:45	1:00
3	Data	10	10	20	20
4
5		0.00	0.50	0.75	1.00	(Time to Decimal Conversion)
6
7			Total:	13.75
Sheet1

The Total formula integrates the data occurring within the hour. The problem I am having is when the times change such that Event Three occurs after the hour point. I would need the hour point to be between Event 2 and Event 3.

Thanks,
-Mike

 

[erik.van.geit]

Mike,
really sitting before this screen for long time
what could you mean
for the life of me, why can a question look so complicated while it should be simple

let's take it another way:
you are writing down events in a sheet, right ?
I can see you are trying to calculate kinda "surfaces" (integrates)

when the times change such that Event Three occurs after the hour point

you have problems when - lost here - there aren't "enough" events within an hour, or ???

I would need the hour point to be between Event 2 and Event 3.

all this looks to me as if you absolutely want to have 3 (or 4?) events within an hour, but there aren't so much events, or ???

this is an Excel-forum
we try to find solutions to problems
when the problem still is to figure out what the problem is, then we've really got a problem !
I would like to encourage you to rethink your question, staying practical (no theorethical)
what purpose will your sheet have ?
what practical use do you want to get from it ?

still willing to help, but how ?
Erik