Forum Discussion
Is there a function to multiple same row of two tables? MMULT does not
Is there a function to multiple cell for cell of two Tables? Otherwise, to use MMULT, my understanding is I need to transpose; if so, wouldn't date information across a row of columns become unconventional (vs a column of dates)?
I prefer a function similar to MMULT but different
EXAMPLE (screenshot below)
Table A, as downloaded, has Headers of Rates; Column 1 has 12 rows (Jan-Dec).
Table B is my data entry; Headers of Multiple Foreign Currencies; 12 rows (Jan-Dec).
Is there a function to multiple Rate to each Foreign Currency without Transposing?
To use MMULT, do I not have to transpose, say Table A?
- Headers would become a Column (a row for each Rate)
- Dates would become Headers (a column for each Month)
IMHO, it is preferable to keep conventional view of Historical Exchange Rates; that is without transpose.
Don't you want a simple SUMPRODUCT?
=SUMPRODUCT('2023 CAD Exchange'!B2:D13, 'HSBC Deposits in CAD'!B2:D13)
HansVogelaar pls correct me if wrong, SUMPRODUCT provides one output whereas MMULT provides column of outputs?
For Tax reporting, I need a column of months and next to it, column of CAD calculated.
Your second screenshot shows a single cell with a #VALUE! result. I guess I don't understand what you want.
Could you attach a small sample workbook demonstrating the problem (without sensitive data), and an indication of the desired output, or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
To multiply each deposit by the corresponding exchange rate would require a straightforward multiplication.
= rates * depositsTo sum the amounts held in each currency, you could use MMULT
= MMULT(rates * deposits, {1;1;1})or you could use a 365 helper function to sum across rows
= BYROW(rates * deposits, LAMBDA(x, SUM(x)))In the near future this last formula will reduce to
= BYROW(rates * deposits, SUM)which is somewhat less intimidating.
