I'm wondering if someone could help with a problem I am having - not so much
a problem - more I'm not as good at Excel as others here and they may be
able to help.

I'm treasurer of a semi-professional non-league football club. As such I
have to calculate travelling expenses for each player. The tax man taxes
players on home games and training sessions but doesn't tax the travelling
expenses for away games for those players that take their own cars and not
the bus. Currently we pay them 15p per mile plus an additional 3p per mile
for every passenger - I know, not much - but we are on a very limited
budget.

I have the following columns
A - Date
B - Name of player
C - Passengers
D - Miles

There are columns for other things such as training session, home game, away
game but it is just the first four columns I am concerned with just now.

Because of the amount paid out in travelling expenses (and the ridiculous
mileage claimed by some players) I am considering scrapping the 3p per
additional passenger amount. I currently use the SUMIF formula to see how
much in total each player has received to date in expenses for training
sessions, home games and away games. I was wondering if someone could find a
formula to do the following :

For each individual find out the total they would lose out on if they
weren't paid this 3p per extra passenger amount. I currently have rows for
each player at the end of the document where this total could go (in any
column from C - K). If this is not possible is it possible to have a total
amount where if the amount in column C 0 then sum them all up for C x D x
£0.03 . (I know it will be hard to believe after reading that last sentence
but English IS my first language).

Hopefully I wouldn't need an extra column as the space is limited on the
page.

Thanks in advance if anyone an help me.

Hi

One way would be to use a Pivot Table.
InsertNameDefineName myData Refers to
=$A$1:INDEX($A:$A,COUNTA($A:$A))

DataPivot TableNextsource =myData LayoutDrag Name to Row areaDrag
Date to Row areaDrag Mileage to Data areaDouble click on Mileage and
ensure it is set to SUMFinish
On the resulting Pivot Table (created on a new sheet), from the Dropdown on
the Pivot Table Toolbar choose FormulasCalculated Field
In Field type Base PayIn Formula type =Mileage*0.15Add
In the next field use title Supplementin Formula type
=Mileage*(Passengers*0.03)Add
in the next field use Title Total Payin formula type
=Mileage*(0.15+(Passengers*0.03))AddOK

On the Pivot Table drag Data button, and drop on Total, aand you will see
the three sets of figures side by side.

--
Regards
Roger Govier

"SM" wrote in message
...
I'm wondering if someone could help with a problem I am having - not so
much a problem - more I'm not as good at Excel as others here and they may
be able to help.

I'm treasurer of a semi-professional non-league football club. As such I
have to calculate travelling expenses for each player. The tax man taxes
players on home games and training sessions but doesn't tax the travelling
expenses for away games for those players that take their own cars and not
the bus. Currently we pay them 15p per mile plus an additional 3p per mile
for every passenger - I know, not much - but we are on a very limited
budget.

I have the following columns
A - Date
B - Name of player
C - Passengers
D - Miles

There are columns for other things such as training session, home game,
away game but it is just the first four columns I am concerned with just
now.

Because of the amount paid out in travelling expenses (and the ridiculous
mileage claimed by some players) I am considering scrapping the 3p per
additional passenger amount. I currently use the SUMIF formula to see how
much in total each player has received to date in expenses for training
sessions, home games and away games. I was wondering if someone could find
a formula to do the following :

For each individual find out the total they would lose out on if they
weren't paid this 3p per extra passenger amount. I currently have rows for
each player at the end of the document where this total could go (in any
column from C - K). If this is not possible is it possible to have a total
amount where if the amount in column C 0 then sum them all up for C x D
x £0.03 . (I know it will be hard to believe after reading that last
sentence but English IS my first language).

Hopefully I wouldn't need an extra column as the space is limited on the
page.

Thanks in advance if anyone an help me.

Too complicated for me as it was my first go at pivot tables. I did some
fiddling about and can see how pivot tables will help me in future. So
thanks for pointing me in that direction.

I went down the road of adding an extra column and it looks like the saving
will be larger than I first thought - probably pay for half a season's wages
for another player.


"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
Hi

One way would be to use a Pivot Table.
InsertNameDefineName myData Refers to
=$A$1:INDEX($A:$A,COUNTA($A:$A))

DataPivot TableNextsource =myData LayoutDrag Name to Row areaDrag
Date to Row areaDrag Mileage to Data areaDouble click on Mileage and
ensure it is set to SUMFinish
On the resulting Pivot Table (created on a new sheet), from the Dropdown
on the Pivot Table Toolbar choose FormulasCalculated Field
In Field type Base PayIn Formula type =Mileage*0.15Add
In the next field use title Supplementin Formula type
=Mileage*(Passengers*0.03)Add
in the next field use Title Total Payin formula type
=Mileage*(0.15+(Passengers*0.03))AddOK

On the Pivot Table drag Data button, and drop on Total, aand you will see
the three sets of figures side by side.

--
Regards
Roger Govier

"SM" wrote in message
...
I'm wondering if someone could help with a problem I am having - not so
much a problem - more I'm not as good at Excel as others here and they
may be able to help.

I'm treasurer of a semi-professional non-league football club. As such I
have to calculate travelling expenses for each player. The tax man taxes
players on home games and training sessions but doesn't tax the
travelling expenses for away games for those players that take their own
cars and not the bus. Currently we pay them 15p per mile plus an
additional 3p per mile for every passenger - I know, not much - but we
are on a very limited budget.

I have the following columns
A - Date
B - Name of player
C - Passengers
D - Miles

There are columns for other things such as training session, home game,
away game but it is just the first four columns I am concerned with just
now.

Because of the amount paid out in travelling expenses (and the ridiculous
mileage claimed by some players) I am considering scrapping the 3p per
additional passenger amount. I currently use the SUMIF formula to see how
much in total each player has received to date in expenses for training
sessions, home games and away games. I was wondering if someone could
find a formula to do the following :

For each individual find out the total they would lose out on if they
weren't paid this 3p per extra passenger amount. I currently have rows
for each player at the end of the document where this total could go (in
any column from C - K). If this is not possible is it possible to have a
total amount where if the amount in column C 0 then sum them all up for
C x D x £0.03 . (I know it will be hard to believe after reading that
last sentence but English IS my first language).

Hopefully I wouldn't need an extra column as the space is limited on the
page.

Thanks in advance if anyone an help me.