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.

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.

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.