A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Complex formula



 
 
Thread Tools Display Modes
  #1  
Old August 3rd, 2008, 04:37 PM posted to microsoft.public.excel.worksheet.functions
steve
external usenet poster
 
Posts: 2
Default Complex formula

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


  #2  
Old August 3rd, 2008, 05:04 PM posted to microsoft.public.excel.worksheet.functions
John C[_2_]
external usenet poster
 
Posts: 1,350
Default Complex formula

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



  #3  
Old August 3rd, 2008, 11:19 PM posted to microsoft.public.excel.worksheet.functions
steve
external usenet poster
 
Posts: 2
Default Complex formula

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





  #4  
Old August 3rd, 2008, 11:52 PM posted to microsoft.public.excel.worksheet.functions
Dana DeLouis
external usenet poster
 
Posts: 468
Default Complex formula

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





  #5  
Old August 4th, 2008, 02:09 PM posted to microsoft.public.excel.worksheet.functions
John C[_2_]
external usenet poster
 
Posts: 1,350
Default Complex formula

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






  #6  
Old August 5th, 2008, 05:55 PM posted to microsoft.public.excel.worksheet.functions
John C[_2_]
external usenet poster
 
Posts: 1,350
Default Complex formula

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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:34 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.