How to find next value in upper row?

  • Thread starter Thread starter NeedToKnow
  • Start date Start date
N

NeedToKnow

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:
 
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
Click to expand...

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)
 
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))
 
This gives ne #N/A but I think it's closer solution than any of mine. I'll
keep trying

"T. Valko" kirjoitti:
 
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:
 
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:
 
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:
 
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.
 
Back
Top