Complex formula

[steve]

Hi

I need some help,please.

I need a formula to claculate a value when input A and B are inserted. The
table below details the values I am working with and depending on the values
the results will change.
If we drew a line from 8 and 3 the yellow squares would be used in the
calculation.

Input A
1 2 3 4 5 6 7 8 9 10 11 12 36 Input A 8
2 3 4 5 6 7 8 9 10 11 12 20 Input B 3
3 4 5 6 7 8 9 10 11 12 12 Result 68
4 5 6 7 8 9 10 11 12
Input B 5 6 7 8 9 10 11 12
6 7 8 9 10 11 12
7 8 9 10 11 12
8 9 10 11 12
9 10 11 12
10 11 12
11 12
12
68


If we changed the inputs and the position of the line the following squares
would be used.


Input A
1 2 3 4 5 6 7 8 9 10 11 12 78 Input A 12
2 3 4 5 6 7 8 9 10 11 12 77 Input B 12
3 4 5 6 7 8 9 10 11 12 75 Result 650
4 5 6 7 8 9 10 11 12 72
Input B 5 6 7 8 9 10 11 12 68
6 7 8 9 10 11 12 63
7 8 9 10 11 12 57
8 9 10 11 12 50
9 10 11 12 42
10 11 12 33
11 12 23
12 12
650

 

[John C]

There are no 'yellow' squares. More explanation is needed.

 

[steve]

Sorry John

The colour was there when I sent the email. I can send the excel sheet if it
helps.


Input A
1 2 3 4 5 6 7 8 9 10 11 12 Total - 36
Input A 8
2 3 4 5 6 7 8 9 10 11 12 Total - 20
Input B 3
3 4 5 6 7 8 9 10 11 12 Total - 12
Result 68
4 5 6 7 8 9 10 11 12
Input B 5 6 7 8 9 10 11 12
6 7 8 9 10 11 12
7 8 9 10 11 12
8 9 10 11 12
9 10 11 12
10 11 12
11 12
12
--------
68
--------


If a line was drawn from the top row on the number 8 to the number 3 in the
fiest column.

It would touch a number of cells. If the numbers in these cells were added
together ...

eg line 1 - 1+2+3+4+5+6+7+8+9+10+11+12 = 36
line 2 - 2+3+4+5+6 = 20
line 3 - 3+4+5 = 12

Total Score of 36+20+12 = 68.

I need a way of just taking in the above inputs eg 8 and 3 and returning 68.

Cheers

Steve

 

[John C]

It would also depend on how wide the columns are. You also state starting on
8 for Input A, but then you say =1+2+3+4+5+6+7+8+9+10+11+12, but then you say
that =36, but that is only =1+2+3+4+5+6+7+8

 

[John C]

This might be what you are looking for, test it and see.
Assuming your data of numbers starts in A1, down to row 12, and across to
column L. I used the following formula in column M.
M1:
=IF(ROW()>InputB,"",SUM(OFFSET($A1,0,0,1,IF(ROUND(InputA-((ROW()-1)*InputA/InputB),0)=0,1,ROUND(InputA-((ROW()-1)*InputA/InputB),0)))))
Copy this formula down to M12
Total: =SUM(M1:M12)

Hope this helps.