Adding up whole dollars only
 

Hi all
I am trying to add up my credit card points for each transaction i make,
problem is points are only counted for whole dollars, for example a
transaction of $82.50 will earn 82 points, $100.95 will earn 100 points and
so on, to further complicate matters mastercard earns 1 point for every
dollar & our Amex card earns 2 points for every dollar, example for an Amex
transation of $175.95 will earn 350 points ($175x2), the cents are not
calculated.

Example of database so far

Column G = dollars spent
Column J = Mastercard or Amex ( I enter an M or an A here)
Column L = Points, formula i have so far is =IF(J6="m",(G6*1),IF(J6="a",G6*2))

G6 = $82.50, J6 = M, L6 = 82.50 (this should read just 82)
G7 = $100.95, J7 = A, L7 = 201.90 (this should read just 200)

I hope this all maks sense
--
Thanks Mike T

Adding up whole dollars only
 

Hi Mike,

Have a go with Int() function ...
=INT(A1)

HTH
Carim

Adding up whole dollars only
 

=INT(G2)*(1+(J2="M"))

to calculate the points by line, but to add them all in one sweep


=SUMPRODUCT(INT(G2:G100),--(J2:J100="A"))

and

=SUMPRODUCT(INT(G2:G100)*2,--(J2:J100="M"))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Mike T" wrote in message
...
Hi all
I am trying to add up my credit card points for each transaction i make,
problem is points are only counted for whole dollars, for example a
transaction of $82.50 will earn 82 points, $100.95 will earn 100 points
and
so on, to further complicate matters mastercard earns 1 point for every
dollar & our Amex card earns 2 points for every dollar, example for an
Amex
transation of $175.95 will earn 350 points ($175x2), the cents are not
calculated.

Example of database so far

Column G = dollars spent
Column J = Mastercard or Amex ( I enter an M or an A here)
Column L = Points, formula i have so far is
=IF(J6="m",(G6*1),IF(J6="a",G6*2))

G6 = $82.50, J6 = M, L6 = 82.50 (this should read just 82)
G7 = $100.95, J7 = A, L7 = 201.90 (this should read just 200)

I hope this all maks sense
--
Thanks Mike T

Adding up whole dollars only
 

Thanks Carim for your quick response, that works up to a point but where i
see it fall down is it includes the cents and then rounds down to the dollar
example is $139.90, should be 278 but with your formula reads 279 which it
looks like it adds the 90c twice and then rounds down. It looks like its got
to do with the second * part - INT(G10*2)))

the formula reads =IF(J10="m",INT(G10*1),IF(J10="a",INT(G10*2)))

--
Thanks Mike T


"Carim" wrote:

Hi Mike,

Have a go with Int() function ...
=INT(A1)

HTH
Carim

Adding up whole dollars only
 

Hi Mike,

Watch your multiplications ...
=IF(J10="m",INT(G10*1),IF(J10="a",INT(G10*2)))
should be
=IF(J10="m",INT(G10)*1,IF(J10="a",INT(G10)*2))

HTH
Cheers
Carim

Adding up whole dollars only
 

Thanks Carim
changed the multification to outside the bracket and works like a charm
Thanks again for your help and thanks to Bob Philips for his reply and help
also.

what do you guys do when you go to sleep at night, dream formulas and stuff
!!!!
--
Thanks Mike T


"Carim" wrote:

Hi Mike,

Watch your multiplications ...
=IF(J10="m",INT(G10*1),IF(J10="a",INT(G10*2)))
should be
=IF(J10="m",INT(G10)*1,IF(J10="a",INT(G10)*2))

HTH
Cheers
Carim

Adding up whole dollars only
 

Thanks for the feedback ...

Bob is a real pro ...!!!

Cheers
Carim

Adding up whole dollars only
 

small point, the *1 is redundant

=IF(J10="m",INT(G10),IF(J10="a",INT(G10)*2))

and if it can only be a or m you can reduce it further

=IF(J10="m",INT(G10),INT(G10)*2)

always good to remove superfluous code <G


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Carim" wrote in message
ups.com...
Hi Mike,

