Problem with adding up formula

[gorang]

Hi all

I hope someone can help me with this formula as its doing my head in

I am trying to make up a sheet that auto calcs miles per gallon etc.

The problem I have is that if i put in the miles everytime then there is no problem getting everything to work

but if I put in only litres and no miles I can't get it to add up the litres on each row "until" the miles are put in, but ONLY back up to where the miles were last added

I can understand why the formulas I have been trying are NOT working, I just can't sus the formula that will work

I have shown in the attachment what I need it to do by putting in the answers manually and I have also shown one of the formulas I have tried which does not work

hope someone can help me with this

thanks in anticipation

Gordon

[Sindhus]

Try this formula

=SUM(IF(C5="",0,INDIRECT("B"&LARGE(IF($C$1:C4<>"",ROW($C$1:C4)),1)+1&":B"&ROW())))

entered with ctrl+shift+enter

[Dave H9]

I think I may be misunderstanding but here goes -

Column B is litres
Column C is miles

Should column D not be working out miles per litre (BxC)?
Or should you be converting to MPG (BxCx3.78541178)?

For total just sum all column C at the end of your data.

Dave H

[gorang]

thanks for the quick replies

Try this formula

Formula:
=SUM(IF(C5="",0,INDIRECT("B"&LARGE(IF($C$1:C4<>"",ROW($C$1:C4)),1)+1&":B"&ROW()))) entered with ctrl+shift+enter

Sorry but you have completly lost me here

Should column D not be working out miles per litre (BxC)?

No as that is worked out in another colum but in order for that calc to work I need the answer from colum D ie the total amount of litres that has been added

colum D has to work out how many litres has been put in since the miles was recorded last as per the the attachment


if the litre's and miles are recorded in every row then the calcs would work fine with no problems

But like in the attachment the miles will not be added in every row and thats where the probs start

When the miles are not added in each row I need the litres in all the previous rows (up to the row where the mile were last added) to add up, and thats what I can't make happen automatically

hope that makes sense

I will make up a dummy sheet with actual entries and attach it to see if that clears up the confusion as to what I need

Thanks

Gordon

[Sindhus]

Is this not your requirement?
[QUOTE
Sorry but you have completly lost me here



[/QUOTE]

[Dave H9]

I would say that Sinhus has given you the solution.

Please see attached.

Dave H

[gorang]

Sorry Sinhus,

yes you have gave me the solution,
I just didn't put it correct the first time so didn't get it to work
as I never changed the first cell number (what a donut I am) its been one of them days up to now but hopefully it will now get better lol

Thank you thank you thank you very much

And thanks to you as well Dave H for spelling it out for me in the sheet

much appreciation to both of you

thank you

Gordon

[gorang]

Sorry Folks

The formula works on the test spready but when I try to add colums in the front of the formula colum it goes back to zero or when I enter the formula into the sheet I am working on it just comes up saying

#VALUE!

What am I doing wrong????????

any ideas please

Have attached the sheet I am working on see if that helps

Thanks

Gordon

[gorang]

Thanks Dave H9

That worked a dream

Thank you very very much

star has been clicked

[gorang]

Thank you Fotis1991

Just got it when you were repling to me

Thanks again for everyones help with this

Gordon

[Dave H9]

Hi Gordon,

It is an array formula which was provided. Try selecting cell H9, instead of pressing just enter, hold CTRL+Shift and press enter. You will notice the formula is now surrounded by this type of bracket -{} signifying an array formula. You can now copy this cell down the required range.


Dave H

[gorang]

Sorry I forgot to add I am using office 2010 if that makes a difference

[Fotis1991]

You have to confirm the formula using Control+shift+Enter-Not just enter. This is an Array formula.

[Fotis1991]

You are welcome Gordon. In fact i did nothing. Congr to Sindhus for the nice formula and of course to Dave for all help that provided.