Create a new array that skips row
Hi everyone,
I am looking for help with multiplication. Basically let say there are values in cells A1:A8 and B1:B4 and I am trying to multiply A1 with B1, A3 with B2, A5 with B3 and so on. For A column every nth row is skipped when multiplying with B column. Can I know if I would be able to achieve that by using arrays? I understand that {=SUM(A1:A8*B1:B4)} will be able to do the trick but I cant figure out how I will be able to skip the row.
Thank you so much for helping.
Hello kzh11411 and Welcome to Excel Forum.
I imagine that you are looking for a single formula solution, and perhaps one exists and perhaps I or another volunteer here will think of how to put it together. In the mean time here is a proposed solution that involves a helper column (C).
The formula that populates the helper column is: =INDIRECT("B"&ROUNDUP(ROW(B1)/D$1,0))
Cell D1, referenced by the formula as well as the one following, is the nth row, in the case of your example it would be 2
The formula that gives the sum of the values in column B and every nth (2nd) value in column A is:Note: the formula looks for more cells than the originally stipulated 8 so that if you would like to change the value in D1 to say 3 you would be able to place 9 values in column A and 3 values in column B. (I hope that makes sense).Formula:
Please Login or Register to view this content.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Thank you so much for your help! I think this will be able to solve the problem for the moment. Indeed I am working on more than 8 rows so your solutions can save me a lot of the time.
You're Welcome and thank you for the feedback. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks