Hi Experts ,

I have an excel database which shows the following
Column A : Client Identification Number
Column B : Trade date
Column C : Trade Volume
Column D : Reward Miles

The computation of the reward miles is base on Trade Volume/25000, roundown
to nearest hundred, on the condition the trades are done on the same day.

i.e 15151 5th Jan 07 $30000 100 miles
1548 6th Jan 07 $25000 100 miles

however, if the client trade on multiple trades on a single day , the
computation of miles will be based on the consolidated trades of the day

in this scenario ,

i.e 15115 5th Jan 07 $15000
15115 5th Jan 07 $15000
15115 5th Jan 07 $20000
15115 6th Jan 07 $25000

In the above scenario , the client in total traded $50000 on 5th Jan in
which he is entitled to 200 miles and $25000 on 6th Jan , in which he is
entitle to 100 miles

Any way to program the cells to compute in such a manner ????????

You could do the calculation with sumproduct, but that would require that you
know which clients traded on which dates. Why not just use a Pivot Table and
let Excel figure that part out as well? Select your data, then Data Pivot
Table. Accept the defaults for data range (Excel data in the highlighted
cells). For the layout, drag client and date to the row fields, and trade
volume to the data field. Assuming that all the trade volume cells have
numbers, Excel will probably default to 'Sum of Trade Volume', which is just
what you need.

"Soultek" wrote:

Hi Experts ,

I have an excel database which shows the following
Column A : Client Identification Number
Column B : Trade date
Column C : Trade Volume
Column D : Reward Miles

The computation of the reward miles is base on Trade Volume/25000, roundown
to nearest hundred, on the condition the trades are done on the same day.

i.e 15151 5th Jan 07 $30000 100 miles
1548 6th Jan 07 $25000 100 miles

however, if the client trade on multiple trades on a single day , the
computation of miles will be based on the consolidated trades of the day

in this scenario ,

i.e 15115 5th Jan 07 $15000
15115 5th Jan 07 $15000
15115 5th Jan 07 $20000
15115 6th Jan 07 $25000

In the above scenario , the client in total traded $50000 on 5th Jan in
which he is entitled to 200 miles and $25000 on 6th Jan , in which he is
entitle to 100 miles

Any way to program the cells to compute in such a manner ????????

Hi bpeltzer,

I did the method using PivotTable , but a new problem occur

Using PviotTable , I manage to lump all the 5th Jan Trades for Client X, and
computed the miles

i.e Client X 5th Jan $75,000 300 miles

however a new problem occur , when the client have multiple trades/multiple
dates, the PivotTable will show the following

i.e Client X 5th Jan $75,000 300 miles
Client X 6th Jan $15,000
Client X 7th Jan $10,000
Total $100,000 400 miles

the correct computation is shown for the daily entries , whereby $75k earn
300miles , $15k and $10k on different dates earn nothing

But on the Total Table , the PivotTable lumped the Trade volume to $100k and
give 400miles !

I would like to have the Total option , but it is the addition of all the
miles earned by the client over the trade dates, Not to re-sum all the volume
and derive the miles.

Any help ???

"bpeltzer" wrote:

You could do the calculation with sumproduct, but that would require that you
know which clients traded on which dates. Why not just use a Pivot Table and
let Excel figure that part out as well? Select your data, then Data Pivot
Table. Accept the defaults for data range (Excel data in the highlighted
cells). For the layout, drag client and date to the row fields, and trade
volume to the data field. Assuming that all the trade volume cells have
numbers, Excel will probably default to 'Sum of Trade Volume', which is just
what you need.

"Soultek" wrote:

Hi Experts ,

I have an excel database which shows the following
Column A : Client Identification Number
Column B : Trade date
Column C : Trade Volume
Column D : Reward Miles

The computation of the reward miles is base on Trade Volume/25000, roundown
to nearest hundred, on the condition the trades are done on the same day.

i.e 15151 5th Jan 07 $30000 100 miles
1548 6th Jan 07 $25000 100 miles

however, if the client trade on multiple trades on a single day , the
computation of miles will be based on the consolidated trades of the day

in this scenario ,

i.e 15115 5th Jan 07 $15000
15115 5th Jan 07 $15000
15115 5th Jan 07 $20000
15115 6th Jan 07 $25000

In the above scenario , the client in total traded $50000 on 5th Jan in
which he is entitled to 200 miles and $25000 on 6th Jan , in which he is
entitle to 100 miles

Any way to program the cells to compute in such a manner ????????

I'm assuming that you've entered a formula outside the Pivot Table to
calculate the reward miles. You could just put that calculation inside an IF
function to ensure that this isn't a total line: =if(b2="","",your reward
formula).
Or, if you want to calculate the client's total rewards,
=if(b2="",sumif(a$1:a1,a1,d$1:d1),your reward formula) (This assumes that
columns A, B and D show the client id, date and rewards, respectively; the
formula should be entered in D2 and autofilled down.)
--Bruce

"Soultek" wrote:

Hi bpeltzer,

I did the method using PivotTable , but a new problem occur

Using PviotTable , I manage to lump all the 5th Jan Trades for Client X, and
computed the miles

i.e Client X 5th Jan $75,000 300 miles

however a new problem occur , when the client have multiple trades/multiple
dates, the PivotTable will show the following

i.e Client X 5th Jan $75,000 300 miles
Client X 6th Jan $15,000
Client X 7th Jan $10,000
Total $100,000 400 miles

the correct computation is shown for the daily entries , whereby $75k earn
300miles , $15k and $10k on different dates earn nothing

But on the Total Table , the PivotTable lumped the Trade volume to $100k and
give 400miles !

I would like to have the Total option , but it is the addition of all the
miles earned by the client over the trade dates, Not to re-sum all the volume
and derive the miles.

Any help ???

"bpeltzer" wrote:

You could do the calculation with sumproduct, but that would require that you
know which clients traded on which dates. Why not just use a Pivot Table and
let Excel figure that part out as well? Select your data, then Data Pivot
Table. Accept the defaults for data range (Excel data in the highlighted
cells). For the layout, drag client and date to the row fields, and trade
volume to the data field. Assuming that all the trade volume cells have
numbers, Excel will probably default to 'Sum of Trade Volume', which is just
what you need.

"Soultek" wrote:

Hi Experts ,

I have an excel database which shows the following
Column A : Client Identification Number
Column B : Trade date
Column C : Trade Volume
Column D : Reward Miles

The computation of the reward miles is base on Trade Volume/25000, roundown
to nearest hundred, on the condition the trades are done on the same day.

i.e 15151 5th Jan 07 $30000 100 miles
1548 6th Jan 07 $25000 100 miles

however, if the client trade on multiple trades on a single day , the
computation of miles will be based on the consolidated trades of the day

in this scenario ,

i.e 15115 5th Jan 07 $15000
15115 5th Jan 07 $15000
15115 5th Jan 07 $20000
15115 6th Jan 07 $25000

In the above scenario , the client in total traded $50000 on 5th Jan in
which he is entitled to 200 miles and $25000 on 6th Jan , in which he is
entitle to 100 miles

Any way to program the cells to compute in such a manner ????????