Formula Questions??

[Metalteck]

I have a spreadsheet that counts the start and end times. I need to be able
get the difference between the two times. I then have columns that are marked
15m, 30m and so on. If the difference between the two columns is less then
or equal to 15, to count it, if not, count it wherever it's time frame are.
Can you please help me out wit this.

[JulieD]

Hi

if you got rid of the "m" in the 15m, 30m etc then it would mean less typing
for you:

assuming you have
...........A...............B................C................D.......................E
1.....Start.............Finish.........15..............30.......................45
2.....10:00...........10:15......

the formula for C2 would be
=IF($B2-$A2<=(C$1/1440),1,0)
the formula for D2 would be
=IF(AND($B2-$A2<=(D$1/1440),$B2-$A2>=(C$1/1440)),1,0)

the formula for C2 could then be filled down column C
the formula for D2 could be filled accross row 2 and then down as many rows
as needed.

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Metalteck" <[email protected]> wrote in message
news:[email protected]...
>I have a spreadsheet that counts the start and end times. I need to be able
> get the difference between the two times. I then have columns that are
> marked
> 15m, 30m and so on. If the difference between the two columns is less
> then
> or equal to 15, to count it, if not, count it wherever it's time frame
> are.
> Can you please help me out wit this.
>

[Metalteck]

IF my columns are formated as 0:15 to 1:00 what would be the difference in
the way that I type the formula and what would be the additional formula be
for the extra columns.

"JulieD" wrote:

> Hi
>
> if you got rid of the "m" in the 15m, 30m etc then it would mean less typing
> for you:
>
> assuming you have
> ...........A...............B................C................D.......................E
> 1.....Start.............Finish.........15..............30.......................45
> 2.....10:00...........10:15......
>
> the formula for C2 would be
> =IF($B2-$A2<=(C$1/1440),1,0)
> the formula for D2 would be
> =IF(AND($B2-$A2<=(D$1/1440),$B2-$A2>=(C$1/1440)),1,0)
>
> the formula for C2 could then be filled down column C
> the formula for D2 could be filled accross row 2 and then down as many rows
> as needed.
>
> --
> Cheers
> JulieD
> check out www.hcts.net.au/tipsandtricks.htm
> ....well i'm working on it anyway
> "Metalteck" <[email protected]> wrote in message
> news:[email protected]...
> >I have a spreadsheet that counts the start and end times. I need to be able
> > get the difference between the two times. I then have columns that are
> > marked
> > 15m, 30m and so on. If the difference between the two columns is less
> > then
> > or equal to 15, to count it, if not, count it wherever it's time frame
> > are.
> > Can you please help me out wit this.
> >
>
>
>

[JulieD]

Hi

C2's formula would be
=IF($B2-$A2<=C$1,1,0)

D2's formula would be
=IF(AND($B2-$A2<=D$1,$B2-$A2>=C$1),1,0)

D2's formula can be copied to all your other columns (move your cursor over
the bottom right hand corner of D2 until you see a +, hold down your mouse
and drag across the columns). the C2 formula can be dragged down column C
and the other column's formulas can be dragged down for all your rows.

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Metalteck" <[email protected]> wrote in message
news:[email protected]...
> IF my columns are formated as 0:15 to 1:00 what would be the difference in
> the way that I type the formula and what would be the additional formula
> be
> for the extra columns.
>
> "JulieD" wrote:
>
>> Hi
>>
>> if you got rid of the "m" in the 15m, 30m etc then it would mean less
>> typing
>> for you:
>>
>> assuming you have
>> ...........A...............B................C................D.......................E
>> 1.....Start.............Finish.........15..............30.......................45
>> 2.....10:00...........10:15......
>>
>> the formula for C2 would be
>> =IF($B2-$A2<=(C$1/1440),1,0)
>> the formula for D2 would be
>> =IF(AND($B2-$A2<=(D$1/1440),$B2-$A2>=(C$1/1440)),1,0)
>>
>> the formula for C2 could then be filled down column C
>> the formula for D2 could be filled accross row 2 and then down as many
>> rows
>> as needed.
>>
>> --
>> Cheers
>> JulieD
>> check out www.hcts.net.au/tipsandtricks.htm
>> ....well i'm working on it anyway
>> "Metalteck" <[email protected]> wrote in message
>> news:[email protected]...
>> >I have a spreadsheet that counts the start and end times. I need to be
>> >able
>> > get the difference between the two times. I then have columns that are
>> > marked
>> > 15m, 30m and so on. If the difference between the two columns is less
>> > then
>> > or equal to 15, to count it, if not, count it wherever it's time frame
>> > are.
>> > Can you please help me out wit this.
>> >
>>
>>
>>

[Metalteck]

When i implement these formulas, how do i get the if the time is greater than
15 not to count in the 15, but just the 30, because right now it is only
counting the 15.

[Metalteck]

Also, I would only like to get times that are less then or = to 15, to only
be counted in the 15 column and times greater than 15 and less than or = 30
to be placed in the 30 column, because right now all it is doing is counting
it only in the 15 column, even thought it is greater.

[JulieD]

interesting, as it's not doing this for me ... could you copy & paste the
first formula you're using under the 15 heading and the first formula under
the 30 heading in a reply post


--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Metalteck" <[email protected]> wrote in message
news:[email protected]...
> When i implement these formulas, how do i get the if the time is greater
> than
> 15 not to count in the 15, but just the 30, because right now it is only
> counting the 15.
>

[Metalteck]

On the spreadsheet, I got an individual field that tells me calculates the
time difference, and works fine as shown.
Q3 T3 W3 15 30 45 60
2:30 3:00 0:30 1 0 0 0

But to actually make it know where to place the count is not working. It
only places it in the 15 column.

You've helped me in the time difference, how do i get the count to be
recognized in the proper column. I don't think I need it to calculate the
difference in the time frame columns, just count. But I'm asking you for
help, your the expert.

=IF($W3-$R3<=(AY$1/1440),1,0) formula in the 15 column.

=IF(AND($W3-$R3<=(AZ$1/1440),$W3-$R3>=(AY$1/1440)),1,0) is the formula for
the rest of the column

[JulieD]

Hi

if you have already subtracted the columns you can then just test ... but i
can't work out what column the 0:30 is in, nor the 15, 30, 45, 60

please post back with cell references.


--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Metalteck" <[email protected]> wrote in message
news:[email protected]...
> On the spreadsheet, I got an individual field that tells me calculates the
> time difference, and works fine as shown.
> Q3 T3 W3 15 30 45 60
> 2:30 3:00 0:30 1 0 0 0
>
> But to actually make it know where to place the count is not working. It
> only places it in the 15 column.
>
> You've helped me in the time difference, how do i get the count to be
> recognized in the proper column. I don't think I need it to calculate the
> difference in the time frame columns, just count. But I'm asking you for
> help, your the expert.
>
> =IF($W3-$R3<=(AY$1/1440),1,0) formula in the 15 column.
>
> =IF(AND($W3-$R3<=(AZ$1/1440),$W3-$R3>=(AY$1/1440)),1,0) is the formula for
> the rest of the column

[blue12]

What is the function used for MPG. Will I need to add a macro and create a UDF if my version of excel does not have it? I am having problems with that. Here is the problem:

MPG=(premium cost-reg. cost)/4.5456 Litres

It seems simple enough, but I do not already have an UDF defined.