Specialized Sum Formula

[excel-help]

Hi All,

I am trying to write a formula that will sum a set of numbers in a range (several columns in one row). The number of columns in the range I want to be summed will be typed into another cell. I want the sum to always start one column to the left from where the formula is (and sum backward from left to right).

There are a few complicating items:

1. If the number of columns to be summed is a fraction (i.e. 1.25), I would like the final number to be multiplied by the fraction and then summed (i.e., for 1.5, two numbers would be summed: the first and 0.25 x the second).
2. If the number of columns to be summed is more than the columns in the range, just sum all the columns in the range.
3. If the number of columns to be summed is less than 1 (i.e. 0.5), do not move one column over from the column where the formula is. Instead, multiply the fraction by the number in the same column.
4. If the number of columns to be summed is 0, the answer should be 0.
5. If possible, I would like to exclude any columns in the range that are empty in the calculation (but this is not critical if it overly complicates the formula).

I have included an excel file to better explain what I am looking to do.

Thank you very much.

[excel-help]

I think I solved my question. It's not elegant, but it seems to work. The formula I am using is in cell O6 (and copied down through O10).

The only issue I haven't been able to solve is that if I insert a blank cell inside the range of numbers, the formula sums that cell anyway (and counts it as a 0), instead of ignoring it. If anyone has a solution for this (or has a way to make the formula more elegant), please let me know.

Thanks.