I have several monthly reports where data is imported form another file, and therefore the values in the data range changes each month, as well as the location and number of non-blank cells. The range is always numeric, values are always in ascending order down the column (though there are typically multiple values that are the same in its respective column), but the location of the last non-blank cell changes with each new month's data set.

I haven't found a solution that does what I'm trying to do, which is:

Find the last non-blank cell in a column, take the value of that last non-blank cell and multiply it to a static number that is in the same row, but a different column to the right.

In the example, the "totals" column is irrelevant. I'm attempting to make a new row at the bottom that returns the value of the last non-blank cell in its respective column multiplied by the static multiplier (column N) that is in that non-blank cell's row.

Any help is greatly appreciated!


Excel Example 1.jpg