How to multiply two arrays together

FlashDota

New Member
Joined
Jun 11, 2023
Messages
33
Office Version
  1. 2021
Platform
  1. Windows
I am trying to do an array multiplication, multiply each of the months with the corresponding numbers from the 'Array' below, for example for "Person 1" do 0.1*150, then 0.2*250 and so on.
As you can see it is returning #VALUE. Even if I try multiplying as an array, or multiply the numbers one by one it still shows #VALUE. What is the problem here and how to fix it?



ListStarting monthMonth 1Month 2Month 3Month 4Month 5Month 6Month 7Month 8Month 9Month 10Month 11Month 12Month 13Month 14Month 15Month 16Month 17Month 18Month 19Month 20Month 21Month 22Month 23Month 24Month 25Month 26Month 27Month 28Month 29Month 30Month 31Month 32Month 33Month 34Month 35Month 36
Person 11/1/2024150250350150250350150250150250350350150250350150150250350250350150250350150250350150250350150250150250350350
Person 22/1/2024290390490290390490290390290390490490290390490290290390490390490290390490290390490290390490290390290390490490
Person 34/1/2024640340140640340140640340640340140140640340140640640340140340140640340140640340140640340140640340640340140140
1/1/20242/1/20243/1/20244/1/20245/1/20246/1/20247/1/20248/1/20249/1/202410/1/202411/1/202412/1/2024
0.10.20.30.40.50.60.70.80.90.80.70.6
Array
Person 11/1/20240.10.20.30.40.50.60.70.80.90.80.70.60.10.20.30.40.50.60.70.80.90.80.70.60.10.20.30.40.50.60.70.80.90.80.70.6
Person 22/1/20240.20.30.40.50.60.70.80.90.80.70.60.10.20.30.40.50.60.70.80.90.80.70.60.10.20.30.40.50.60.70.80.90.80.70.60.1
Person 34/1/20240.40.50.60.70.80.90.80.70.60.10.20.30.40.50.60.70.80.90.80.70.60.10.20.30.40.50.60.70.80.90.80.70.60.10.20.3
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
The formula for the Array results: =INDEX($A$10:$L$10, MOD(XMATCH($B14, $A$9:$L$9, 0) - 1 + SEQUENCE(1, 36, 0), 12) + 1)
I am trying to do an array multiplication, multiply each of the months with the corresponding numbers from the 'Array' below, for example for "Person 1" do 0.1*150, then 0.2*250 and so on.
As you can see it is returning #VALUE. Even if I try multiplying as an array, or multiply the numbers one by one it still shows #VALUE. What is the problem here and how to fix it?



ListStarting monthMonth 1Month 2Month 3Month 4Month 5Month 6Month 7Month 8Month 9Month 10Month 11Month 12Month 13Month 14Month 15Month 16Month 17Month 18Month 19Month 20Month 21Month 22Month 23Month 24Month 25Month 26Month 27Month 28Month 29Month 30Month 31Month 32Month 33Month 34Month 35Month 36
Person 11/1/2024150250350150250350150250150250350350150250350150150250350250350150250350150250350150250350150250150250350350
Person 22/1/2024290390490290390490290390290390490490290390490290290390490390490290390490290390490290390490290390290390490490
Person 34/1/2024640340140640340140640340640340140140640340140640640340140340140640340140640340140640340140640340640340140140
1/1/20242/1/20243/1/20244/1/20245/1/20246/1/20247/1/20248/1/20249/1/202410/1/202411/1/202412/1/2024
0.10.20.30.40.50.60.70.80.90.80.70.6
Array
Person 11/1/20240.10.20.30.40.50.60.70.80.90.80.70.60.10.20.30.40.50.60.70.80.90.80.70.60.10.20.30.40.50.60.70.80.90.80.70.6
Person 22/1/20240.20.30.40.50.60.70.80.90.80.70.60.10.20.30.40.50.60.70.80.90.80.70.60.10.20.30.40.50.60.70.80.90.80.70.60.1
Person 34/1/20240.40.50.60.70.80.90.80.70.60.10.20.30.40.50.60.70.80.90.80.70.60.10.20.30.40.50.60.70.80.90.80.70.60.10.20.3
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
The formula for the Array results: =INDEX($A$10:$L$10, MOD(XMATCH($B14, $A$9:$L$9, 0) - 1 + SEQUENCE(1, 36, 0), 12) + 1)
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top