Excel help please

Airwolf1

Hi,

I use excel 2007 at the moment, and have a spreadsheet set up where I add in each months accounts. This spreadsheet also works out my mileage at £0.40 per mile. However, is there a way to make it so when the spreadsheet realises I've reached 10,000 miles (in total), that anything extra should be worked out at £0.25 per mile?

So Col A is the name of the month.
Col B is the mileage done each month.
Col C is where it works out the cost of the mileage.
Col D is other expenses.
Col E adds C and D.

How do I make it so when the total miles on that spreadsheet, when adding up to more than 10,000, needs to be worked out differently?

Many thanks.

Valli

Do you have a cell for total mileage (ie cumulative mileage?)
Presumably this is per annum so
have a cell for total mileage so far then have another cell with conditional formatting applied so that if the total goes above 10000
(apply conditional formatting) if X>10000then ...

does that help?

Mobeer

Have cells named Rate1 (0.40), Rate2 (0.25), Max1 (10000)

Then have a cell P1 that adds up all the mileage values (assuming they are in B1:B12)
=sum(B1:B12)

Then have another cell that multiples all mile by the lower rate if less than the limit on miles, else multiplies the first limit on miles times the first rate and remaining miles times the second rate:
=IF(P1<=max1,P1*rate1,max1*rate1+(P1-max1)*rate2)

--
Valli, conditional formatting is for formatting cells, not for calculations

Valli

Yes I know!
- what I meant was to have another cell which checked the condition of the total mileage, ie above/below 10000 then to apply a relevant calculation elsewhere -
ie if A1 <=10000 then x by .40 etc.
and possibly, also, OP could apply a colour to the cell to show if the mileage had exceeded 10000, if he needed to know that. That's why I put...

Valli wrote: »

Do you have a cell for total mileage (ie cumulative mileage?)
Presumably this is per annum so
have a cell for total mileage so far then have another cell with conditional formatting applied so that if the total goes above 10000
(apply conditional formatting) if X>10000then ...

does that help?

'X' in this case referred to the cell address in which the cumulative mileage was calculated. But clumsily put, by me! I didn't want to scare the OP!

Airwolf1

Thanks for your replies guys. Erm...can someone actually type me out what I should put as the formula please!! It is B2-B14 that contains monthly mileage, with B15 adding them up as I going along. B15 is currently is currently on 16,280 miles and it is currently calculating all this at £0.40.

I appreciate your help guys.

geewhiz

Airwolf1 wrote: »

Thanks for your replies guys. Erm...can someone actually type me out what I should put as the formula please!! It is B2-B14 that contains monthly mileage, with B15 adding them up as I going along. B15 is currently is currently on 16,280 miles and it is currently calculating all this at £0.40.

I appreciate your help guys.

Assuming B15 has 16,280 in it put this in another cell:

=IF(B15>10000,(10000*0.4)+((B15-10000)*0.25),B15*0.4)

Airwolf1

Thank you geewhiz, that is great. In Column C, it works out the cost in terms of pounds and pence, eg the formula for cell C14 is =B14*0.4.

Now, cell C15 has the formula =SUM(C2:C14). Is there anyway that I can incorporate the formula that geewhiz just gave me into into C15, as I want C15 to add up the C cells too.

Thank you very much for help so far.

geewhiz

Airwolf1 wrote: »

Thank you geewhiz, that is great. In Column C, it works out the cost in terms of pounds and pence, eg the formula for cell C14 is =B14*0.4.

Now, cell C15 has the formula =SUM(C2:C14). Is there anyway that I can incorporate the formula that geewhiz just gave me into into C15, as I want C15 to add up the C cells too.

Thank you very much for help so far.

That would just become:

=IF(SUM(C2:C14)>10000,(10000*0.4)+((SUM(C2:C14)-10000)*0.25),SUM(C2:C14)*0.4)

Airwolf1

Thanks geewhiz - I think there may be an error in that calculation somewhere. When I put the above in as the formula in C15, it is bringing the cost to £2,604.80. The actual cost should be 0.4 x 10,000, then add the answer to 6280 x £0.25, so I'm looking at arriving at £5,570.

Airwolf1

Ok - I may have got a couple of things wrong myself, I apologise. That formula works fine, but it means my other columns are wrong, which isn't a problem.

Airwolf1

Shall we go to get this whole thing right, if possible?

Column A A2-A14 contains the months April-April.
Column B B2-B14 contains the miles done for each month, with B15 adding the total miles up as I go along.
Column C C2-C14 contains the workings out for the cost of mileage, eg, formula for C4 is =B4*0.4, with C15 again adding C2-C14 up.
Column D contains Sundry expenses for each month, with D15 adding D2-D14.
Col E is total expenses, so formula for cell E4 is =C4+D4, again, E15 adding E2:E14.
Column F is Gross Income - these figures are copied from a different spreadsheet, but again, F15 adds up F2:F14.
Column G is taxable income, and the formula for G4 is =F4-E4, with G15 adding G2:G14

I can use the formula given by geewhiz which makes some of my other totals redundant. How can I incorporate row 15 (adding up the totals) but to also work out the mileage cost correctly too? Any ideas?

Thank you