Watch your multiplications ...
=IF(J10="m",INT(G10*1),IF(J10="a",INT(G10*2)))
should be
=IF(J10="m",INT(G10)*1,IF(J10="a",INT(G10)*2))

HTH
Cheers
Carim

Adding up whole dollars only
 

Bob
thanks again, simplifying a formula is always better, less chance of making
errors

--
Thanks Mike T


"Bob Phillips" wrote:

small point, the *1 is redundant

=IF(J10="m",INT(G10),IF(J10="a",INT(G10)*2))

and if it can only be a or m you can reduce it further

=IF(J10="m",INT(G10),INT(G10)*2)

always good to remove superfluous code <G


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Carim" wrote in message
ups.com...
Hi Mike,

Watch your multiplications ...
=IF(J10="m",INT(G10*1),IF(J10="a",INT(G10*2)))
should be
=IF(J10="m",INT(G10)*1,IF(J10="a",INT(G10)*2))

HTH
Cheers
Carim

Adding up whole dollars only
 

Dear Bob

Nice solution with sumproduct. I learned something new. Also
=INT(G2)*(1+(J2="M")) is very nice.

As Mike has mentioned in his original post, Amex dollars earn 2 points. So I
think you have to change place of "M" and "A" in all your three equations.

"Bob Phillips" wrote in message
...
=INT(G2)*(1+(J2="M"))

to calculate the points by line, but to add them all in one sweep


=SUMPRODUCT(INT(G2:G100),--(J2:J100="A"))

and

=SUMPRODUCT(INT(G2:G100)*2,--(J2:J100="M"))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Mike T" wrote in message
...
Hi all
I am trying to add up my credit card points for each transaction i make,
problem is points are only counted for whole dollars, for example a
transaction of $82.50 will earn 82 points, $100.95 will earn 100 points
and
so on, to further complicate matters mastercard earns 1 point for every
dollar & our Amex card earns 2 points for every dollar, example for an
Amex
transation of $175.95 will earn 350 points ($175x2), the cents are not
calculated.

Example of database so far

Column G = dollars spent
Column J = Mastercard or Amex ( I enter an M or an A here)
Column L = Points, formula i have so far is
=IF(J6="m",(G6*1),IF(J6="a",G6*2))

G6 = $82.50, J6 = M, L6 = 82.50 (this should read just 82)
G7 = $100.95, J7 = A, L7 = 201.90 (this should read just 200)

I hope this all maks sense
--
Thanks Mike T

Adding up whole dollars only
 

You are correct. I re-read the original post, and I did get the identifiers
the wrong way around.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Rasoul Khoshravan" wrote in message
...
Dear Bob

Nice solution with sumproduct. I learned something new. Also
=INT(G2)*(1+(J2="M")) is very nice.

As Mike has mentioned in his original post, Amex dollars earn 2 points. So
I
think you have to change place of "M" and "A" in all your three equations.

"Bob Phillips" wrote in message
...
=INT(G2)*(1+(J2="M"))

to calculate the points by line, but to add them all in one sweep


=SUMPRODUCT(INT(G2:G100),--(J2:J100="A"))

and

=SUMPRODUCT(INT(G2:G100)*2,--(J2:J100="M"))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Mike T" wrote in message
...
Hi all
I am trying to add up my credit card points for each transaction i
make,
problem is points are only counted for whole dollars, for example a
transaction of $82.50 will earn 82 points, $100.95 will earn 100 points
and
so on, to further complicate matters mastercard earns 1 point for every
dollar & our Amex card earns 2 points for every dollar, example for an
Amex
transation of $175.95 will earn 350 points ($175x2), the cents are not
calculated.

Example of database so far

Column G = dollars spent
Column J = Mastercard or Amex ( I enter an M or an A here)
Column L = Points, formula i have so far is
=IF(J6="m",(G6*1),IF(J6="a",G6*2))

G6 = $82.50, J6 = M, L6 = 82.50 (this should read just 82)
G7 = $100.95, J7 = A, L7 = 201.90 (this should read just 200)

I hope this all maks sense
--
Thanks Mike T