Show me the Money

[clemkonan]

I am tracking US and Canadian credit card charges for a list of items over the last year. I want to end up with the totals for each column so that I can show total US and total Canadian spent. However, on occasion the statement does not include the US amount and purchases in Canada have to be back calculated to avoid having zeros in the US column.

I am using three columns A1 = US$ B1 = CDN $ and C1 = Conversion rate as US / CDN

So a row of data reads:
A2= 31.66 US
B2= 32.64 CDN
C2 = 31.66/ 32.64 = 0.969975

for my next entry :

A3= Blank

B3 = 50.13 CDN

I want to set M3 = 0.969975

Then I want a formula that looks at A3 (well in fact all of column A) and says that if that cell is blank do the calculation M3 X B3, otherwise do nothing because there is a value there.


What would really be neat would be to have the formula look for the month which I have coded as 1,2,3 ...12 where 2 = February, and take an average of column "C " the conversion rates and dump that value into M3. For each row of data I have code the month at D1 so that October's statement for example = 10

If that's too complicated I can manually do that part to get the value for M3 but I need the formula.

Thanks again

 

[Robert Mika]

The first part I recon will be like this:

Excel Workbook
	A	B	C	D	M
1					0.969975
2	31.66	32.64	0.969975
3		50.13	0.969975
Sheet1

For the second.
Where do you keep your months?
or do you have columns with dates?

 

[clemkonan]

I have an adjacent column originally with dates like Oct 13 -Nov 15 as per the statement but since my first two columns are transaction date and posted date I simplified this to 1, 2, 3...12 where 1 = January ,etc. This means if January has 23 record each records has a 1 .

Thanks for the help much appreciated.

 

[Robert Mika]

If you want to average based on C and then put the result in M1 you will get circular refernces as (in example) C2 will point back to M1.