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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|