Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding up whole dollars only
Hi Mike,
Have a go with Int() function ... =INT(A1) HTH Carim |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding up whole dollars only
Thanks for the feedback ...
Bob is a real pro ...!!! Cheers Carim |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count dollars in related cells | Excel Discussion (Misc queries) | |||
How do I convert euro to us dollars in excel? | Charts and Charting in Excel | |||
Adding dollars | Excel Worksheet Functions | |||
Adding total dollars based on specific data from another column | Excel Discussion (Misc queries) | |||
Separating dollars and cents | Excel Worksheet Functions |