Excel questions

[Melena]

Going to start this off with the disclaimer that i'm an execl noob and this is probably something really simple.

I need to create a simple excel sheet for mileage tracking for Uber. It's essentially going to be 5 columns, 1 for each date.

Column A= Date
Column B= Starting Miles
Column C= Ending Miles
Column D= Total Miles
Column E= Running yearly total of miles.

So what i'm essentially trying to do for each row to automatically have it take Column C, Subtract Column B from it and put it in Column D. Column E then will be an addition to each line in Column D so when I add the next date, it automatically updates. How can I do this? The only formula I can figure out is to manually change it on each line. So one formula would be Column C Row 3 Minus Column B Row 3 then for the next row I have to manually update it.

Any automatic way to automate this?

[Fiye]

Can you copy paste the formula to the next line? I know in google sheets it'll adjust all the variables to the next row. Dunno how Excel works.

Like you'd be writing the columns like so.



Wherein you'd manually update Column B and C.

[synistar]

On Excel, Column D will be =SUM(C2-B2). Then you should be able to drag the bottom right of the cell downwards to have it continue for each row.

Bottom right of the cell there's that tiny thicker box. Click and hold and drag it down Column D

It will be correct when Row 3 is =SUM(C3-B3) and Row 4 has it for row 4, etc.

Then for the running total, you can do =SUM(D:D) and it will take the running sum for the total mileage. Just have this formula be anywhere besides the first 4 columns.

[Mabubeezareel]

There's a few different ways to do this, but the basic idea is that you first want to set a formula for Column D to subtract Column B from Column C. Let's assume that you're using a header row, so your first row of data will be row 2. Set the formula for D2 as follows:

D2 = C2-B2

Use the fill handle to then copy the formula in D2 down the rest of column D to however many rows you need.
Next, you'll want to set Column E to be equal to Column D for the first row of data.

E2 = D2

For the next row (3rd row, technically), Column E would be D3 + E2:

E3 = D3+E2

Once you have that formula for E3, use the fill handle again to copy the formula in E3 down the rest of column E. Doing so will preserve the relative integrity of the formula, so that other rows in Column E would look like this:

E4 = D4+E3
E5 = D5+E4

etc.

That'll get the very basic job done, but could result in some possibly undesired behavior. For instance, the entirety of Column D will likely show as 0 until you enter values in either Columns B or C. And if you enter data into only one or the other, you'll get a negative number until both are present. Likewise with Column E, in that the entirety of the column will have SOME number in it once you add data into B2 and C2. If you don't like either of those behaviors, you can use some basic IF() functions in combination with an AND() to calculate only when there's data in the appropriate cells. Something like this would work for Column D, in that it will only subtract Column B from Column C if there's value present in both cells:

D2 = IF(AND(C2<>"",B2<>""),C2-B2,"")

Same basic principle applies for Column E3, though you'd want to be checking if D2 is blank. If it's not blank, then you'd add D3 and E2:

E3 =IF(D3<>"",D3+E2,"")