Is there a function to multiple same row of two tables? MMULT does not

Copper Contributor

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

SunnyPhoenix1_1-1708243371313.png

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?

  1. Headers would become a Column (a row for each Rate)
  2. 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.

SunnyPhoenix1_0-1708242896528.pngSunnyPhoenix1_2-1708243994139.png

 

 

4 Replies

@SunnyPhoenix1 

Don't you want a simple SUMPRODUCT?

 

=SUMPRODUCT('2023 CAD Exchange'!B2:D13, 'HSBC Deposits in CAD'!B2:D13)

@Hans Vogelaar 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.

@SunnyPhoenix1 

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?

@SunnyPhoenix1 

To multiply each deposit by the corresponding exchange rate would require a straightforward multiplication.

= rates * deposits

To 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.