ExcelBanter - Complex formula

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Complex formula (https://www.excelbanter.com/excel-worksheet-functions/197377-complex-formula.html)

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

There are no 'yellow' squares. More explanation is needed.
--
John C

"steve" wrote:

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

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" <johnc@stateofdenial wrote in message
...
There are no 'yellow' squares. More explanation is needed.
--
John C

"steve" wrote:

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

Hi. It's not too clear, but when A & B are equal, you can sum the diagonal, and all to the left via:

n=12

?(1/6)*n*(1 + n)*(1 + 2*n)
650

I'm not clear on your rule where "3 in column 1" equals 3+4+5 = 12
- -
HTH :)
Dana DeLouis

"steve" wrote in message ...

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" <johnc@stateofdenial wrote in message
...

There are no 'yellow' squares. More explanation is needed.
--
John C

"steve" wrote:

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

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

"steve" wrote:

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" <johnc@stateofdenial wrote in message
...
There are no 'yellow' squares. More explanation is needed.
--
John C

"steve" wrote:

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

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.
--
John C

"steve" wrote:

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