I have a table with 5 columns and about 30 rows. A containes litres, B
kilometres, C average consumption in 100 km, D additive litres and E should
have additives average consumption in 100 km. Problem is that additive is
refueled in various periods and in between there might be 3-9 empty cells. I
have also considered to add 1 column first on left which has dates, if this
has any impact to formula-case.

How do I make formula that finds next upper value and calculates total km
between refilling additive? I have tried IF(ISBLANK(OFFSET... and
IF(ISVALUE(FIND... IF(ISERROR(... but so far nothing works.

Can you post a sample that illustrates what you want?

--
Biff
Microsoft Excel MVP


"NeedToKnow" wrote in message
...
I have a table with 5 columns and about 30 rows. A containes litres, B
kilometres, C average consumption in 100 km, D additive litres and E
should
have additives average consumption in 100 km. Problem is that additive is
refueled in various periods and in between there might be 3-9 empty cells.
I
have also considered to add 1 column first on left which has dates, if
this
has any impact to formula-case.

How do I make formula that finds next upper value and calculates total km
between refilling additive? I have tried IF(ISBLANK(OFFSET... and
IF(ISVALUE(FIND... IF(ISERROR(... but so far nothing works.

km total litre total kk av.c. ADD litre total ADD kk av.c.
6 114 3 969 64,9 187,0

km litre average cons. ADD L ADD av.c.
800 220 58,8
1100 200 66,7
1500 260 65,0 50,0 ???
1920 300 71,4
2340 270 64,3
2760 250 59,5
3180 290 69,0 48,0 ???
3600 270 64,3

Here are ??? where I need to have average consumption counted based on 48 L
/ ( (3180 km - 1500 km)/100) and the problem is how to seach last addition
filling and count the driven km in between.

Hopefully this clears little. First 2 rows are total for this month and all
but km and L cells are locked from daily user.

I know this can't be so difficult but somehow I'm stuck to think always the
same. :-/
"T. Valko" kirjoitti:

Can you post a sample that illustrates what you want?

--
Biff
Microsoft Excel MVP


"NeedToKnow" wrote in message
...
I have a table with 5 columns and about 30 rows. A containes litres, B
kilometres, C average consumption in 100 km, D additive litres and E
should
have additives average consumption in 100 km. Problem is that additive is
refueled in various periods and in between there might be 3-9 empty cells.
I
have also considered to add 1 column first on left which has dates, if
this
has any impact to formula-case.

How do I make formula that finds next upper value and calculates total km
between refilling additive? I have tried IF(ISBLANK(OFFSET... and
IF(ISVALUE(FIND... IF(ISERROR(... but so far nothing works.

Assume this data is in the range A2:D9 -

800 220 58,8 0
1100 200 66,7
1500 260 65,0 50,0
1920 300 71,4
2340 270 64,3
2760 250 59,5
3180 290 69,0 48,0
3600 270 64,3

Enter a 0 in D2 (like I have above)

Enter this formula E3 and copy down as needed:

=IF(D3="","",(A3-LOOKUP(1E100,D$2:D2,A$2:A2))/D3)

Your first average is 50/((1500-800)/100)
The 2nd average is 48/((3180-1500)/100)

--
Biff
Microsoft Excel MVP


"NeedToKnow" wrote in message
...
km total litre total kk av.c. ADD litre total ADD kk av.c.
6 114 3 969 64,9 187,0

km litre average cons. ADD L ADD av.c.
800 220 58,8
1100 200 66,7
1500 260 65,0 50,0 ???
1920 300 71,4
2340 270 64,3
2760 250 59,5
3180 290 69,0 48,0 ???
3600 270 64,3

Here are ??? where I need to have average consumption counted based on 48
L
/ ( (3180 km - 1500 km)/100) and the problem is how to seach last addition
filling and count the driven km in between.

Hopefully this clears little. First 2 rows are total for this month and
all
but km and L cells are locked from daily user.

I know this can't be so difficult but somehow I'm stuck to think always
the
same. :-/
"T. Valko" kirjoitti:

Can you post a sample that illustrates what you want?

--
Biff
Microsoft Excel MVP


"NeedToKnow" wrote in message
...
I have a table with 5 columns and about 30 rows. A containes litres, B
kilometres, C average consumption in 100 km, D additive litres and E
should
have additives average consumption in 100 km. Problem is that additive
is
refueled in various periods and in between there might be 3-9 empty
cells.
I
have also considered to add 1 column first on left which has dates, if
this
has any impact to formula-case.

How do I make formula that finds next upper value and calculates total
km
between refilling additive? I have tried IF(ISBLANK(OFFSET... and
IF(ISVALUE(FIND... IF(ISERROR(... but so far nothing works.

Ooops! I posted the wrong formula in my previous reply.

Use this formula:

=IF(D3="","",D3/((A3-LOOKUP(1E100,D$2:D2,A$2:A2))/100))


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Assume this data is in the range A2:D9 -

800 220 58,8 0
1100 200 66,7
1500 260 65,0 50,0
1920 300 71,4
2340 270 64,3
2760 250 59,5
3180 290 69,0 48,0
3600 270 64,3

Enter a 0 in D2 (like I have above)

Enter this formula E3 and copy down as needed:

=IF(D3="","",(A3-LOOKUP(1E100,D$2:D2,A$2:A2))/D3)

Your first average is 50/((1500-800)/100)
The 2nd average is 48/((3180-1500)/100)

--
Biff
Microsoft Excel MVP


"NeedToKnow" wrote in message
...
km total litre total kk av.c. ADD litre total ADD kk av.c.
6 114 3 969 64,9 187,0

km litre average cons. ADD L ADD av.c.
800 220 58,8
1100 200 66,7
1500 260 65,0 50,0 ???
1920 300 71,4
2340 270 64,3
2760 250 59,5
3180 290 69,0 48,0 ???
3600 270 64,3

Here are ??? where I need to have average consumption counted based on 48
L
/ ( (3180 km - 1500 km)/100) and the problem is how to seach last
addition
filling and count the driven km in between.

Hopefully this clears little. First 2 rows are total for this month and
all
but km and L cells are locked from daily user.

I know this can't be so difficult but somehow I'm stuck to think always
the
same. :-/
"T. Valko" kirjoitti:

Can you post a sample that illustrates what you want?

--
Biff
Microsoft Excel MVP


"NeedToKnow" wrote in message
...
I have a table with 5 columns and about 30 rows. A containes litres, B
kilometres, C average consumption in 100 km, D additive litres and E
should
have additives average consumption in 100 km. Problem is that additive
is
refueled in various periods and in between there might be 3-9 empty
cells.
I
have also considered to add 1 column first on left which has dates, if
this
has any impact to formula-case.

How do I make formula that finds next upper value and calculates total
km
between refilling additive? I have tried IF(ISBLANK(OFFSET... and
IF(ISVALUE(FIND... IF(ISERROR(... but so far nothing works.

This gives ne #N/A but I think it's closer solution than any of mine. I'll
keep trying

"T. Valko" kirjoitti:

Assume this data is in the range A2:D9 -

800 220 58,8 0
1100 200 66,7
1500 260 65,0 50,0
1920 300 71,4
2340 270 64,3
2760 250 59,5
3180 290 69,0 48,0
3600 270 64,3

Enter a 0 in D2 (like I have above)

Enter this formula E3 and copy down as needed:

=IF(D3="","",(A3-LOOKUP(1E100,D$2:D2,A$2:A2))/D3)

Your first average is 50/((1500-800)/100)
The 2nd average is 48/((3180-1500)/100)

--
Biff
Microsoft Excel MVP


"NeedToKnow" wrote in message
...
km total litre total kk av.c. ADD litre total ADD kk av.c.
6 114 3 969 64,9 187,0

km litre average cons. ADD L ADD av.c.
800 220 58,8
1100 200 66,7
1500 260 65,0 50,0 ???
1920 300 71,4
2340 270 64,3
2760 250 59,5
3180 290 69,0 48,0 ???
3600 270 64,3

Here are ??? where I need to have average consumption counted based on 48
L
/ ( (3180 km - 1500 km)/100) and the problem is how to seach last addition
filling and count the driven km in between.

Hopefully this clears little. First 2 rows are total for this month and
all
but km and L cells are locked from daily user.

I know this can't be so difficult but somehow I'm stuck to think always
the
same. :-/
"T. Valko" kirjoitti:

Can you post a sample that illustrates what you want?

--
Biff
Microsoft Excel MVP


"NeedToKnow" wrote in message
...
I have a table with 5 columns and about 30 rows. A containes litres, B
kilometres, C average consumption in 100 km, D additive litres and E
should
have additives average consumption in 100 km. Problem is that additive
is
refueled in various periods and in between there might be 3-9 empty
cells.
I
have also considered to add 1 column first on left which has dates, if
this
has any impact to formula-case.

How do I make formula that finds next upper value and calculates total
km
between refilling additive? I have tried IF(ISBLANK(OFFSET... and
IF(ISVALUE(FIND... IF(ISERROR(... but so far nothing works.

Also this gives error #N/A, but thank you for trying to solve my problem. I
just keep wondering until it's done, bacause I know now I'm getting closer :)

"T. Valko" kirjoitti:

Ooops! I posted the wrong formula in my previous reply.

Use this formula:

=IF(D3="","",D3/((A3-LOOKUP(1E100,D$2:D2,A$2:A2))/100))


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Assume this data is in the range A2:D9 -

800 220 58,8 0
1100 200 66,7
1500 260 65,0 50,0
1920 300 71,4
2340 270 64,3
2760 250 59,5
3180 290 69,0 48,0
3600 270 64,3

Enter a 0 in D2 (like I have above)

Enter this formula E3 and copy down as needed:

=IF(D3="","",(A3-LOOKUP(1E100,D$2:D2,A$2:A2))/D3)

Your first average is 50/((1500-800)/100)
The 2nd average is 48/((3180-1500)/100)

--
Biff
Microsoft Excel MVP


"NeedToKnow" wrote in message
...
km total litre total kk av.c. ADD litre total ADD kk av.c.
6 114 3 969 64,9 187,0

km litre average cons. ADD L ADD av.c.
800 220 58,8
1100 200 66,7
1500 260 65,0 50,0 ???
1920 300 71,4
2340 270 64,3
2760 250 59,5
3180 290 69,0 48,0 ???
3600 270 64,3

Here are ??? where I need to have average consumption counted based on 48
L
/ ( (3180 km - 1500 km)/100) and the problem is how to seach last
addition
filling and count the driven km in between.

Hopefully this clears little. First 2 rows are total for this month and
all
but km and L cells are locked from daily user.

I know this can't be so difficult but somehow I'm stuck to think always
the
same. :-/
"T. Valko" kirjoitti:

Can you post a sample that illustrates what you want?

--
Biff
Microsoft Excel MVP


"NeedToKnow" wrote in message
...
I have a table with 5 columns and about 30 rows. A containes litres, B
kilometres, C average consumption in 100 km, D additive litres and E
should
have additives average consumption in 100 km. Problem is that additive
is
refueled in various periods and in between there might be 3-9 empty
cells.
I
have also considered to add 1 column first on left which has dates, if
this
has any impact to formula-case.

How do I make formula that finds next upper value and calculates total
km
between refilling additive? I have tried IF(ISBLANK(OFFSET... and
IF(ISVALUE(FIND... IF(ISERROR(... but so far nothing works.

See this screencap:

http://img252.imageshack.us/img252/7415/monture5.jpg

If the formula is returning the incorrect results what results do you
expect?

The formula follows your description of:

based on 48 L / ( (3180 km - 1500 km)/100)

--
Biff
Microsoft Excel MVP


"NeedToKnow" wrote in message
...
Also this gives error #N/A, but thank you for trying to solve my problem.
I
just keep wondering until it's done, bacause I know now I'm getting closer
:)

"T. Valko" kirjoitti:

Ooops! I posted the wrong formula in my previous reply.

Use this formula:

=IF(D3="","",D3/((A3-LOOKUP(1E100,D$2:D2,A$2:A2))/100))


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Assume this data is in the range A2:D9 -

800 220 58,8 0
1100 200 66,7
1500 260 65,0 50,0
1920 300 71,4
2340 270 64,3
2760 250 59,5
3180 290 69,0 48,0
3600 270 64,3

Enter a 0 in D2 (like I have above)

Enter this formula E3 and copy down as needed:

=IF(D3="","",(A3-LOOKUP(1E100,D$2:D2,A$2:A2))/D3)

Your first average is 50/((1500-800)/100)
The 2nd average is 48/((3180-1500)/100)

--
Biff
Microsoft Excel MVP


"NeedToKnow" wrote in message
...
km total litre total kk av.c. ADD litre total ADD kk av.c.
6 114 3 969 64,9 187,0

km litre average cons. ADD L ADD av.c.
800 220 58,8
1100 200 66,7
1500 260 65,0 50,0 ???
1920 300 71,4
2340 270 64,3
2760 250 59,5
3180 290 69,0 48,0 ???
3600 270 64,3

Here are ??? where I need to have average consumption counted based on
48
L
/ ( (3180 km - 1500 km)/100) and the problem is how to seach last
addition
filling and count the driven km in between.

Hopefully this clears little. First 2 rows are total for this month
and
all
but km and L cells are locked from daily user.

I know this can't be so difficult but somehow I'm stuck to think
always
the
same. :-/
"T. Valko" kirjoitti:

Can you post a sample that illustrates what you want?

--
Biff
Microsoft Excel MVP


"NeedToKnow" wrote in message
...
I have a table with 5 columns and about 30 rows. A containes litres,
B
kilometres, C average consumption in 100 km, D additive litres and
E
should
have additives average consumption in 100 km. Problem is that
additive
is
refueled in various periods and in between there might be 3-9 empty
cells.
I
have also considered to add 1 column first on left which has dates,
if
this
has any impact to formula-case.

How do I make formula that finds next upper value and calculates
total
km
between refilling additive? I have tried IF(ISBLANK(OFFSET... and
IF(ISVALUE(FIND... IF(ISERROR(... but so far nothing works.

My apologies! Now if ever I feel so stupid... :(

I have been writing =IF(D3="","",D3/((A3-LOOKUP(1E+100,D$2:D2,A$2:A2))/100))
adding + between 1E and 100 and now when I cheked it I saw it in 0,5 seconds.

Hugs and have a nice weekend!


"T. Valko" kirjoitti:

See this screencap:

http://img252.imageshack.us/img252/7415/monture5.jpg

If the formula is returning the incorrect results what results do you
expect?

The formula follows your description of:

based on 48 L / ( (3180 km - 1500 km)/100)

--
Biff
Microsoft Excel MVP


"NeedToKnow" wrote in message
...
Also this gives error #N/A, but thank you for trying to solve my problem.
I
just keep wondering until it's done, bacause I know now I'm getting closer
:)

"T. Valko" kirjoitti:

Ooops! I posted the wrong formula in my previous reply.

Use this formula:

=IF(D3="","",D3/((A3-LOOKUP(1E100,D$2:D2,A$2:A2))/100))


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Assume this data is in the range A2:D9 -

800 220 58,8 0
1100 200 66,7
1500 260 65,0 50,0
1920 300 71,4
2340 270 64,3
2760 250 59,5
3180 290 69,0 48,0
3600 270 64,3

Enter a 0 in D2 (like I have above)

Enter this formula E3 and copy down as needed:

=IF(D3="","",(A3-LOOKUP(1E100,D$2:D2,A$2:A2))/D3)

Your first average is 50/((1500-800)/100)
The 2nd average is 48/((3180-1500)/100)

--
Biff
Microsoft Excel MVP


"NeedToKnow" wrote in message
...
km total litre total kk av.c. ADD litre total ADD kk av.c.
6 114 3 969 64,9 187,0

km litre average cons. ADD L ADD av.c.
800 220 58,8
1100 200 66,7
1500 260 65,0 50,0 ???
1920 300 71,4
2340 270 64,3
2760 250 59,5
3180 290 69,0 48,0 ???
3600 270 64,3

Here are ??? where I need to have average consumption counted based on
48
L
/ ( (3180 km - 1500 km)/100) and the problem is how to seach last
addition
filling and count the driven km in between.

Hopefully this clears little. First 2 rows are total for this month
and
all
but km and L cells are locked from daily user.

I know this can't be so difficult but somehow I'm stuck to think
always
the
same. :-/
"T. Valko" kirjoitti:

Can you post a sample that illustrates what you want?

--
Biff
Microsoft Excel MVP


"NeedToKnow" wrote in message
...
I have a table with 5 columns and about 30 rows. A containes litres,
B
kilometres, C average consumption in 100 km, D additive litres and
E
should
have additives average consumption in 100 km. Problem is that
additive
is
refueled in various periods and in between there might be 3-9 empty
cells.
I
have also considered to add 1 column first on left which has dates,
if
this
has any impact to formula-case.

How do I make formula that finds next upper value and calculates
total
km
between refilling additive? I have tried IF(ISBLANK(OFFSET... and
IF(ISVALUE(FIND... IF(ISERROR(... but so far nothing works.

And once more correction, of course the sign is + and I have been adding -.
Maybe I just go to bed and wake up on Monday...

"T. Valko" kirjoitti:

See this screencap:

http://img252.imageshack.us/img252/7415/monture5.jpg

If the formula is returning the incorrect results what results do you
expect?

The formula follows your description of:

based on 48 L / ( (3180 km - 1500 km)/100)

--
Biff
Microsoft Excel MVP


"NeedToKnow" wrote in message
...
Also this gives error #N/A, but thank you for trying to solve my problem.
I
just keep wondering until it's done, bacause I know now I'm getting closer
:)

"T. Valko" kirjoitti:

Ooops! I posted the wrong formula in my previous reply.

Use this formula:

=IF(D3="","",D3/((A3-LOOKUP(1E100,D$2:D2,A$2:A2))/100))


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Assume this data is in the range A2:D9 -

800 220 58,8 0
1100 200 66,7
1500 260 65,0 50,0
1920 300 71,4
2340 270 64,3
2760 250 59,5
3180 290 69,0 48,0
3600 270 64,3

Enter a 0 in D2 (like I have above)

Enter this formula E3 and copy down as needed:

=IF(D3="","",(A3-LOOKUP(1E100,D$2:D2,A$2:A2))/D3)

Your first average is 50/((1500-800)/100)
The 2nd average is 48/((3180-1500)/100)

--
Biff
Microsoft Excel MVP


"NeedToKnow" wrote in message
...
km total litre total kk av.c. ADD litre total ADD kk av.c.
6 114 3 969 64,9 187,0

km litre average cons. ADD L ADD av.c.
800 220 58,8
1100 200 66,7
1500 260 65,0 50,0 ???
1920 300 71,4
2340 270 64,3
2760 250 59,5
3180 290 69,0 48,0 ???
3600 270 64,3

Here are ??? where I need to have average consumption counted based on
48
L
/ ( (3180 km - 1500 km)/100) and the problem is how to seach last
addition
filling and count the driven km in between.

Hopefully this clears little. First 2 rows are total for this month
and
all
but km and L cells are locked from daily user.

I know this can't be so difficult but somehow I'm stuck to think
always
the
same. :-/
"T. Valko" kirjoitti:

Can you post a sample that illustrates what you want?

--
Biff
Microsoft Excel MVP


"NeedToKnow" wrote in message
...
I have a table with 5 columns and about 30 rows. A containes litres,
B
kilometres, C average consumption in 100 km, D additive litres and
E
should
have additives average consumption in 100 km. Problem is that
additive
is
refueled in various periods and in between there might be 3-9 empty
cells.
I
have also considered to add 1 column first on left which has dates,
if
this
has any impact to formula-case.

How do I make formula that finds next upper value and calculates
total
km
between refilling additive? I have tried IF(ISBLANK(OFFSET... and
IF(ISVALUE(FIND... IF(ISERROR(... but so far nothing works.

Ok, glad to hear you got it straightened out!

When I write formulas using these expressions: 1E100, I write it just like
that and when you hit ENTER Excel will automatically convert it to 1E+100.

--
Biff
Microsoft Excel MVP


"NeedToKnow" wrote in message
...
My apologies! Now if ever I feel so stupid... :(

I have been writing
=IF(D3="","",D3/((A3-LOOKUP(1E+100,D$2:D2,A$2:A2))/100))
adding + between 1E and 100 and now when I cheked it I saw it in 0,5
seconds.

Hugs and have a nice weekend!


"T. Valko" kirjoitti:

See this screencap:

http://img252.imageshack.us/img252/7415/monture5.jpg

If the formula is returning the incorrect results what results do you
expect?

The formula follows your description of:

based on 48 L / ( (3180 km - 1500 km)/100)

--
Biff
Microsoft Excel MVP


"NeedToKnow" wrote in message
...
Also this gives error #N/A, but thank you for trying to solve my
problem.
I
just keep wondering until it's done, bacause I know now I'm getting
closer
:)

"T. Valko" kirjoitti:

Ooops! I posted the wrong formula in my previous reply.

Use this formula:

=IF(D3="","",D3/((A3-LOOKUP(1E100,D$2:D2,A$2:A2))/100))


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Assume this data is in the range A2:D9 -

800 220 58,8 0
1100 200 66,7
1500 260 65,0 50,0
1920 300 71,4
2340 270 64,3
2760 250 59,5
3180 290 69,0 48,0
3600 270 64,3

Enter a 0 in D2 (like I have above)

Enter this formula E3 and copy down as needed:

=IF(D3="","",(A3-LOOKUP(1E100,D$2:D2,A$2:A2))/D3)

Your first average is 50/((1500-800)/100)
The 2nd average is 48/((3180-1500)/100)

--
Biff
Microsoft Excel MVP


"NeedToKnow" wrote in message
...
km total litre total kk av.c. ADD litre total ADD kk av.c.
6 114 3 969 64,9 187,0

km litre average cons. ADD L ADD av.c.
800 220 58,8
1100 200 66,7
1500 260 65,0 50,0 ???
1920 300 71,4
2340 270 64,3
2760 250 59,5
3180 290 69,0 48,0 ???
3600 270 64,3

Here are ??? where I need to have average consumption counted based
on
48
L
/ ( (3180 km - 1500 km)/100) and the problem is how to seach last
addition
filling and count the driven km in between.

Hopefully this clears little. First 2 rows are total for this month
and
all
but km and L cells are locked from daily user.

I know this can't be so difficult but somehow I'm stuck to think
always
the
same. :-/
"T. Valko" kirjoitti:

Can you post a sample that illustrates what you want?

--
Biff
Microsoft Excel MVP


"NeedToKnow" wrote in
message
...
I have a table with 5 columns and about 30 rows. A containes
litres,
B
kilometres, C average consumption in 100 km, D additive litres
and
E
should
have additives average consumption in 100 km. Problem is that
additive
is
refueled in various periods and in between there might be 3-9
empty
cells.
I
have also considered to add 1 column first on left which has
dates,
if
this
has any impact to formula-case.

How do I make formula that finds next upper value and calculates
total
km
between refilling additive? I have tried IF(ISBLANK(OFFSET...
and
IF(ISVALUE(FIND... IF(ISERROR(... but so far nothing works.